Oracle Waveset 8.1.1 System Administrator's Guide

SQL Server Databases

Some customers who used an SQL Server 2000 database as a repository reported that as concurrency increased, SQL Server 2000 reported deadlocking problems that were related to SQL Server’s internal use of pessimistic locking (primarily lock escalation).

These deadlock errors display in the following format:


com.waveset.util.IOException:
  ==> com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 51) 
was deadlocked on lock | communication buffer resources with another 
process and has been chosen as the deadlock victim. Rerun the transaction.

    To prevent or address deadlocking problems, do the following:

  1. Use the SQL Server 2005 database.

  2. Configure the READ_COMMITTED_SNAPSHOT parameter by formatting the command as follows:

    ALTER DATABASE waveset SET READ_COMMITTED_SNAPSHOT ON

    Enabling the READ_COMMITTED_SNAPSHOT parameter does the following:

    • Removes contention during the execution of SELECT statements that can cause blocks, which greatly reduces the potential for deadlocks internal to SQL Server.

    • Prevents uncommitted data from being read and guarantees that SELECT statements receive a consistent view of committed data.

    For more information about the READ_COMMITTED_SNAPSHOT parameter, see:http://msdn.microsoft.com/en-us/library/ms188277.aspx.