#Sql lock request time out period exceeded code

(I added the code changes in the question above.) In the BEGIN CATCH, right before a ROLLBACK, I ran sp_lock and sp_who2. I wrapped the delete loop in a BEGIN TRY / BEGIN CATCH. Using Nicholas's suggestion, I added a BEGIN TRANSACTION and a COMMIT. I've found the answer: my looped delete is conflicting with the ghost cleanup proc. Why would one of these task managers be acquiring locks on MyTable? Here is what I saw: spid dbid ObjId IndId Type Resource Mode Statusġ9 2 1401108082 1 PAG 1:52841 X GRANT ()ġ9 2 1401108082 0 TAB IX GRANT () I added a BEGIN TRY / BEGIN CATCH and ran an exec sp_lock in the catch block. TextData: 1:15634 (one example of several)ĭBCC PAGE reports these pages are outside of the range of the master database (ID 1).

It's timing out on a PAGELOCK during the delete: Event Class: Lock:Timeout I added the Lock:Timeout event to the profiler. Is there a way the process can avoid this lock timeout or detect that it is safe to resume? Why is the DELETE loop hitting a lock timeout against itself? The prior delete statement should have already completed, and I thought that meant any page splits were also finished. I assume the lock timeouts are because of something like a page split, but I am not sure why this would conflict with the current loop iteration. It also fails again if I add a short delay before retrying. I have tried a BEGIN TRY / BEGIN CATCH to ignore the 1222 error and retry the delete, but it fails again immediately with the same lock timeout error. I can re-run the same script a second later and it picks up where it left off, happily deleting records- until the next lock timeout. I am positive there is no other activity on this system while I am testing this process, because it is my own developer box, nobody else is connected, there are no other processes running on it, and the profiler shows no activity. Problem: sometimes this loop hits a lock timeout 1222 'Lock request time out period exceeded' when it is the only thing running. That should never happen, but I am trying to be safe.

I added the READPAST hint to avoid deleting records locked by other processes. I added the ROWLOCK hint to try to avoid lock escalations we have seen in production. There are no other indexes on this table.

I don't care in which order they are deleted so long as one group is processed at a time. It indicates a logical grouping of records, so MyField = SomeValue often selects many records. MyField is in the first column in the clustered index. This process should be chosen as a deadlock victim in the case of a deadlock.īEGIN TRANSACTION - added per comment belowĭELETE TOP (1000) FROM MyTable WITH (ROWLOCK, READPAST) WHERE MyField = SomeValueĮxec sp_lock - added to display the open locks after the timeoutĮxec sp_who2 - shows the active processes It cannot TRUNCATE TABLE, because there are records in there that need to stay.īecause of the volume, I have broken the delete into a loop similar to this: - Do not block if records are locked. I am testing a process that deletes many, many records at once.