select r.*
from reservations r
where r.workspaceSid = 'WS0c9faa70166e42f4cf3a8df4b11b4d73'
and r.workerSid = 'WK89d7521c91805d7f0a8a2d38a6ec7ab4'
and r.status IN (0, 6) for update
2023-03-22T10:35:38.458591Z 33536411 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 474931 page no 14657 n bits 416 index fk_workspace_sid of table twilio_wds
.reservations
trx id 68165090733 lock_mode X waiting
2023-03-22T10:35:38.458616Z 33536411 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 68165090729, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 89 lock struct(s), heap size 8400, 170 row lock(s), undo log entries 1 MySQL thread id 33536411, OS thread handle 139955900372736, query id 47887747705 172.25.89.40 twilio Sending data
select r.*
from reservations r
where r.workspaceSid = 'WS0c9faa70166e42f4cf3a8df4b11b4d73'
and r.workerSid = 'WK53a59d20955be23871feb083639ecd30'
and r.status IN (0, 6) for
update
2023-03-22T10:35:38.458647Z 33536411 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 474931 page no 14657 n bits 416 index fk_workspace_sid of table twilio_wds
.reservations
trx id 68165090729 lock_mode X
2023-03-22T10:35:38.458665Z 33536411 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
This is the innodb status page deadlock result. The rows accessed in these transactions are different and hence there should be no contention that could cause deadlock right?
I tried to understand the rows affected and the rows were different.
This composite index may help:
In any case, your app must plan for a Deadlock. The usual code is to catch it and replay the entire transaction.