There has been a lot of controversy over the deadlock of concurrent insert operations, and all the examples and simulations of online postings do not necessarily reflect the real situation, such as:
https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
Http://www.cnblogs.com/LBSer/p/5183300.html
Http://www.cnblogs.com/bamboos/p/3532150.html
A lot of similar examples should be in fact a lot of speculation and in order to reproduce the purpose of reproduction.
In fact, open two windows, do not do delete/update/select for update, only Select/insert is 100% can reproduce the deadlock scene, or can not appear.
Start transaction A window select from X where X=1, does not exist then insert X table record 1. Suppose X has an index.
Start Transaction b window select from X where x=2, does not exist then insert X table record 2.
This depends on the transaction isolation level currently in use, with different behavior under different transaction isolation levels.
A session
Use Memdb;
SHOW VARIABLES like '%autocommit% ';
SET tx_isolation= ' SERIALIZABLE ';
SHOW VARIABLES like '%tx% ';
START TRANSACTION;
SELECT * from ' ABCDEFG ' WHERE def=19 and ghi=19 and jkl=19;
--Pause, wait for the first half of window B to execute the next section
INSERT into ' ABCDEFG ' (ABC,DEF,GHI,JKL,MNP) VALUES (' 19 ', 19,19,19,1);
COMMIT;
Use Memdb;
SHOW VARIABLES like '%autocommit% ';
SET tx_isolation= ' SERIALIZABLE ';
SHOW VARIABLES like '%tx% ';
START TRANSACTION;
SELECT * from ' ABCDEFG ' WHERE def=18 and ghi=18 and jkl=18;
--Pause, etc. the first half of the a window is executed in the next section of the
INSERT into ' ABCDEFG ' (ABC,DEF,GHI,JKL,MNP) VALUES (' 18 ', 18,18,18,1);
COMMIT;
The reason in the MySQL line lock implementation is mainly divided into record lock, Gap Lock, Next-key lock and so on. The range of these three locks is different, and the row lock does not mean that all existing rows that meet the conditions explicitly stated in where are locked when implemented, and may include implicit, nonexistent records.
Because serializable is using the gap lock, the problem occurs when the insert >= all records in the current index are added with the S lock.
This is also a very pure example of a deadlock that is only an insert operation, which is sometimes quite incomprehensible to developers who use Oracle and SQL Server.
Specifically see the different types of locks http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html.
MySQL Concurrency insert deadlock analysis and resolution, no delete/update/for update
