Transactions (Level 2)

Installing SQL Server

previous page next page

Installing SQL Server

Transactions (Level 2)

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 OFF, a ROLLBACK statement closes any Transact-SQL cursor defined with the SQL-92 form of the DECLARE CURSOR statement, unless the DECLARE CURSOR statement contains either the INSENSITIVE or STATIC keywords. 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.

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 OFF, a ROLLBACK statement closes any Transact-SQL cursor defined with the SQL-92 form of the DECLARE CURSOR statement, unless the DECLARE CURSOR statement contains either the INSENSITIVE or STATIC keywords. 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
REPEATABLE READ	Yes	No
SERIALIZABLE	Yes	Yes

previous page start next page