Simple transactions cause deadlocks for each other by UPDATE queries locking the primary key
Hi
I have the following lines in my error log (often repeating after a recent code update):
------------------------------------------------------
*** (1) TRANSACTION:
TRANSACTION 2959776709, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 206844, OS thread handle 0x7f2d9bcf3700, query id 237435182 <IP> <USER> updating
UPDATE Member SET LastLoginDate=NOW(), LoginCount=IFNULL(LoginCount,0)+1, LastLoginIP='<IP>' WHERE MemberID=229101 and RemovedDate IS NULL
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1054808 page no 4126 n bits 136 index `PRIMARY` of table `<SCHEMA>`.`member` trx id 2959776709 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 2959776706, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
16 lock struct(s), heap size 2936, 10 row lock(s), undo log entries 3
MySQL thread id 206827, OS thread handle 0x7f2d9876d700, query id 237435203 <IP> <USER> updating
update Member set LastUsedOrderID=6705560 where RemovedDate is null and MemberID=229101
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1054808 page no 4126 n bits 136 index `PRIMARY` of table `<SCHEMA>`.`member` trx id 2959776706 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1054808 page no 4126 n bits 136 index `PRIMARY` of table `<SCHEMA>`.`member` trx id 2959776706 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------------------------------------------------
Now, if you look at the second transaction it seems to be waiting for the same lock that it holds (same transaction number and same lock info). That really can't be the case, as this opposes everything I've learned. So can someone explain to me what is going on?
The first transaction (1) consists of that single query only, so it's a really simple transaction.
The second transaction (2) includes some other queries, which look like this:
2A)
Insert Into `Order`(MemberId, SessionId, StoreId, AddressId, StoreDefaultPercentageAddon, ProductsTotalVATAmount, GeneralDiscountPercentage, IsArchived, CreationDate, LastModifiedDate) Values(?p_MemberId, ?p_SessionId, ?p_StoreId, ?p_AddressId, 0, 0, 0, 0, Now(), Now());
2B)
Update `Order` O, Store S
Set O.StoreDefaultPercentageAddon=S.DefaultPercentageAddon
Where O.OrderId={0}
And O.StoreId=S.StoreId
And S.RemovedDate Is Null;
2C)
Update `Order` O Set O.MemberID=?p_MemberID Where O.OrderId=?p_OrderID;
2D)
update Member set LastUsedOrderID=?p_OrderID where RemovedDate is null and MemberID=?p_MemberID;
I've searched the binary log for other transactions/queries that could lock the table but there are no other transactions/queries participating in this deadlock.