MySQL Deadlock reason analysis

There are three modes of row-level locks:

There are roughly three types of InnoDB lock Record-level Lock: Record lock, Gap Lock and Next-keylocks. Record lock Locks a row of recordsGap Lock locks Records in a certain rangeNext key lock is the overlay of the previous two effects.

Problem:

Row-level lock representation: Next-key Lock

Error code: 1213
Deadlock found when trying to get lock; Try restarting transaction, the focus is: Deadlock FOUND when trying to get LOCK; Represents row-level lock collisions

Solve:

solution, the update may be in a large table, and there are constant data insertions in the table, you can use the update identity on the corresponding update group, and then, to update the data more than once, if there is a lock, error, transaction rollback, and then again to update the data, this lock occurs in a small probability, so, This method can be used to resolve

Reference blog:

There are roughly three types of InnoDB lock Record-level Lock: Record lock, Gap Lock and Next-keylocks. Record lock Locks a row of recordsGap Lock locks Records in a certain rangeNext key lock is the overlay of the previous two effects. here is a link to the relevant content in the MySQL official documentationhttp://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html"Experimental Environment"Session 1 20:39:29> Show create TABLE Gap \g*************************** 1. Row ***************************Table:gapCreate table:create Table ' Gap ' (' id ' int (one) DEFAULT NULL,KEY ' ind_gap_id ' (' id ')) Engine=innodb DEFAULT Charset=utf81 row in Set (0.00 sec)Session 1 20:39:32> SELECT * from Gap; +------+| id |+------+| || || || || || |+------+6 rows in Set (0.00 sec) "Experiment"two sessions are at the Repeatable-read transaction isolation level. And all in the transaction. Session 1 20:39:37> start transaction; Query OK, 0 rows Affected (0.00 sec)Session 1 20:39:41> Delete from Gap where id=33;Query OK, 1 row Affected (0.00 sec)Session 20:40:07> The value of Insert ID <20 and >=39 in Session 2 can be performed successfully, and Gap lock is encountered when the value of the ID [20,39] to be inserted. Session 2 20:40:15> start transaction;Query OK, 0 rows Affected (0.00 sec)Session 2 20:40:30> INSERT into gap values (+);Query OK, 1 row Affected (0.00 sec)Session 2 20:40:59> INSERT into gap values (+);Query OK, 1 row Affected (0.00 sec)Session 2 20:41:06> INSERT into gap values (+);ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transactionSession 2 20:41:12> INSERT into gap values (+);ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transactionSession 2 20:42:17>Session 2 20:42:53> INSERT into gap values (+);ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transactionSession 2 20:44:09>Session 2 20:44:56> INSERT into gap values (in);Query OK, 1 row Affected (0.00 sec)Session 2 20:45:13> INSERT into gap values (40); Query OK, 1 row Affected (0.00 sec) from the above experiment, we can see that session 1 executes the DELETE statement, not only locks the record of id=33, but also locks the record of the interval as [20,39]. The specific reason is to execute the delete from Gap where id=33 statement, MySQL will perform an index scan and apply a next-key lock on the table, scan left to 20, scan to the right to 39, lock interval left closed to open, so the lock range is [20,39].  "problem with Gap lock"there is a situation in the production environment where:The program will update and insert a table messageSession 1UPDATE message SET gmt_modified = Now (), deal_times = Deal_times +1, status = ' sending ', gmt_retry = ' 2012-11-17 23 : 54:10 'WHERE message_id=18;INSERT into message (body, user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (' Hello! ', -1, ' sending ', ' Instance_status_sync ', 2,127,now (), now (), now ()); Session 2UPDATE message SET gmt_modified = Now (), deal_times = Deal_times +1, status = ' sending ', gmt_retry = ' 2012-11-17 23 : 54:10 'WHERE message_id=19;INSERT into message (body, user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (' Hello world! ', -2, ' sending ', ' Instance_status_sync ', 1,17,now (), now (), now ()); for these programs to run normally without concurrency, but with large concurrency, the order of execution may become the following:UPDATE message SET gmt_modified = Now (), deal_times = Deal_times +1, status = ' sending ', gmt_retry = ' 2012-11-17 23 : 54:10 'WHERE message_id=;UPDATE message SET gmt_modified = Now (), deal_times = Deal_times +1, status = ' sending ', gmt_retry = ' 2012-11-17 23 : 54:10 'WHERE message_id=;INSERT into message (body, user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (' Hello world! ', -2, ' sending ', ' Instance_status_sync ', 1,17,now (), now (), now ());INSERT into message (body, user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) values (' Hello! ', -1, ' sending ', ' Instance_status_sync ', 2,127,now (), now (), now ()); this often causes an error[ERROR] Could not execute Write_rows event on table db.message; Deadlock found when trying toget lock;;  Try restarting transaction, error_code:1213;  The first two statements of the update type have obtained the S lock recorded within the [59,75] interval, and then the two transactions request X lock for the position of the message_id=10 in the interval, then a deadlock occurs, and no one requests X locks because each holds the S lock.  "There are two types of solutions"1. Change the logic of database operation in the program2. Cancel the gap lock mechanismGap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED orenable the Innodb_locks_unsafe_for_binlog sy Stem variable."Reference"http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.htmlhttp://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.htmlBlog Address: http://blog.itpub.net/22664653/viewspace-750824/

MySQL Deadlock reason analysis