Mysql – Help understanding InnoDB Deadlock Detection

deadlocklockingMySQLtransaction

I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but from the status output it seems not. It would be a great help if anyone could provide an explanation of what the output meaning:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-11-27 16:19:43 7f4860dbe700
*** (1) TRANSACTION:
TRANSACTION 1305986614, ACTIVE 4 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 8 row lock(s), undo log entries 3
MySQL thread id 1681095, OS thread handle 0x7f48610ca700, query id 2133831389 127.0.0.1 root executing
Update Resources.ResourceAllocations as ra Set 
                        ra.Act_Arr = Date_Add( ra.Sch_Arr, interval (deplate - ra.CumulativePerformanceAllowance + cpa) second ), 
                        ra.Act_Dep = Date_Add( ra.Sch_Dep, interval (deplate - ra.CumulativePerformanceAllowance + cpa) second ), 
                        ra.Arr_Late = (deplate - ra.CumulativePerformanceAllowance + cpa), 
                        ra.Dep_Late = (deplate - ra.CumulativePerformanceAllowance + cpa),
                        ra.ActualType = 'F'
                    Where ra.ResourceTrainID = tid and (ra.Sch_Arr > pdate or ra.Sch_Dep > pdate) and  ( Select @updateMoveIDs := concat_ws( ', ', ra.ID, @updateMoveIDs ))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986614 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1305986516, ACTIVE 5 sec fetching rows
mysql tables in use 3, locked 3
36316 lock struct(s), heap size 3765800, 3874929 row lock(s)
MySQL thread id 1674629, OS thread handle 0x7f4860dbe700, query id 2133828216 10.22.33.17 stuart Sending data
Create table TrainsToRetrieve (Index (ResourceTrainID))
            SELECT ra.ResourceTrainID, ra.CIFSequence, Simplifier.ReportingLocations.SimplifierSequence
            FROM Resources.ResourceAllocations as ra
            Inner Join Simplifier.ReportingLocations ON ra.Tiploc = Simplifier.ReportingLocations.Tiploc
            inner Join Simplifier.SimplifierNames as SimplifierNames On SimplifierNames.ID = Simplifier.ReportingLocations.SimplifierNameID
            Where ra.DiagramDate >= date_sub(d, interval 1 day) and ra.DiagramDate <= date_add(d, interval 1 day) and Simplifier.ReportingLocations.SimplifierNameID = simnameid and (
                ( ra.Sch_Dep >= date_add(d, interval (SimplifierNames.StartHour) Hour) and ra.Sch_Dep < date_add(d, interval (SimplifierNames.EndDay* 24 + SimplifierNames.EndHour) Hour) ) or 
                ( ra.Sch_Arr >= date_add(d, interval (SimplifierNames.StartHour) Hour) and ra.Sch_Arr < date_add(d, interval (SimplifierNames
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986516 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986516 lock mode S locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Best Answer

Your (2) transaction holds S-lock on table Resources.ResourceAllocations. (1) and (2) transactions wait for X-lock for that table -> deadlock.

What happened:

  1. Trx(2) got S-lock on ResourceAllocations with the select statement (because of create table ... select)
  2. Trx(1) needs X-lock to make update on ResourceAllocations -> wait for X-lock
  3. Trx(2) needs X-lock (for unknown reason) on ResourceAllocations -> wait for X-lock

As the result Trx(1) waits for X-lock from Trx(2) (because Trx(2) has S-lock) and Trx(2) waits for X-lock from Trx(1) (because it is behind Trx(1) in a queue for X-lock).

It's hard for me to say what can be done because I do not see other queries in transactions, but the solution definitely would be: do not require for X-lock in transaction (2).

In simple words: commit transaction(2) after create table statement or do not update tables in this statement from any other transactions until it is done.

Related Question