Analysis of lock waiting and deadlock in MySQL

Time:2021-11-15

preface:

During MySQL operation and maintenance, lock waiting and deadlock problems are a headache for DBAs and development students. Such problems will cause business rollback, jamming and other failures, especially in busy systems. The impact will be more serious after deadlock problems occur. In this article, let’s learn what is lock waiting and deadlock, and how to analyze and deal with such problems?

1. Understand lock waiting and deadlock

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

Imagine a scenario. If you want to borrow a high-performance MySQL from the library, you can make an appointment (lock) in advance in order to prevent someone from borrowing the book in advance. How can you add this lock?

  • Block library (database level lock)
  • Lock all books related to the database (table level lock)
  • Lock only MySQL related books (page level locks)
  • Only lock the book “high performance mysql” (row level lock)

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

Lock waiting can also be called transaction waiting. Transactions executed later wait for the previous transaction to release the lock, but the waiting time exceeds the lock waiting time of MySQL, this exception will be thrown. The error message after waiting timeout is “lock wait timeout exceeded…”.

Deadlock occurs because two transactions wait for each other to release the lock of the same resource, resulting in an endless loop. An error “deadlock found when trying to get lock…” will be reported immediately after a deadlock occurs.

2. Phenomenon recurrence and treatment

Let’s take MySQL version 5.7.23 as an example (isolation level is RR) to reproduce the above two exceptions.

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 is executed first
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 is then executed
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. The wait times out and an error is reported. InnoDB row lock wait timeout_ lock_ wait_ The timeout parameter controls. The default value of this parameter is 50, and the unit is seconds. That is, by default, transaction 2 will wait for 50s. 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 the information_ Several system tables in schema to query transaction status.

  • innodb_ trx   All currently running transactions.
  • innodb_ locks   Lock currently present.
  • innodb_ lock_ waits   Correspondence of lock waiting
#   When a lock wait occurs   View InnoDB_ The TRX table can see all transactions 
#   trx_ The state value is lock   WAIT   Indicates 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. There are the following statuses: running and lock   WAIT、ROLLING   Back and committing.
trx_ Started: transaction start time.
trx_ requested_ lock_ ID: the ID of the transaction that is currently waiting for a lock. It can be associated with InnoDB_ Locks table   JOIN   For more details.
trx_ wait_ Started: the time the transaction starts waiting.
trx_ Weight: the weight of the transaction.
trx_ mysql_ thread_ ID: transaction thread   ID, can and   PROCESSLIST   surface   JOIN。
trx_ Query: the number of transactions being executed   SQL   sentence.
trx_ operation_ State: current operation state of the transaction.
trx_ tables_ in_ Use: the current transaction is executed   SQL   Number of tables used in.
trx_ tables_ Locked: current execution   SQL   Number of row locks.
trx_ lock_ Structs: number of locks reserved by the transaction.
trx_ isolation_ Level: the isolation level of the current transaction.

# sys.innodb_ lock_ In the waits view, you can also see the transaction waiting status, and the linked SQL is given
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

sys.innodb_ lock_ The waits view integrates the transaction waiting status and gives the kill statement to kill the blocking source side. However, whether to kill the link still needs comprehensive consideration.

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

#   Open two transactions
#   Transaction execution
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 execution
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

#   Back to transaction one execution   After entering   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

#   Go back to transaction 2 and execute again   At this time, they wait for each other to deadlock
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, and further locate the cause of the deadlock according to the deadlock log and in combination with the business logic.

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

  • Transactions should be as small as possible. Do not put complex logic into one transaction.
  • When multiple lines of records are involved, different transactions are agreed to be accessed in the same order.
  • In business, transactions should be submitted or rolled back in time to reduce the probability of deadlock.
  • Tables should have appropriate indexes.
  • Try changing the isolation level to RC.

Summary:

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

The above is the detailed analysis of MySQL lock waiting and deadlock. For more information about MySQL lock waiting and deadlock, please pay attention to other related articles of developeppaer!