Sign upSign InSign upSign InMember-only storyDwenFollowLevel Up Coding--ListenShareMysql deadlock.Prepare the environment.2. Confirm the database isolation level.3. Turn off autocommit for MySQL.4. Create a test table.Simulate a deadlock.First, we need to open two terminals to simulate transactions.The first terminal we call TA and the second terminal we call TB.① Perform the update operation in TA.② Perform the update operation in TB.③ Perform the insert operation in TA.At this time, we will find that the terminal TA is blocked and cannot be executed.④ Perform the insert operation in TB.We can find that Transaction TB is executed successfully, and transaction TA deadlock reports an error: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction .Now we commit the operation on both terminals.We found that all operations of transaction TB eventually succeeded, while transaction TA operations were rolled back due to errors.Deadlocks are a database protection mechanism for transactions, and in the event of a deadlock, MySQL selects relatively small transactions (less undo) to roll back.Analyze deadlock logs.You can query the latest deadlock log information through the show engine innodb status; command.(1) TRANSACTION it`s the first transaction.(2) TRANSACTION it`s the second transaction.Let’s analyze the first transaction.His transaction ID is 235360 , and the SQL is: insert into students(`age`, `name`) values (15, 'lee');WAITING FOR THIS LOCK TO BE GRANTED tells us that the current transaction is waiting for another transaction to release the X lock.Let’s analyze the second transaction.His transaction ID is 235361 , and the SQL is: insert into students(`age`, `name`) values (30, 'dec')HOLDS THE LOCK(S) tells us that the current transaction alreay has a X lock.WAITING FOR THIS LOCK TO BE GRANTED tells us that the current transaction is waiting for another transaction to release the X lock.Summary: Transaction 1 is waiting for the release of the X lock, and transaction 2 itself holds an X lock and is also waiting for the release of the X lock, which eventually causes transaction 1 and transaction 2 to wait for each other to release the lock, and a deadlock occurs.The above is just an explanation in the deadlock log, but it is not clear enough, let’s restore the real occurrence of the deadlock.However, before we start, we need to supplement the knowledge of gap locks.Gap lock.A gap lock is a lock on a gap between index records.It is mainly used to ensure that the data in a certain gap will not change in the case of locking. than my mysql default isolation level under repeatable reads (RR).When using a unique index to search for statements that are unique rows, gap locking is not required. If the id column in the following statement has a unique index, only rows with an id value of 10 will be used to use record locks.The range of the gap.According to the search criteria, look forward for the record value A closest to the search condition as the left interval.Look backwards for the record value B closest to the search condition as the right interval, that is, the locked gap is (A, B).Suppose, we now have a table with a field called number, and the data in the table is as follows 1, 2, 3, 4, 5, 6, 6, 6, 11 .The locking range of the gap lock is: (5, 11).If you want to insert a number between 5 and 11, it will be blocked.The process by which the deadlock occurred.Since at the beginning there are only 2 records in the database table with age of 10 and 20.In TA, a gap lock is generated when update students set name = 'bar_a' where age = 20; is executed, and its range is (10, +∞) .In TB, a gap lock is generated when update students set name = 'foo_b' where age = 10; is executed, and its range is (-∞, 20) .Why does executing insert into students(`age`, `name`) values (15, 'lee');in TA cause blocking?Because age=15, it is in the range (-∞, 20), which holds the lock on TB, so SQL needs to wait for the transaction TB to be released.When insert into students(`age`, `name`) values (30, 'dec');in TB is executed, a gap lock (20, +∞) is generated, and the submission can be successful.But this SQL will also hit the gap range (10, +∞), so transaction TB still needs to wait for transaction TA to release the lock.Eventually a deadlock was created.If you like such stories and want to support me, please give me a clap.Your support is very important to me, thank you.----Level Up CodingFour years full-stack development experience engineer, enjoys speaking, writing, and technical sharing. If you like what I write, please do-follow.HelpStatusWritersBlogCareersPrivacyTermsAboutText to speechTeams