Search code examples
sqldeadlocksybasesap-ase

Encountering deadlock while deleting and running update statistics

I am running a stored procedure that deletes data from a table, the procedure looks like:

SET rowcount 10000
WHILE ( @rows_deleted > 0 )
BEGIN

BEGIN TRAN

DELETE TABLE1 WHERE status = '1'

SELECT @rows_deleted = @@rowcount

COMMIT TRAN

END

While this procedure is running, update statistics is also running on the same table. The table's lock scheme is all pages. I am wondering if the locking is all pages how can it encounter a deadlock? There is nothing else running on this table.

I am using Sybase 12.5 ASE

Solution

  • Found out that update statistics indeed was creating the deadlock. It was taking a shared lock on the table. As the table has locking scheme of all pages, the delete would have to wait for it to complete. But instead of blocking delete query for long time, Sybase chooses to terminate it as the victim.