MySQL database deadlock instance analysis, mysql instance analysis

MySQL database deadlock instance analysis, mysql instance analysis

1. The story was caused by a production bug in November 15, 2016. The business scenario is to archive the data in a table to the History Table, and delete the primary table records.

2. Simplified background scenarios (Database Engine InnoDb, data isolation level RR [REPEATABLE])

-- Create table test1 create table test1 (id int (11) not null AUTO_INCREMENT, name varchar (10) not null, primary key (id )); insert into test1 values ('hello'); -- create table test2 create table test2 (id int (11) not null AUTO_INCREMENT, name varchar (10) not null, primary key (id); -- Transcation 1 begin; insert into test2 select * from test1 where id = 1; delete from test1 where id = 1; -- Transcation 2 begin; insert into test2 select * from test1 where id = 1;

3. Specific execution sequence

Transcation1	Transcation2
Begin; -This SQL statement obtains the share Lock S (id = 1) insert into test2 select * from test1 where id = 1;
	Begin; -This SQL statement attempts to obtain the share Lock S (id = 1) of the primary key index of the table test1, but it is already occupied by T1, so it enters the lock Request queue. Insert into test2 select * from test1 where id = 1;
-This SQL statement attempts to upgrade the shared Lock S (id = 1) of the primary key index lock of the test1 table to the exclusive lock X (id = 1) -At this time, T1 also initiates a lock request. At this time, mysql finds that there is a transaction T2 pair (id = 1) in the lock Request queue that applied for the S lock, resulting in a deadlock. Delete from test1 where id = 1;
	After a deadlock occurs, mysql selects transaction 2 with a smaller weight based on the weights of the two transactions and rollback as the victim of the deadlock.
After T2 rollback, T1 successfully acquires the lock and runs successfully.

Mysql official explanation

Deadlock occurs here because client A needs an X lock to delete the row. however, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. as a result, InnoDBgenerates an error for one of the clients and releases it S locks. The client returns this error.

There are some differences between the actual scenario and the mysql document. The document needs to obtain the X lock. In this example, the S lock is required.

Next we will analyze the mysql deadlock step by step.

1. Lock Mechanism of common MySQL storage engines

MyISAM and MEMORY use table-level locking)

BDB uses page-level locking or table-level locking. The default value is page lock.

InnoDB supports row-level locking and table-level locking. The default value is row-level locking.

2. Various lock features

Table-Level Lock: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency

Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, minimum probability of lock conflict, and highest concurrency

Page lock: overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks, and the concurrency is average.

3. Applicable scenarios of various locks

Table-level locks are more suitable for queries, and only a few applications update data based on index conditions, such as Web applications.

Row-level locks are more suitable for applications with a large number of concurrent data updates based on index conditions and concurrent queries, such as some online transaction processing systems.

4. deadlock

Two or more processes are waiting for each other due to resource competition during execution. If there is no external force, they will not be able to proceed.

Table-level locks do not produce deadlocks. Therefore, the most commonly used InnoDB is used to solve deadlocks.

5. deadlock Example Analysis

In MySQL, row-level locks do not directly lock records, but lock indexes. Indexes can be classified into primary key indexes and non-primary key indexes. If an SQL statement operates on a primary key index, MySQL locks the primary key index. If a statement operates on a non-primary key index, mySQL will first lock the non-primary key index, and then lock the relevant primary key index.

During the UPDATE and DELETE operations, MySQL not only locks all index records scanned by the WHERE condition, but also locks adjacent key values, that is, the so-called next-key locking.

For example, a table db. Tab_test, with the following structure:

Id: primary key;

State: status;

Time: time;

Index: idx_1 (state, time)

The deadlock log is as follows:

? * ** (1) TRANSACTION :? TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read? Mysql tables in use 1, locked 1? Lock wait 3 lock struct (s), heap size 320? MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update? Update tab_test set state = 1064, time = now () where state = 1061 and time <date_sub (now (), INTERVAL 30 minute) (SQL statement of Task 1 )? * ** (1) waiting for this lock to be granted: (the index record waiting for Task 1 )? Record locks space id 0 page no 849384 n bits 208 index 'primary' of table 'db/tab_test 'trx id 0 677833455 _ mode X locks rec but not gap waiting? Record lock, heap no 92 physical record: n_fields 11; compact format; info bits 0? 0: len 8; hex 800000000097629c; asc B; 1: len 6; hex 00002866 eaee; asc (f; 2: len 7; hex 00000d40040110; asc @; 3: len 8; hex 80000000000050b2; asc P; 4: len 8; hex 800000000000502a; asc P *; 5: len 8; hex 8000000000005426; asc T &; 6: len 8; hex 800012412c66d29c; asc A, f; 7: len 23; hex 20178616e642e706870; asc xxx.com/133; 8: len 8; hex 8001_000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +; 10: len 8; hex 8000000000004e24; asc N $ ;;? * ** (2) TRANSACTION :? TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499? Mysql tables in use 1, locked 1? 3 lock struct (s), heap size 320, undo log entries 1? MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state = 1067, time = now () where id in (9921180) (SQL statement of Task 2 )? * ** (2) holds the lock (S): (the lock obtained by Task 2 )? Record locks space id 0 page no 849384 n bits 208 index 'primary' of table 'db/tab_test 'trx id 0 677833454 lock_mode X locks rec but not gap? Record lock, heap no 92 physical record: n_fields 11; compact format; info bits 0? 0: len 8; hex 800000000097629c; asc B; 1: len 6; hex 00002866 eaee; asc (f; 2: len 7; hex 00000d40040110; asc @; 3: len 8; hex 80000000000050b2; asc P; 4: len 8; hex 800000000000502a; asc P *; 5: len 8; hex 8000000000005426; asc T &; 6: len 8; hex 800012412c66d29c; asc A, f; 7: len 23; hex 20178616e642e706870; asc uploadfire.com/hand.php#; 8: len 8; hex 800000000 Listen 42B; asc +; 9: len 4; hex 474bfa2b; asc GK +; 10: len 8; hex 8000000000004e24; asc N $ ;;? * ** (2) waiting for this lock to be granted: (Task 2 waits FOR the LOCK )? Record locks space id 0 page no 843102 n bits 600 index 'idx _ 1' of table 'db/tab_test 'trx id 0 677833454 lock_mode X locks rec but not gap waiting? Record lock, heap no 395 physical record: n_fields 3; compact format; info bits 0? 0: len 8; hex 8000000000000425; asc %; 1: len 8; hex 800012412c66d29c; asc A, f; 2: len 8; hex 800000000097629c; asc B ;;? * ** We roll back transaction (1 )? (Rollback Task 1 to remove the deadlock)

Cause analysis:

When"update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”During execution, MySQL uses the idx_1 index. Therefore, the relevant index records are locked first. Because idx_1 is a non-primary key index, MySQL also locks the primary key index to execute this statement.

Assume that"update tab_test set state=1067,time=now () where id in (9921180)When executing the statement almost simultaneously, this statement first locks the primary key index. to update the state value, you also need to lock some index records of idx_1.

In this way, the first statement locks the record of idx_1, waits for the primary key index, and the second statement locks the primary key index record, and waits for the record of idx_1, resulting in a deadlock.

6. Solution

Split the first SQL statement, first identify the qualified primary key value, and then update the record according to the primary key:

　　select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);　　update tab_test state=1064,time=now() where id in(......);

Here is an introduction to the instance Analysis and Solution to the MySQL deadlock problem. I hope this introduction will be helpful to you!

Mysql official documentation: http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-example.html