在没有任何显式事务的情况下运行的SELECT...JOIN
查询与包含INSERT
s的事务之间发生死锁。
INSERT
有我们无法更改的订单(插入人员,然后插入就业)。然而,SELECT
子句中使用的表似乎以相反的顺序锁定(Employment then Person)。
不幸的是,更改SELECT
(即所涉及的表的顺序)并不容易,因为它是由复杂的遗留引擎生成的。
有没有办法按照我们需要的顺序锁定SELECT...JOIN
查询中使用的表?
我不想使用像快照隔离这样的复杂解决方案。我也不想在SELECT
中使用NOLOCK
。
死锁图xml:
<deadlock>
<victim-list>
<victimProcess id="process8bfdd2188" />
</victim-list>
<process-list>
<process id="process8bfdd2188" taskpriority="0" logused="0" waitresource="PAGE: 223:1:769193 " waittime="4340" ownerId="17504759932" transactionname="SELECT" lasttranstarted="2016-04-02T03:03:25.527" XDES="0x63f8f1cc0" lockMode="S" schedulerid="2" kpid="27920" status="suspended" spid="954" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2016-04-02T03:03:25.523" lastbatchcompleted="2016-04-02T03:03:25.523" lastattention="1900-01-01T00:00:00.523" clientapp="Security Services" hostname="A-PRI0010IIS" hostpid="29908" loginname="SWE2078262_EOUser" isolationlevel="read committed (2)" xactid="17504759932" currentdb="223" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="58" sqlhandle="0x020000000b6f1301768ce09dcc48ead49292f31b2305871b0000000000000000000000000000000000000000">
select distinct em.EmploymentId.. from employment.Employment as em left join employment.Person as p on ... </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@dp1 datetime,@dp2 datetime)select em.EmploymentId from employment.Employment as em left join employment.Person as p on ...</inputbuf>
</process>
<process id="process8e5957498" taskpriority="0" logused="120220" waitresource="OBJECT: 223:1938105945:0 " waittime="4207" ownerId="17504759159" transactionname="user_transaction" lasttranstarted="2016-04-02T03:03:25.333" XDES="0x254d02d08" lockMode="IX" schedulerid="1" kpid="23832" status="suspended" spid="924" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-04-02T03:03:25.703" lastbatchcompleted="2016-04-02T03:03:25.703" lastattention="1900-01-01T00:00:00.703" clientapp="Security Services" hostname="A-PRI0010IIS" hostpid="29908" loginname="SWE2078262_EOUser" isolationlevel="read uncommitted (1)" xactid="17504759159" currentdb="223" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="360" sqlhandle="0x020000002d54221bf934de11467adec42e4bb5413da44e120000000000000000000000000000000000000000">
insert [employment].[Employment]([EmploymentId], [CompanyId], [PersonId], ...</frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier,@3 varchar(30),@4 datetime2(7),@5 datetime2(7),@6 nvarchar(50),@7 decimal(18,2),@8 nvarchar(50),@9 bit,@10 varchar(15))insert [employment].[Employment]([EmploymentId], [CompanyId], [PersonId]...
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="769193" dbid="223" subresource="FULL" objectname="SWE2078262_Core.employment.Person" id="lock1f1566080" mode="IX" associatedObjectId="72057594248167424">
<owner-list>
<owner id="process8e5957498" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process8bfdd2188" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
<objectlock lockPartition="0" objid="1938105945" subresource="FULL" dbid="223" objectname="SWE2078262_Core.employment.Employment" id="lock2341db7480" mode="S" associatedObjectId="1938105945">
<owner-list>
<owner id="process8bfdd2188" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process8e5957498" mode="IX" requestType="wait" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
您可能仍然想使用其中一个:
- 乐观并发,即数据库和选择查询的快照隔离模式
- 重复死锁事务。我的意思是,您需要在C#代码中捕获SqlException(我假设您使用C#)并重复查询(可以是insert或select)