Analysis of MySQL lock wait and deadlock problems

Preface:
1. Understand lock waiting and deadlock
2. Recurrence and treatment of the phenomenon
Summarize:

Preface:

In the process of MySQL operation and maintenance, lock waiting and deadlock problems are very headaches for DBAs and developers. Such problems may cause business rollback, freeze and other failures. Especially for busy systems, the impact will be more serious if deadlock occurs. In this article, let’s learn what lock waiting and deadlock are, and how to analyze and deal with such problems.

1. Understand lock waiting and deadlock

The reason for lock waiting or deadlock is that access to the database requires locking. Then you may ask, why do we need to lock? The reason is to ensure data correctness in concurrent update scenarios and to ensure the isolation of database transactions.

Imagine a scenario where you want to borrow a book called "High Performance MySQL" from the library. To prevent someone from borrowing the book in advance, you can make a reservation (lock it) in advance. How can you add this lock?

Locking library (database level locks)
Lock all database-related books (table-level locks)
Only lock MySQL related books (page level lock)
Only lock the book "High Performance MySQL" (row-level lock)

The finer the lock granularity, the higher the concurrency level and the more complex the implementation.

Lock waiting can also be called transaction waiting. The transaction executed later waits for the previously processed transaction to release the lock. However, if the waiting time exceeds the MySQL lock waiting time, this exception will be triggered. The error message after the wait timeout is "Lock wait timeout exceeded...".

Deadlock occurs when two transactions wait for each other to release the lock on the same resource, resulting in an infinite loop. When a deadlock occurs, an error message "Deadlock found when trying to get lock..." will be reported immediately.

2. Recurrence and treatment of the phenomenon

Let's take MySQL 5.7.23 as an example (isolation level is RR) to reproduce the above two abnormal phenomena.

mysql> show create table test_tb\G
*************************** 1. row ***************************
       Table: test_tb
Create Table: CREATE TABLE `test_tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(50) NOT NULL DEFAULT '',
  `col2` int(11) NOT NULL DEFAULT '1',
  `col3` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_col1` (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from test_tb;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 1 | fdg | 1 | abc |
| 2 | a | 2 | fg |
| 3 | ghrv | 2 | rhdv |
+----+------+------+------+
3 rows in set (0.00 sec)

# Transaction 1 first executes mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_tb where col1 = 'a' for update;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 2 | a | 2 | fg |
+----+------+------+------+
1 row in set (0.00 sec)

# Transaction 2 and then execute mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> update test_tb set col2 = 1 where col1 = 'a';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The reason for the above exception is that transaction 2 is waiting for the row lock of transaction 1, but transaction 1 has not been committed, and the waiting timeout results in an error. The InnoDB row lock wait timeout is controlled by the innodb_lock_wait_timeout parameter. The default value of this parameter is 50 seconds. That is, by default, transaction 2 will wait for 50 seconds. If the row lock is still not obtained, a wait timeout exception will be reported and the statement will be rolled back.

For version 5.7, when a lock wait occurs, we can view several system tables in information_schema to query the transaction status.

innodb_trx All currently running transactions.
innodb_locks The locks currently occurring.
innodb_lock_waits lock wait correspondence

# When a lock wait occurs, you can view all transactions in the innodb_trx table.
 # If the trx_state value is LOCK WAIT, it means that the transaction is in a waiting state. mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 38511
                 trx_state: LOCK WAIT
               trx_started: 2021-03-24 17:20:43
     trx_requested_lock_id: 38511:156:4:2
          trx_wait_started: 2021-03-24 17:20:43
                trx_weight: 2
       trx_mysql_thread_id: 1668447
                 trx_query: update test_tb set col2 = 1 where col1 = 'a'
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 38510
                 trx_state: RUNNING
               trx_started: 2021-03-24 17:18:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 4
       trx_mysql_thread_id: 1667530
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

# innodb_trx field value meaning trx_id: transaction ID.
trx_state: transaction status, which includes the following states: RUNNING, LOCK WAIT, ROLLING BACK and COMMITTING.
trx_started: transaction start time.
trx_requested_lock_id: The ID of the transaction currently waiting for the lock. You can JOIN with the INNODB_LOCKS table to get more detailed information.
trx_wait_started: The time when the transaction starts waiting.
trx_weight: The weight of the transaction.
trx_mysql_thread_id: transaction thread ID, which can be JOINed with the PROCESSLIST table.
trx_query: The SQL statement being executed by the transaction.
trx_operation_state: current operation state of the transaction.
trx_tables_in_use: The number of tables used in the SQL executed by the current transaction.
trx_tables_locked: The number of row locks currently executing SQL.
trx_lock_structs: The number of locks retained by the transaction.
trx_isolation_level: The isolation level of the current transaction.

# The sys.innodb_lock_waits view also shows the transaction waiting status and gives the SQL to kill the connection
mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2021-03-24 17:20:43
                    wait_age: 00:00:22
               wait_age_secs: 22
                locked_table: `testdb`.`test_tb`
                locked_index: idx_col1
                 locked_type: RECORD
              waiting_trx_id: 38511
         waiting_trx_started: 2021-03-24 17:20:43
             waiting_trx_age: 00:00:22
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 1668447
               waiting_query: update test_tb set col2 = 1 where col1 = 'a'
             waiting_lock_id: 38511:156:4:2
           waiting_lock_mode: X
             blocking_trx_id: 38510
                blocking_pid: 1667530
              blocking_query: NULL
            blocking_lock_id: 38510:156:4:2
          blocking_lock_mode: X
        blocking_trx_started: 2021-03-24 17:18:54
            blocking_trx_age: 00:02:11
    blocking_trx_rows_locked: 3
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 1667530
sql_kill_blocking_connection: KILL 1667530

The sys.innodb_lock_waits view consolidates transaction wait status and provides a kill statement to kill the source of the blockage. However, whether to kill the link still requires comprehensive consideration.

Deadlock is slightly different from lock waiting. Let's also simply reproduce the deadlock phenomenon.

# Open two transactions # Transaction 1 executes mysql> update test_tb set col2 = 1 where col1 = 'a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# Transaction 2 mysql> update test_tb set col2 = 1 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# Return to transaction 1 and press Enter. This statement is in lock waiting state. mysql> update test_tb set col1 = 'abcd' where id = 3;
Query OK, 1 row affected (5.71 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# Return to transaction 2 and execute again. At this time, the two are waiting for each other and a deadlock occurs. mysql> update test_tb set col3 = 'gddx' where col1 = 'a';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

After a deadlock occurs, a transaction will be selected for rollback. To find out the cause of the deadlock, you can execute show engine innodb status to view the deadlock log. Based on the deadlock log and business logic, you can further locate the cause of the deadlock.

In practical applications, we should try to avoid deadlock. We can start from the following aspects:

Keep transactions as small as possible and do not put complex logic into one transaction.
When multiple rows of records are involved, different transactions are required to access them in the same order.
Timely committing or rolling back transactions can reduce the probability of deadlock.
The table must have appropriate indexes.
You can try changing the isolation level to RC.

Summarize:

This article briefly introduces the causes of lock waiting and deadlock. In fact, it is still difficult to analyze deadlocks in real business, which requires a certain amount of experience accumulation. This article is only for beginners, and I hope you can have a preliminary impression of deadlock.

The above is the detailed analysis of MySQL lock wait and deadlock problems. For more information about MySQL lock wait and deadlock, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Learn MySQL intentional shared lock, intentional exclusive lock, and deadlock in one article
Implementation of row lock, table lock and deadlock of Mysql lock mechanism
Ali interview MySQL deadlock problem handling
Solution to MySQL deadlock problem at RC level
How to print deadlock log in MySQL
Detailed explanation of MySQL deadlock and database and table sharding issues
MySQL online deadlock analysis practice
Mysql super detailed explanation of deadlock problem

<<: Installation and use of Apache stress testing tools

>>: Pure CSS3 to create page switching effect example code