Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve deadlock in MySQL, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. Understand lock waiting and deadlock

The reason for lock waiting or deadlock is that access to the database needs to be locked, so you may want to ask, why do you want to add a lock? The reason is to ensure the correctness of data in concurrent update scenarios and the isolation of database transactions.

Lock waits can also be called transaction waits, and later executed transactions wait for the previously processed transaction to release the lock, but this exception is thrown when the wait time exceeds the lock wait time of the MySQL. The error after waiting for timeout is "Lock wait timeout exceeded...".

The deadlock occurs when two transactions wait for each other to release the lock of the same resource, resulting in a dead loop. An error "Deadlock found when trying to get lock..." will be reported immediately after the deadlock is generated.

two。 Phenomenon recurrence and treatment

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

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 `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 one 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 then execute mysql > begin Query OK, 0 rows affected (0.01sec) mysql > update test_tb set col2 = 1 where col1 = 'asides; 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 the transaction is not committed, waiting for the timeout and reporting an error. The InnoDB row lock wait timeout is controlled by the innodb_lock_wait_timeout parameter, which defaults to 50 in seconds, that is, transaction 2 waits for 50 seconds by default. If it still fails to get the row lock, it will report a wait timeout exception and roll back this statement.

For version 5. 7, when a lock wait occurs, we can look at several system tables in information_schema to query the transaction status.

All transactions currently running by innodb_trx.

The lock that currently appears in innodb_locks.

The correspondence of innodb_lock_waits Lock waiting

# viewing the innodb_ box table when lock waiting occurs, you can see that all transactions # trx_ state value of LOCK WAIT means that the transaction is in the 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 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 means trx_id: transaction ID. Trx_state: transaction state, with the following states: RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING. Trx_started: transaction start time. Trx_requested_lock_id: the transaction is currently waiting for the identification of the lock. You can JOIN the INNODB_LOCKS table for more details. Trx_wait_started: the time that the transaction begins to wait. Trx_weight: the weight of the transaction. Trx_mysql_thread_id: transaction thread ID, which can be JOIN with the PROCESSLIST table. Trx_query: the SQL statement that the transaction is executing. Trx_operation_state: the current operational 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: isolation level of the current transaction. # sys.innodb_lock_waits view can also see the transaction waiting condition And give the SQL 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 update test_tb set col2 156 4 2 waiting_lock_mode: X blocking_trx_id: 38510 blocking_pid: 1667530 blocking_query: NULL Blocking_lock_id: 38510 blocking_trx_started 156 blocking_trx_age 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 integrates transaction wait conditions and gives kill statements that kill blocking the source side. However, whether to kill the link still needs to be considered comprehensively.

Deadlock is slightly different from lock waiting, so let's also simply recreate the deadlock phenomenon.

# start two transactions # transaction 1 execute 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 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: 0 # after returning to transaction 1, the 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 # when they return to transaction 2 and then execute, the two 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 is selected to roll back. If you want to find out the cause of the deadlock, you can execute show engine innodb status to view the deadlock log, and further locate the deadlock cause according to the deadlock log and business logic.

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

Transactions as small as possible, do not talk about complex logic into a transaction.

When multiple lines of records are involved, it is agreed that different transactions are accessed in the same order.

In order to reduce the probability of deadlock, the transaction should be submitted or rolled back in time.

The table should have an appropriate index.

Try changing the isolation level to RC.

About how to achieve deadlock in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.