选择查询中mssql中索引的意外锁定

Question

我不断得到

Caused by: java.sql.SQLException: Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

所以我用

DBCC TRACEON(1222,-1)
DBCC TRACEON(1204,-1)

以下死锁信息打印在日志中：

这些行表示已检测到死锁，进程1已被选为受害者。

process-list
deadlock victim=PROCESS_ID_1
deadlock-list

进程id_1的日志

   [1] (@P0 nvarchar(4000))select namesequen0_.id as id1_47_<c/> namesequen0_.container_id as containe9_47_<c/> namesequen0_.increment_ as incremen2_47_<c/> namesequen0_.nextValue as nextValu3_47_<c/> namesequen0_.numChars as numChars4_47_<c/> namesequen0_.padChar as padChar5_47_<c/> namesequen0_.scope as scope6_47_<c/> namesequen0_.sequenceType as sequence7_47_<c/> namesequen0_.uppercase as uppercas8_47_ from NameSequence namesequen0_ where (namesequen0_.scope like  @P0 ) and (namesequen0_.container_id is null)        
    [2] frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x020000005734e00acfb3060d49cc0e8565acb3b105807744
    [3] executionStack
    [4] process id=PROCESS_ID_1 taskpriority=0 logused=1248 waitresource=KEY: 14:72057594053459968 (95c9fddfaf17) waittime=3506 ownerId=13645568 transactionname=implicit_transaction lasttranstarted=2015-06-02T07:52:41.660 XDES=0xa30dd950 lockMode=S schedulerid=1 kpid=1848 status=suspended spid=68 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-06-02T07:52:41.663 lastbatchcompleted=2015-06-02T07:52:41.660 clientapp=jTDS hostname=QALAB6 hostpid=123 loginname=qalab6_nrm isolationlevel=read committed (2) xactid=13645568 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058

进程id_2的日志

[1]select card0_.id as id2_77_<c/> card0_.autoDisplayName as autoDisp3_77_<c/> card0_.autoPartialName as autoPart4_77_<c/> card0_.namedWithSequence_id as namedWi38_77_<c/> card0_.namedWithSequenceValue as namedWit5_77_<c/> card0_.userDisplayName as userDisp6_77_<c/> card0_.userPartialName as userPart7_77_<c/> card0_.lifeCycleState_id as lifeCyc40_77_<c/> card0_.model_id as model41_77_<c/> card0_.outOfService as outOfSe12_77_<c/> card0_.bottomClearance as bottomC13_77_<c/> card0_.leftClearance as leftCle14_77_<c/> card0_.rightClearance as rightCl15_77_<c/> card0_.topClearance as topClea16_77_<c/> card0_.ncmElementKey as ncmElem24_77_ from VfdItem card0_ where card0_.DTYPE='Card' and card0_.ncmElementKey= @P0
[2]frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x02000000386ba721896ce39b0b7c9d01df11539c0d843c83
[3]executionStack
[4]process id=PROCESS_ID_2 taskpriority=0 logused=62048 waitresource=KEY: 14:72057594051297280 (fadae9b0c9d3) waittime=3333 ownerId=13645330 transactionname=implicit_transaction lasttranstarted=2015-06-02T07:52:39.570 XDES=0xbf9f5950 lockMode=S schedulerid=1 kpid=5344 status=suspended spid=66 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-06-02T07:52:41.833 lastbatchcompleted=2015-06-02T07:52:41.813 clientapp=jTDS hostname=QALAB6 hostpid=123 loginname=qalab6_nrm isolationlevel=read committed (2) xactid=13645330 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058

下面一行给出了process_id_1的细节，

参考文献[1]是我们想要执行的查询。。。注意：这是对表名和表名的select查询。container\u id是来自表名vfditem的外键。

Ref[4]说它正在等待资源并被暂停

下面一行给出了process_id_2死锁的详细信息process_id_1

参考文献[1]给出想要运行的查询。。。注意，这是对表名vfditem和card0的选择查询。namedWithSequence\u id是表名中的外键

Ref[4]说它正在等待资源并被暂停

下面几行打印所有被锁定的资源，以及由谁和谁在等待该资源。

[11] waiter id=PROCESS_ID_2 mode=S requestType=wait
[10] waiter-list
[9] owner id=PROCESS_ID_1 mode=X
[8] owner-list
[7] keylock hobtid=72057594051297280 dbid=14 objectname=qalab6_nrm.dbo.vfditem indexname=vfditem_pk id=lock85330d00 mode=X associatedObjectId=72057594051297280
[6] waiter id=PROCESS_ID_1 mode=S requestType=wait
[5] waiter-list
[4] owner id=PROCESS_ID_2 mode=X
[3] owner-list
[2] keylock hobtid=72057594053459968 dbid=14 objectname=qalab6_nrm.dbo.namesequence indexname=NameSequence_PK id=lock848c3380 mode=X associatedObjectId=72057594053459968
[1] resource-list

这就意味着

[1] 资源qalab6\u nrm。dbo。namesequence indexname=namesequence\u PK被进程id\u 2以独占锁定模式锁定，进程id\u 1正在等待它。

[2]资源qalab6_nrm.dbo.vfditemindexname=vfditem_pk被process_id_1以独占锁定模式锁定，process_id_2正在等待它。

宾果，我们可以看到它是如何陷入僵局的

现在奇怪的是

如果仔细观察，process\u id\u 1试图从表名和列中运行select查询。container\u id是表vfditem的外键，并且进程id\u 1已经获得vfditem表的锁

[1]为什么process_is_1获取vfdItem索引的锁？

进一步的process_id_2正在尝试从表名vfdItem和列运行选择查询vfditem.namesequence_id是表NameSequence和的外键process_id_2已经获取了Namesequence表的indexname=NameSequence_PK的锁

[2] 为什么进程2会在名称序列的索引上获得锁？




Answer 1

MSSQL对select语句使用锁定，它可以执行表、页和行锁定。这实际上取决于您想在系统中做什么，更重要的是，一个好的指针是查看可以放在SELECT语句上的表提示，尤其是查看NOWAIT、NOLOCK、READCOMMITED和READ\U COMMITED\U SNAPSHOT

您可能还需要考虑在所有事务上设置隔离级别，以避免在其他地方出现此问题。

这是一些非常基本的信息，DBA可能能够提供更详细的答案，但这些选项将阻止该问题的发生

您可以通过运行DBCC useroptions来判断隔离级别，也可以通过从sys运行SELECT is\u read\u committed\u snapshot\u来判断read\u committed\u快照。名称为“YourDatabase”的数据库