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)
and ( Select ... )
? !ra
have an index onResourceTrainID
? Please provideSHOW CREATE TABLE
.