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.
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!