The default isolation level in InnoDB is Repeatable Read, which would lock the index (not locking the data table) until the end of transaction. My summary of the article and the answer to my own question is as following: Thank for giving the clue about isolation level. The article here gives a exhaustive explanation about the locks and isolation levels. The result of SHOW CREATE TABLE is as following: | task_content | CREATE TABLE `mytbl` ( Then why Task_2 held an S lock and resulted in deadlock? The INSERT statement requires X lock on the single row to be inserted. According to MySQL manual, the simple SELECT statement uses snapshot read which requires no S lock.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 *** (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 S TRANSACTION 0 746449, ACTIVE 0 sec, process no 4690, OS thread id 140411389953792 inserting, thread declared inside InnoDB 500ġ172 lock struct(s), heap size 112624, 32914 row lock(s) ![]() Record lock, heap no 1 PHYSICAL RECORD: n_fields 1 compact format info bits 0Ġ: len 7072656d756d asc supremum ![]() 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 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: The deadlock log is as following (with some details truncated):. INSERT mytbl (id, name, ts) values ('newId', 'anotherValue', now()) SELECT count(id) from mytbl where name = 'someValue' and timestampdiff(hour, ts, now()) < 1 The deadlock occurred when two tasks tried to select and then insert the same table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |