为什么删除语句到一个表，将一个选择语句锁定到同一个表——SQL Server

Question

我有一个用Spring 3.0编写的应用程序，并使用SQL Server 2012 Enterprise作为RDS。

我一直在我的 DAO 操作中使用@Transactional，在这种特殊情况下

    @Transactional
    public void removeAll(String token) {
        cacheDao.delete(token);
    }
    public ClassSome getValue(String id) {
        return cacheDao.getValue(id);
    }

我的删除操作是事务性的，但未指定选择操作。这就是下面发生死锁的原因吗？

最近我一直在数据库中遇到死锁。主要是上述两个操作出现死锁。

我不明白对单个表的查询如何相互死锁。以下是DBA给我的死锁列表示例：

<deadlock-list>
<deadlock victim="process1180f5d498">
  <process-list>
   <process id="process1180f5d498" taskpriority="0" logused="0" waitresource="OBJECT: 6:1266103551:0 " waittime="2141" ownerId="1748561" transactionname="SELECT" lasttranstarted="2013-12-25T11:24:17.140" XDES="0x117ce7ba40" lockMode="S" schedulerid="30" kpid="4424" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2013-12-25T11:24:17.140" lastbatchcompleted="2013-12-25T11:24:17.137" lastattention="1900-01-01T00:00:00.137" clientapp="Microsoft SQL Server JDBC Driver" hostname="xxxx" hostpid="0" loginname="xxxx" isolationlevel="read committed (2)" xactid="1748561" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x02000000e0a92205aebcb9dd3f38539312f56b0c41af55990000000000000000000000000000000000000000">
select token, type, value from cache where token=@P0 and type=@P1     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 varchar(8000),@P1 varchar(8000))select token, type, value from cache where token=@P0 and type=@P1                    </inputbuf>
   </process>
   <process id="process117d375c38" taskpriority="0" logused="0" waitresource="OBJECT: 6:1266103551:29 " waittime="2141" ownerId="1748560" transactionname="implicit_transaction" lasttranstarted="2013-12-25T11:24:17.140" XDES="0xf68c743a8" lockMode="X" schedulerid="64" kpid="9628" status="suspended" spid="96" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-12-25T11:24:17.140" lastbatchcompleted="2013-12-25T11:24:17.140" lastattention="1900-01-01T00:00:00.140" clientapp="Microsoft SQL Server JDBC Driver" hostname="xxxx" hostpid="0" loginname="xxx" isolationlevel="read committed (2)" xactid="1748560" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x0200000023b477359eec278e4060e11f3a1c194cbed41cc10000000000000000000000000000000000000000">
delete cache where token=@P0 and type=@P1     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 varchar(8000),@P1 varchar(8000))delete cache where token=@P0 and type=@P1                    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="1266103551" subresource="FULL" dbid="6" objectname="xxxx.dbo.cache" id="lock10034ef580" mode="X" associatedObjectId="1266103551">
    <owner-list>
     <owner id="process117d375c38" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process1180f5d498" mode="S" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="29" objid="1266103551" subresource="FULL" dbid="6" objectname="xxxx.dbo.cache" id="lock1011729e00" mode="IS" associatedObjectId="1266103551">
    <owner-list>
     <owner id="process1180f5d498" mode="IS"/>
    </owner-list>
    <waiter-list>
     <waiter id="process117d375c38" mode="X" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
</deadlock>
</deadlock-list>

编辑 #1

-- Results for: 
    SELECT  i.name, i.allow_row_locks, i.allow_page_locks
    FROM    sys.indexes i
    WHERE   i.object_id = OBJECT_ID(N'dbo.cache')
name    allow_row_locks allow_page_locks
NULL    1   1
IND_cache_token_type    1   1

Answer 1

考虑我们有这些表：

Department (department-id (pk) , name);
Emp (EMP_ID (pk), department-id (FK), name);

从部门中删除行时，为了保持完整性，RDMBS 必须在表中搜索和查找Emp子记录。

当表中Emp department-id列上没有索引时，Emp会发生全表扫描，RDBMS将在操作前锁定整个Emp表。

如果Emp有很多记录，操作将需要很长时间，同时如果其他事务试图操纵Emp，可能会发生超时或死锁。

强烈建议在外键上创建索引以防止此问题。

需要更多信息来调查您的问题，我刚刚提到了一个常见问题。

Answer 2

> 1）最有可能的是，此DL的原因是缺少索引。尝试创建以下索引：

CREATE /*UNIQUE*/ INDEX IX_Cache_Token_Type
ON dbo.Cache (Token, Type)
INCLUDE (Value)

此索引应该有助于两种类型的查询：

select token, type, value from cache where token=@P0 and type=@P1 

和

delete cache where token=@P0 and type=@P1

2）如果这个索引没有消除这些DL，那么应该问问自己为什么两个并发事务试图删除/读取同一行？

3） 切换到快照隔离并不是一个简单的决定：

• 快照隔离：对完整性的威胁（第 1 部分）？
• 快照隔离：对完整性的威胁（第 2 部分）？
• 快照隔离：对完整性的威胁（第 3 部分）？
• 快照隔离：对完整性的威胁（第 4 部分）？

编辑#1：

DECLARE @sqlhandle1 VARBINARY(64);
SET @sqlhandle1 = 0x02000000e0a92205aebcb9dd3f38539312f56b0c41af55990000000000000000000000000000000000000000
SELECT  qp.query_plan
FROM    sys.dm_exec_query_plan(@sqlhandle1) qp
DECLARE @sqlhandle2 VARBINARY(64);
SET @sqlhandle2 = 0x0200000023b477359eec278e4060e11f3a1c194cbed41cc10000000000000000000000000000000000000000
SELECT  qp.query_plan
FROM    sys.dm_exec_query_plan(@sqlhandle2) qp

编辑#2：我们还需要检查是否允许行/页面锁定：

SELECT  i.name, i.allow_row_locks, i.allow_page_locks
FROM    sys.indexes i
WHERE   i.object_id = OBJECT_ID(N'dbo.cache')