MySQL deadlock in simple table

Question

I'm having a deadlock issue that I don't get. Possibly it's because of the composite index (idSite,timestamp). Maybe it's range locking on it?

I have this table:

CREATE TABLE `logs` (
 `idLog` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `idSite` int(10) unsigned NOT NULL,
 `secondsToNextLog` int(11) DEFAULT NULL 
 `gwRestarted` tinyint(4) NOT NULL 
 `timestamp` int(10) unsigned DEFAULT NULL 
 PRIMARY KEY (`idLog`),
 KEY `idSite` (`idSite`,`timestamp`)
) ENGINE=InnoDB

I already removed a foreign key constraint from idSite, because foreign key constraints can be confusing in deadlocks.

I have four processes inserting data into this table (from a queue). The show engine innodb status shows these queries deadlocking:

INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5396', '1438764388', '900', '0')

and

INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5395', '1438764417', '60', '0')

I don't see how this could be deadlocking. The table is very simple and the two queries both are for a different idSite (I made sure of that in the queue processors). Moreover, the engine status shows that both transaction only use and lock one table, so I don't see how this can be a deadlock.

Shouldn't it be easily possible to insert as many rows at the same time as one wants, as long as idSite is different?

This is the complete deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015年08月05日 09:11:38 2b6fcbf25700
*** (1) TRANSACTION:
TRANSACTION 516854792, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 15503218, OS thread handle 0x2b6fcb273700, query id 3125430892 172.31.0.175 vrm_dev update
INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5396', '1438764388', '900', '0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 135684 page no 15 n bits 776 index `idSite` of table `vrm_2012_dev`.`logs` trx id 516854792 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 516854776, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 15503217, OS thread handle 0x2b6fcbf25700, query id 3125430894 172.31.0.175 vrm_dev update
INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5395', '1438764417', '60', '0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 135684 page no 15 n bits 776 index `idSite` of table `vrm_2012_dev`.`logs` trx id 516854776 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 135684 page no 15 n bits 776 index `idSite` of table `vrm_2012_dev`.`logs` trx id 516854776 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

• jkavalik: Are those inserted idSite values (5395, 5396) bigger than anything already present in the table? asc supremum seems to hint that the "bigger-than-everything" gap is being locked by both those inserts, once to check it and second time to show the intention to insert - and a race occurs so the one locking it to check gets behind in signalling insert so you get a deadlock - it is not marked as a gap lock but this comment suggests it is possible not having a gap bit set github.com/mysql/mysql-server/blob/…
• Rick James: How many rows are being inserted per second? Can they be batched? Do you have code in place to retry the insert on deadlock? Is autocommit=ON? Or are you using BEGIN..COMMIT? Are other transactions touching the area near these rows?
• BigSmoke: I'm guessing that you probably resolved this issue between 2015 and now. Care to answer your own question and satisfy my curiosity?
• Halfgaar: @BigSmoke done.

Answer 1

As requested, my solution to an old question.

The write intention lock docs say (emphasis mine, and yes, MySQL 5.7, because 8.0 didn't exist at the time):

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row.

The crucial part is the bold. In my case, my transaction was not done at this point, so it was holding a write intent and exclusive lock.

I suppose the fix is to finish your transaction after the insert, and not continue inserting. My fix was to (externally) serialize inserts, as it was a relatively infrequent operation. For instance, lacking direct concurrency control primitives like mutexes and rwlocks, you can designate a table with locks in it, and do SELECT * FROM hack_locks WHERE id = <id_of_insert_lock_row> FOR UPDATE. That way, once a transaction starts inserting, others can't.