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:

2015-11-27 16:19:43 7f4860dbe700
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 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 ))
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
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 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
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
  • This looks pretty much exactly like the situation I had to deal with recently. See this SO post for explanation and options: stackoverflow.com/a/23816970/1797579 – Willem Renzema Nov 28 '15 at 6:47
  • What the heck is and ( Select ... ) ? ! – Rick James Dec 12 '15 at 22:54
  • Does ra have an index on ResourceTrainID? Please provide SHOW CREATE TABLE. – Rick James Dec 12 '15 at 22:55

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.

| improve this answer | |
  • CREATE TABLE, I think, closes the open transaction (if any) and is its own transaction. – Rick James Dec 12 '15 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.