MySQL deadlock caused by concurrent INSERT and SELECT

Question

• MySQL version: 5.6
• Storage engine: InnoDB

The deadlock occurred when two tasks tried to select and then insert the same table. The procedure looks like:

          Task_1       Task_2
          ------      ------
Phase 1 | SELECT      SELECT
Phase 2 | INSERT      INSERT

SELECT count(id) from mytbl where name = 'someValue' and timestampdiff(hour, ts, now()) < 1;
INSERT mytbl (id, name, ts) values ('newId', 'anotherValue', now());

The deadlock log is as following (with some details truncated):

------------------------
LATEST DETECTED DEADLOCK
------------------------
151225  8:22:17
*** (1) TRANSACTION:
TRANSACTION 0 746402, ACTIVE 0 sec, process no 4690, OS thread id 140411390486272 inserting
mysql tables in use 1, locked 1
LOCK WAIT 1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3909, query id 31751474 10.20.36.38 mydb update
INSERT INTO mytbl -- truncated
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`mytbl` trx id 0 746402 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 0 746449, ACTIVE 0 sec, process no 4690, OS thread id 140411389953792 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3906, query id 31751477 10.20.36.38 mydb update
INSERT INTO mytbl  -- truncated
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 lock mode S
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 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 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)

---

Questions

1. According to MySQL manual, the simple SELECT statement uses snapshot read which requires no S lock. The INSERT statement requires X lock on the single row to be inserted. Then why Task_2 held an S lock and resulted in deadlock?

---

Edit

The result of SHOW CREATE TABLE is as following:

| task_content | CREATE TABLE `mytbl` (
`id` bigint(20) NOT NULL,
`ts` timestamp NULL DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Answer 1

The article here gives a exhaustive explanation about the locks and isolation levels.

Thank @newtover for giving the clue about isolation level. My summary of the article and the answer to my own question is as following:

The default isolation level in InnoDB is Repeatable Read, which would lock the index (not locking the data table) until the end of transaction.

In my circumstance, the only index is PRIMARY, which was useless in my SELECT query (can be verified by explain select...). As a result, all entries in the PRIMARY index were locked. When TXN_2 waited for an X lock on a certain entry, the entry was locked by an S lock retained by TXN_1. Similarly, TXN_1 waited for an X lock on another entry but the entry was also locked by S lock retained by itself. A "one S two X" deadlock occurred.

By contrast, after I created an index name on the column name, the index name would be used in the SELECT statement (can be verified by explain select ...), so the locks would be issued on the index name instead of PRIMARY. More importantly, the SELECT statement would only issue S lock on the entry equals to someValue instead of all entries of index name. Besides, the IX lock and X lock required by INSERT would be issued on the index PRIMARY. Conflicts between S lock and IX lock, X lock would be solved.

The index on column name did not only speed up the query but more importantly prevented locking all entries of the index.

Answer 2

where name = 'someValue' and timestampdiff(hour, ts, now()) < 1;

That is rather inefficient. Let's clean that up to speed things up, to decrease the likelihood of a deadlock.

timestampdiff(hour, ts, now()) < 1 hides any index with ts; let's rewrite it to

ts < NOW() - INTERVAL 1 HOUR

Yours truncated in unexpected ways; mine says "ts older than 1 hour ago", which I suspect you wanted.

Now we can index ts to good effect. But let's carry it further by using a "composite" index:

INDEX(name, ts)

This will efficiently use both parts of the WHERE clause for locating the row(s).

You say COUNT(id) -- this implies that you need to avoid NULLs in id. Perhaps that is not a concern, and you could say simply COUNT(*).

Those should make the SELECT faster. Now let's figure out why that SELECT and INSERT have anything to do with each other. Are they in the same transaction? Or do you have autocommit turned OFF, but forgot to say COMMIT? Please show us the entire transaction, plus SHOW CREATE TABLE.