BUG: Undetected Deadlock on System Catalogs During Dump

• Microsoft SQL Server, version 6.5

SYMPTOMS

An undetected deadlock can occur between a database dump and a user process that holds locks on syscolumns. When this deadlock occurs, all activity within the database is suspended until the deadlock is manually resolved.

CAUSE

When a dump is initiated, the server acquires an exclusive table lock on syslogs to enable it to coordinate user activity with the database dump process. Once this lock is established, the server locks and dumps sysobjects, and then locks and dumps syscolumns. The database dump process then dumps all allocated pages within the database, and deals with any logged user activity through its out-of-sequence queue.

However, when a dump is initiated, it is possible for a user to simultaneously acquire a lock on syscolumns. If the release of this lock is dependent on a logged update, the process will wait for its transaction to be appended to syslogs.

Because the dump process has syslogs exclusively locked, the user process will wait for its request to update syslogs before releasing its lock on syscolumns. The dump process will then attempt to lock syscolumns, and will wait on the user process to release its lock.

This results in an undetected deadlock: sp_who will show the user process sleeping and the database dump blocked on the sleeping process.

WORKAROUND

Killing either the user process or the database dump process will resolve the deadlock and allow activity within the database to continue.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.