帮助理解InnoDB死锁检测

Question

在单独的线程中执行Update和Select时会出现死锁，但我不确定如何处理它。以前，我的理解是，这种情况可以并行发生，但从状态输出来看，似乎并非如此。如果有人能解释输出的含义，这将是一个很大的帮助：

------------------------
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)

Answer 1

您的(2)事务在表Resources.ResourceAllocations上持有S锁.(1)和(2)事务等待该表->死锁的X锁。

发生了什么：

1. Trx(2)使用select语句在ResourceAllocations上获得S锁(因为创建了表.(选择)
2. Trx(1)需要X锁来更新ResourceAllocations ->，等待X锁。
3. Trx(2)需要ResourceAllocations ->上的X锁(原因不明)等待X锁

作为结果，Trx(1)等待来自Trx(2)的X-锁(因为Trx(2)具有S-锁)和Trx(2)等待来自Trx(1)的X-锁(因为它在队列中等待X-锁)。

我很难说出可以做什么，因为我看不到事务中的其他查询，但解决方案肯定是:不需要在事务(2)中使用X锁。

简单地说:提交事务(2)在create语句之后，或者在完成之前不要从任何其他事务中更新该语句中的表。