SQL Server Lockout Timeout Exceeded Number of Records in Loop

Question

I am testing a process that deletes many, many records at once. It cannot TRUNCATE TABLE , because there are records that must remain there.

Due to the volume, I broke delete into a loop like this:

 -- Do not block if records are locked. SET LOCK_TIMEOUT 0 -- This process should be chosen as a deadlock victim in the case of a deadlock. SET DEADLOCK_PRIORITY LOW SET NOCOUNT ON DECLARE @Count SET @Count = 1 WHILE @Count > 0 BEGIN TRY BEGIN TRANSACTION -- added per comment below DELETE TOP (1000) FROM MyTable WITH (ROWLOCK, READPAST) WHERE MyField = SomeValue SET @Count == @@ROWCOUNT COMMIT END TRY BEGIN CATCH exec sp_lock -- added to display the open locks after the timeout exec sp_who2 -- shows the active processes IF @@TRANCOUNT > 0 ROLLBACK RETURN -- ignoring this error for brevity END CATCH 

MyTable is a clustered table. MyField is in the first column in the clustered index. It indicates a logical grouping of records, so MyField = SomeValue often selects many records. I don’t care in which order they are deleted if one group is processed at a time. There are no other indexes in this table.

I added a ROWLOCK hint to try to avoid escalating the locks we saw during production. I added a READPAST hint to avoid deleting records blocked by other processes. It should never be, but I try to be safe.

Problem: sometimes this cycle hits the lock timeout 1222 "Request lock time out" when it only works.

I am sure that there is no other activity in this system while I am testing this process, because it is my own developer block, no one is connected, there are no other processes on it, and the profiler does not show any activity.

I can restart the same script after a second, and it picks up the place where it stopped, happily deleting entries - until the next lock timeout.

I tried a BEGIN TRY / BEGIN CATCH ignore error 1222 and try to delete again, but it again crashes again with the same lock timeout error. It also fails if I add a short delay before retrying.

I assume blocking timeouts are due to something like page splitting, but I'm not sure why this would contradict the current loop iteration. The previous delete statement should already be completed, and I thought that meant that all page separators were also completed.

Why does the DELETE loop hit the lock timeout?

Is there a way that a process can avoid this lockout timeout or find that it can be resumed?

This is on SQL Server 2005.

- EDIT -

I added a lock event: timeout in the profiler. It disables PAGELOCK during uninstall:

 Event Class: Lock:Timeout TextData: 1:15634 (one example of several) Mode: 7 - IU Type: 6 - PAGE 

DBCC PAGE reports that these pages are outside the range of the main database (ID 1).

- EDIT 2 -

I added a BEGIN TRY / BEGIN CATCH and ran exec sp_lock in a catch block. Here is what I saw:

 spid dbid ObjId IndId Type Resource Mode Status 19 2 1401108082 1 PAG 1:52841 X GRANT (tempdb.dbo.MyTable) 19 2 1401108082 0 TAB IX GRANT (tempdb.dbo.MyTable) Me 2 1401108082 0 TAB IX GRANT (tempdb.dbo.MyTable) Me 1 1115151018 0 TAB IS GRANT (master..spt_values) (?) 

SPID 19 is the manager of TASK MANAGER. Why will one of these task managers get locks on MyTable?

Answer 1

I found the answer: my closed removal contradicts the process of cleaning ghosts.

Using Nicholas's suggestion, I added BEGIN TRANSACTION and a COMMIT . I wrapped the delete loop in a BEGIN TRY / BEGIN CATCH . In the BEGIN CATCH , right before a ROLLBACK , I ran sp_lock and sp_who2 . (I added code changes in the question above.)

When my process is blocked, I saw the following output:

 spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- -------------------------------- -------- ------ 20 2 1401108082 0 TAB IX GRANT 20 2 1401108082 1 PAG 1:102368 X GRANT SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO ---- ---------- ----- -------- ----- ------ ------------- ------- ------ 20 BACKGROUND sa . . tempdb GHOST CLEANUP 31 0 

In the future, when SQL Server deletes records, it sets a bit for them to simply mark them as “ghost records”. Every few minutes, an internal process called ghost cleansing starts to restore pages of records that have been completely deleted (i.e., all records are ghost records).

The process of cleaning ghosts was discussed at ServerFault in this matter.

Here is Paul S. Randall's explanation of the ghost cleaning process.

You can disable the process of cleaning ghosts using the trace flag. But in this case, I did not need to do this.

As a result, I added a lock timeout of 100 ms. This causes random wait locks during the ghost recording cleanup process, but this is acceptable. I also added our loop, which repeats locking timeouts up to 5 times. With these two changes, my process now usually ends. Now it only gets a timeout if there is a very lengthy process that causes a lot of data around what gets tables or page locks for the data that my process needs to clear.

EDIT 2016-07-20

The final code is as follows:

 -- Do not block long if records are locked. SET LOCK_TIMEOUT 100 -- This process volunteers to be a deadlock victim in the case of a deadlock. SET DEADLOCK_PRIORITY LOW DECLARE @Error BIT SET @Error = 0 DECLARE @ErrMsg VARCHAR(1000) DECLARE @DeletedCount INT SELECT @DeletedCount = 0 DECLARE @LockTimeoutCount INT SET @LockTimeoutCount = 0 DECLARE @ContinueDeleting BIT, @LastDeleteSuccessful BIT SET @ContinueDeleting = 1 SET @LastDeleteSuccessful = 1 WHILE @ContinueDeleting = 1 BEGIN DECLARE @RowCount INT SET @RowCount = 0 BEGIN TRY BEGIN TRANSACTION -- The READPAST below attempts to skip over locked records. -- However, it might still cause a lock wait error (1222) if a page or index is locked, because the delete has to modify indexes. -- The threshold for row lock escalation to table locks is around 5,000 records, -- so keep the deleted number smaller than this limit in case we are deleting a large chunk of data. -- Table name, field, and value are all set dynamically in the actual script. SET @SQL = N'DELETE TOP (1000) MyTable WITH(ROWLOCK, READPAST) WHERE MyField = SomeValue' EXEC sp_executesql @SQL, N'@ProcGuid uniqueidentifier', @ProcGUID SET @RowCount = @@ROWCOUNT COMMIT SET @LastDeleteSuccessful = 1 SET @DeletedCount = @DeletedCount + @RowCount IF @RowCount = 0 BEGIN SET @ContinueDeleting = 0 END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK IF Error_Number() = 1222 -- Lock timeout BEGIN IF @LastDeleteSuccessful = 1 BEGIN -- If we hit a lock timeout, and we had already deleted something successfully, try again. SET @LastDeleteSuccessful = 0 END ELSE BEGIN -- The last delete failed, too. Give up for now. The job will run again shortly. SET @ContinueDeleting = 0 END END ELSE -- On anything other than a lock timeout, report an error. BEGIN SET @ErrMsg = 'An error occurred cleaning up data. Table: MyTable Column: MyColumn Value: SomeValue. Message: ' + ERROR_MESSAGE() + ' Error Number: ' + CONVERT(VARCHAR(20), ERROR_NUMBER()) + ' Line: ' + CONVERT(VARCHAR(20), ERROR_LINE()) PRINT @ErrMsg -- this error message will be included in the SQL Server job history SET @Error = 1 SET @ContinueDeleting = 0 END END CATCH END IF @Error <> 0 RAISERROR('Not all data could be cleaned up. See previous messages.', 16, 1) 

Answer 2

You or someone else using the connection sets a blocking timeout for something other than the default value. See http://msdn.microsoft.com/en-US/library/ms189470(v=SQL.90).aspx for more details.

The default lock time is -1 milliseconds, which means Wait Forever.

Line tips are good, but they are code smells and should be avoided. Let SQL Server do its job. He received more information than about the system as a whole.

For starters, you cannot control the size of locks: Escalation of locks occurs automatically based on the number of outstanding locks. It starts with row locks. If you accumulate too many row locks, SQL Server grows into a page lock. Get too many page locks, and it grows into table locks. See http://msdn.microsoft.com/en-us/library/ms184286(v=SQL.90).aspx for more details. However, there are several trace flags that prevent escalation of locks: however, this will degrade SQL Server performance.

Another thing: you must wrap the DELETE in a transaction, especially in a stored procedure.

 DECLARE @Count INT SET @Count = 1 WHILE @Count > 0 BEGIN BEGIN TRANSACTION DELETE TOP (1000) FROM MyTable WITH (ROWLOCK, READPAST) WHERE MyField = SomeValue SET @Count = @@ROWCOUNT COMMIT TRANSACTION END 

This clearly shows your intent and ensures that locks are released when they should be.