I'm stuck and confused by a MySQL InnoDB Deadlock. I've run my unit tests on an H2 DB and everything runs properly. However, running against a MySQL DB (isolation: REPEATABLE_READ or READ_COMMITTED) fails.
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.GeneratedConstructorAccessor55.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
.....
.....
When I look at the InnoDB Engine status (show engine innodb status), I see the following:
------------------------
LATEST DETECTED DEADLOCK
------------------------
140711 15:23:48
*** (1) TRANSACTION:
TRANSACTION 0 1395766, ACTIVE 0 sec, OS thread id 4351500288 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 3 row lock(s), undo log entries 2
MySQL thread id 5, query id 746 localhost 127.0.0.1 test Updating
update contract set version = version + 1 where id = 3
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3650 n bits 72 index `PRIMARY` of table `unit_tests`.`contract` trx id 0 1395766 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 8; hex 8000000000000003; asc ;; 1: len 6; hex 000000154c2f; asc L/;; 2: len 7; hex 0000000e751814; asc u ;; 3: len 8; hex 800012515fefa719; asc Q_ ;; 4: len 8; hex 800012515fefa719; asc Q_ ;; 5: len 12; hex 6e756d6265725f3132333435; asc number_12345;; 6: len 10; hex 747970655f3132333435; asc type_12345;; 7: len 8; hex 800012515fefa7ee; asc Q_ ;; 8: len 8; hex 800012515fefa7ba; asc Q_ ;; 9: len 4; hex 80000008; asc ;; 10: len 12; hex 766f6c756d655f3132333435; asc volume_12345;; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 0 1395765, ACTIVE 0 sec, OS thread id 4385959936 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1216, 4 row lock(s), undo log entries 2
MySQL thread id 9, query id 751 localhost 127.0.0.1 test Updating
update contract set version = version + 1 where id = 3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3650 n bits 72 index `PRIMARY` of table `unit_tests`.`contract` trx id 0 1395765 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 8; hex 8000000000000003; asc ;; 1: len 6; hex 000000154c2f; asc L/;; 2: len 7; hex 0000000e751814; asc u ;; 3: len 8; hex 800012515fefa719; asc Q_ ;; 4: len 8; hex 800012515fefa719; asc Q_ ;; 5: len 12; hex 6e756d6265725f3132333435; asc number_12345;; 6: len 10; hex 747970655f3132333435; asc type_12345;; 7: len 8; hex 800012515fefa7ee; asc Q_ ;; 8: len 8; hex 800012515fefa7ba; asc Q_ ;; 9: len 4; hex 80000008; asc ;; 10: len 12; hex 766f6c756d655f3132333435; asc volume_12345;; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3650 n bits 72 index `PRIMARY` of table `unit_tests`.`contract` trx id 0 1395765 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 8; hex 8000000000000003; asc ;; 1: len 6; hex 000000154c2f; asc L/;; 2: len 7; hex 0000000e751814; asc u ;; 3: len 8; hex 800012515fefa719; asc Q_ ;; 4: len 8; hex 800012515fefa719; asc Q_ ;; 5: len 12; hex 6e756d6265725f3132333435; asc number_12345;; 6: len 10; hex 747970655f3132333435; asc type_12345;; 7: len 8; hex 800012515fefa7ee; asc Q_ ;; 8: len 8; hex 800012515fefa7ba; asc Q_ ;; 9: len 4; hex 80000008; asc ;; 10: len 12; hex 766f6c756d655f3132333435; asc volume_12345;; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL;
I see the deadlock is occuring around the following SQL:
update contract set version = version + 1 where id = 3
So I updated my test to simply execute 50 simultaneous update transactions, but I cannot replicate it with a such a basic test. So I'm relegated to looking at the status to try and determine why/how this simple SQL could cause a deadlock.
Is anyone able to better understand the log and explain what is happening? I see that T1 is waiting for an eXclusive lock, T2 has a Shared lock and is also waiting on an eXclusive lock. But I don't understand why MySQL doesn't issue the eXclusive lock to T2 then instead of rolling back the transaction.
And even more importantly, how do I resolve this? I saw another post on StackOverflow that points to an AOP solution to retry the transaction, but that seems more like a hack than really solving the issue.
Is there a way to really trace the lock allocation/release to see exactly what sequence is causing the error?
