Why did this deadlock happen in MySQL?

user_5835947 0
Viewed 509

I use JMeter to test my program, somehow the total response number is stopping increasing, then I find out there is a deadlock in MySQL. I don't understand what's meaning of below log. Seems that transaction(2) owned a S lock and trying to own a X lock of the same table. Does this cause the deadlock? If so, why will this happen?

ATEST DETECTED DEADLOCK
------------------------
2019-01-02 14:38:27 0x70000f30a000
*** (1) TRANSACTION:
TRANSACTION 24004, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 11953, OS thread handle 123145549275136, query id 418447 localhost 127.0.0.1 username executing
INSERT INTO MRBS_SCHEDULE (ID,START_TIME,END_TIME,ROOM_ID,CREATE_BY,PRESIDE,REPEAT_ID,DESCRIPTION,NUM,TITLE,PRESIDE_EMAIL,PROJECTOR,CONFERENCE_CALL,CREATE_ID,BOOK_TIME,END_TYPE,EXPECTED_END_TIME) select null,'2019-01-03 19:53:00','2019-01-03 19:53:00',10113558,'d','d',12245755,'fdsfds',10,null,'d@sh.ff.com',0,0,10227622,'2019-01-02 14:38:27.358',0,'2019-01-03 19:53:00' from dual WHERE NOT EXISTS (SELECT * FROM MRBS_SCHEDULE ms where ms.START_TIME<'2019-01-03 19:53:00' and ms.END_TIME>'2019-01-03 19:53:00' and ms.ROOM_ID=10113558)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 228 n bits 872 index IND_MRBS_SCHEDULE_END_TIME of table `meeting`.`mrbs_schedule` trx id 24004 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 24005, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 11940, OS thread handle 123145557155840, query id 418448 localhost 127.0.0.1 username executing
INSERT INTO MRBS_SCHEDULE (ID,START_TIME,END_TIME,ROOM_ID,CREATE_BY,PRESIDE,REPEAT_ID,DESCRIPTION,NUM,TITLE,PRESIDE_EMAIL,PROJECTOR,CONFERENCE_CALL,CREATE_ID,BOOK_TIME,END_TYPE,EXPECTED_END_TIME) select null,'2019-01-03 19:54:00','2019-01-03 19:54:00',10113685,'z','z',12245756,'fdsfds',10,null,'z@sz.ff.com',0,0,10227544,'2019-01-02 14:38:27.397',0,'2019-01-03 19:54:00' from dual WHERE NOT EXISTS (SELECT * FROM MRBS_SCHEDULE ms where ms.START_TIME<'2019-01-03 19:54:00' and ms.END_TIME>'2019-01-03 19:54:00' and ms.ROOM_ID=10113685)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 228 n bits 872 index IND_MRBS_SCHEDULE_END_TIME of table `meeting`.`mrbs_schedule` trx id 24005 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 55 page no 228 n bits 872 index IND_MRBS_SCHEDULE_END_TIME of table `meeting`.`mrbs_schedule` trx id 24005 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)

Edit: Table MRBS_SCHEDULE primary key use Auto_increment

deadlock mysql database
1 Answers

Deadlock can occur anytime and it is normal behavior. It happens when transaction 1 tries to update Table B using Table A but at the same time Transaction 2 tries to update Table A using Table B.

basicly Transactions holds theirselves and since nobody wants to give up, deadlock occurs.

you can set deadlock threshold for your db using innodb_lock_wait_timeout

From the documentation,

When deadlock detection is enabled (the default), InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above it knows about row-level locks. Otherwise, InnoDB cannot detect deadlocks where a table lock set by a MySQL LOCK TABLES statement or a lock set by a storage engine other than InnoDB is involved. Resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.

Reference: https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-detection.html

user_11189780
Related