Deadlocks in SQL Server 7

Metastorm

The information in this article applies to product: all versions of e-Work 

Issue

• Deadlocks occur during queries against eAlert table. This problem has been reported against e-Work version 5.1.1 with MS SQL Server 7. It may also affect e-Work versions 5.2 and 5.3.

• The error reported is:

  1. Database exception while trying to GetAlertTree. Operation: open & extract records. Exception: -1. Description: Your transaction (process ID \#26) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.
  4. Unable to open table while doing open on HDynamicTableSet. Table name: eAlert. Additional info: filter: eFolderID='PAN$WTCNT$00012469' AND (eAlertType='' OR eAlertType=' ') - Error message: Your transaction (process ID \#21) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.
  7. The errorlog shows that the access violation arises while SQL Server is trying to report information about a deadlock which has just occurred. The stack trace reveals that SQL Server is in a routine called DeadlockSearch::PrintDeadlockDetails when the problem occurs.
• After the deadlock error, it appears that the engine loses one of its database connections. This causes conditional actions to stop working until the engine is restarted (sometimes SQL Server must be restarted also).

Resolution

• If you receive error \#1 or error \#2 above and are using e-Work 5.3 or earlier:

  1. Stop any applications talking to the e-Work database, including the engine.
  2. Open SQL Server Query Analyser. Connect, and switch to the e-Work database.

  3. Run the following SQL script:

     DROP INDEX eAlert.eMapName
     GO
     CREATE INDEX eMapName ON eAlert(eMapName, eStageName)
     GO
     UPDATE STATISTICS eAlert
     GO

     This should improve the situation. When the script is executed, it removes the clustered index and replaces it with a similar non-clustered index - reducing the chances for contention. Because the effects of this should basically be internal to SQL Server, there should not be any functional implications.

  -OR-

  1. Upgrade to the 5.3.1 engine. This includes changes that improve the way the engine deals with the deadlock situation.
• If you receive error \#3 above or if you are already using e-Work 5.3.1 or later:
  Install SP4 for SQL Server 7.