Transactions (Level 2)

Transactions (Level 2)

  Topic last updated -- July 2003

SQL Server 6.x SQL Server 2000
When CURSOR_CLOSE_ON_COMMIT was set OFF, a ROLLBACK statement did not close a Transact-SQL cursor defined with the DECLARE CURSOR statement. Server cursors opened through database API functions were also left open after a ROLLBACK statement. When CURSOR_CLOSE_ON_COMMIT is set to ON, any COMMIT or ROLLBACK statement, or any action that causes the transaction to end, closes all defined Transact-SQL cursors. All API server cursors are also closed unless they have been defined as STATIC cursors (such as using the ODBC SQL_CURSOR_STATIC attribute).

Expect different results as compared to earlier versions of SQL Server. Reopen all cursors after issuing a ROLLBACK statement.

The REPEATABLE READ clause of the SET TRANSACTION ISOLATION LEVEL statement behaved identically to the SERIALIZABLE clause. There was no way to ensure repeatable reads without also protecting against phantoms (after a rollback, the value read logically never existed). Transactions that required REPEATABLE READ semantics had to pay the additional concurrency penalty of serializability. The REPEATABLE READ clause now does not necessarily protect against phantoms. Serializable transactions, set using the SERIALIZABLE clause of SET TRANSACTION ISOLATION LEVEL, allow less concurrency than the REPEATABLE READ clause because they protect against phantoms.

Expect different results as compared to earlier versions of SQL Server. Many applications only need REPEATABLE READ semantics for correct operation. Use the REPEATABLE READ clause of SET TRANSACTION ISOLATION LEVEL for applications requiring REPEATABLE READ semantics but that do not need phantom protection. If phantom protection is required, use the SERIALIZABLE clause.

Here is a summary of phantom protection for both SQL Server versions 6.5 and SQL Server 2000 using SET TRANSACTION ISOLATION LEVEL.

Phantom protection SQL Server 6.5 SQL Server 2000