Preface:
In the process of MySQL operation and maintenance, lock waiting and deadlock problems are very headache for DBA and development students. This type of fault may cause service rollbacks and stalling, especially in busy systems. Deadlocks may cause more serious impacts. In this article, we will learn what is lock wait and deadlock, and how to analyze and deal with such problems?
1. Understand lock waits and deadlocks
The reason for a lock wait or deadlock is that the database needs to be locked, so you may ask, why lock? The reason is to ensure data correctness in concurrent update scenarios and to ensure the isolation of database transactions.
Imagine a scenario, if you want to go to the library to borrow a copy of High Performance MySQL, in order to prevent someone from taking the book out in advance, you can make an appointment in advance (lock), how can the lock be added?
- Blocking libraries (database level locks)
- Lock all database-related books (table level locking)
- Lock only mysql-related books (page-level lock)
- Lock only the book High-performance MySQL (row-level lock)
The finer the granularity of the lock, the higher the concurrency level and the more complex the implementation.
The lock wait is also called transaction wait. A later transaction waits for a previous transaction to release the lock, but if the wait time exceeds the MySQL lock wait time, this exception is raised. The error message Lock Wait timeout exceeded… .
A deadlock occurs when two transactions wait for each other to release the lock on the same resource, resulting in an infinite loop. Deadlock causes an immediate error “Deadlock found when trying to get lock…” .
2. Symptom recurrence and treatment
MySQL 5.7.23 (RR isolation level) is used as an example to verify 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.00SEC) # execute mysql first> 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.00SEC) # transaction 2 then executes 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
Copy the codeThe above exception occurs because transaction 2 is waiting for the row lock of transaction 1, but transaction 1 is not committed. InnoDB row lock timeout is controlled by the innodb_lock_WAIT_TIMEOUT parameter, which defaults to 50 seconds. By default, the innoDB_lock_WAIT_timeout parameter is 50 seconds. By default, transaction 2 waits 50 seconds.
With version 5.7, several system tables at Information_SCHEMA can be viewed to query transaction status when a lock wait occurs.
- Innodb_trx All transactions currently running.
- Innodb_locks Locks that currently occur.
- Innodb_lock_waits lock waits
If the value of innodb_TRx is LOCK WAIT, the transaction is in WAIT state> 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.00SEC) # innodb_trx Trx_id: transaction ID. Trx_state: transaction status, including the following states:RUNNING", LOCK WAIT, ROLLING BACK, and research (right). Trx_started: indicates the transaction start time. Trx_requested_lock_id: Identifies the lock that the transaction is currently awaiting. This can be matched with the INNODB_LOCKS tableJOINFor more details. Trx_wait_started: Time to wait for the transaction to start. Trx_weight: transaction weight. Trx_mysql_thread_id: transaction thread ID, which can be the same as PROCESSLIST tableJOIN. Trx_query: What the transaction is executingSQLStatements. Trx_operation_state: current operation status of a transaction. Trx_tables_in_use: indicates the current transaction executionSQLThe number of tables used in. Trx_tables_locked: The table is currently lockedSQLNumber of row locks. Trx_lock_structs: number of locks retained by a transaction. Trx_isolation_level: Isolation level of the current transaction. The # sys. innodb_lock_WAITS view also sees transaction waits and gives a kill linkSQL
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
Copy the codeThe sys. innodb_lock_WAITS view integrates transaction waits and gives a kill statement that kills the blocked source. However, whether or not to kill the link needs to be considered.
Deadlocks are slightly different from lock waits, and we will also briefly reproduce the deadlock phenomenon.
Mysql > select * from transaction 1> 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 execute mysql> update test_tb set col2 = 1 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Mysql > select * from 'lock wait'> update test_tb set col1 = 'abcd' where id = 3;
Query OK, 1 row affected (5.71 sec)
Rows matched: 1 Changed: 1 Warnings: 0Mysql > execute transaction 2; mysql > execute transaction 2> update test_tb set col3 = 'gddx' where col1 = 'a';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Copy the codeAfter a deadlock occurs, a transaction is selected to roll back. To find out the cause of the deadlock, run the show Engine Innodb status command to view the deadlock logs and locate the cause based on the deadlock logs and service logic.
In practical application, we should try to avoid the occurrence of deadlock phenomenon from the following aspects:
- Keep transactions as small as possible and do not put complex logic into one transaction.
- When multi-line records are involved, it is agreed that different transactions should be accessed in the same order.
- A transaction must 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.
Conclusion:
This article briefly introduces the causes of lock waiting and deadlock. In fact, it is difficult to analyze life and death locks in real business and requires some experience accumulation. This article is intended for beginners, and hopefully gives you a simple impression of deadlocks.