Server数据库:使用等待类型LCK_M_IX阻止的插入查询

Question

我们的Java应用程序通过cron作业从日志表中清除数据。下面是为清理而执行的查询：

DELETE FROM AU_TRANSACTIONDATA 
WHERE AU_ACTIVITYENDTIME != 0 
  AND AU_ACTIVITYENDTIME <= 1464570001151

这个表的AU_ACTIVITYENDTIME列有一个索引：

CREATE INDEX [IX_AU_TRANSDATA_ENDTIME] 
    ON [AU_TRANSACTIONDATA]([AU_ACTIVITYENDTIME]) ON [PRIMARY];

我们的应用程序将事务数据(在应用程序中执行API时生成)转储到这个表中。以下是INSERT查询：

INSERT INTO AU_TRANSACTIONDATA (AU_TRANSACTIONID, AU_TRANSACTIONNAME, AU_TRANSACTIONDOMAINID, AU_ACTIVITYNAME, AU_ACTIVITYID, AU_ACTIVITYPID, AU_ACTIVITYTYPE, AU_ACTIVITYSTARTTIME, AU_ACTIVITYENDTIME, AU_ACTIVITYSTATUS, AU_CORRECTDATA, AU_ERRORDATA, AU_USERID, AU_GROUPID, AU_NODENAME, AU_TRANSACTIONDESCRIPTION, AU_SEQUENCEID, AU_TRANSSEQUENCEID) 
VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17)

当日志清理(DELETE查询)和数据处理(INSERT查询)同时进行时，我们面临着问题。我们有大约150万条记录用于清理，所以delete查询需要一些时间来清理日志记录。但在此期间，处理被阻止，没有INSERT查询。

以下是日志：

SPID at Head of Blocking Chain:

SPID [ecid]: 3524 [0]

Blocked by SPID: 0

Client Machine: xxxxx

Client Process ID: 123

Application: jTDS

Login Name: xxxx

Last Batch: 5/30/2016 9:06:56 PM

Wait Type:

Wait Resource:

Wait Time: 00:00:00

Database:

Command Text:

DELETE FROM AU_TRANSACTIONDATA 
WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME <= 1464570001151
----------------------------------------------------------------------

Blocked SPID:

SPID [ecid]: 211 [0]

Client Machine: xxxxx

Client Process ID: 123

Application: jTDS

Login Name: xxxxx

Last Batch: 5/30/2016 9:06:56 PM

Wait Type: LCK_M_IX

Wait Resource: AU_TRANSACTIONDATA.IX_AU_TRANSDATA_ENDTIME

Wait Time: 00:00:24

Database: xxxx

Command Text:

INSERT INTO AU_TRANSACTIONDATA (AU_TRANSACTIONID, AU_TRANSACTIONNAME, AU_TRANSACTIONDOMAINID, AU_ACTIVITYNAME, AU_ACTIVITYID, AU_ACTIVITYPID, AU_ACTIVITYTYPE, AU_ACTIVITYSTARTTIME, AU_ACTIVITYENDTIME, AU_ACTIVITYSTATUS, AU_CORRECTDATA, AU_ERRORDATA, AU_USERID, AU_GROUPID, AU_NODENAME, AU_TRANSACTIONDESCRIPTION, AU_SEQUENCEID, AU_TRANSSEQUENCEID) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 )

日志显示，INSERT语句正在等待资源AU_TRANSACTIONDATA.IX_AU_TRANSDATA_ENDTIME (它表示索引)，等待类型为LCK_M_IX。在一些日志中，我们看到等待资源是AU_TRANSACTIONDATA，它是表本身。

请你解释一下：

1. 为什么在执行日志清理的DELETE查询时插入查询会被阻塞？
2. 什么是LCK_M_IX等待类型以及如何解决它？
3. DELETE查询是锁定整个表还是在索引上放置独占(X)锁？

我不熟悉SQL Server中的等待和锁定策略，因此在这方面的任何帮助都将受到感谢。

编辑：我们已经尝试以块的形式删除数据，一次删除10000行，但这无助于原因。以下是新的删除查询：

SET ROWCOUNT 10000
delete_more:
     DELETE FROM AU_TRANSACTIONDATA 
     WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME <= 1464570001151

IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

Answer 1

如果任何给定会话试图对需要独占锁(如INSERT、UPDATE、DELETE)的数据库执行某些操作，并且在单个事务中对执行超过5000行的操作()，则Server将执行锁升级。

它将不再处理5000+单独的行级锁，而是将独占地锁定整表，因此在提交(或回滚)事务之前，再也不可能进行其他操作--甚至是SELECT查询。