Select Isolation Levels

By selecting a transaction isolation level, you can control the degree to which data can be accessed by other users during a transaction.

The ANSI SQL standard defines four levels of isolation for transactions. Each isolation level specifies the kinds of actions that are not permitted while concurrent transactions are processing. Higher levels include the restrictions imposed by lower levels. For more information about isolation levels, see the Adaptive Server Enterprise Transact-SQL Guide.

Note: Replication Server supports not just the ANSI standard values, but all values needed to replicate to any supported data servers.

Level 0 – prevents other transactions from changing data that has already been modified by an uncommitted transaction. However, other transactions can still read the uncommitted data, which results in dirty reads.
Level 1 – prevents dirty reads, which occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change.
Level 2 – prevents nonrepeatable reads, which occur when one transaction reads a row and a second modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield different results than the original read.
Level 3 – ensures that data read by one transaction is valid until the end of the transaction. It prevents “nonrepeatable reads” and “phantom rows” by applying an index page or table lock until the end of the transaction.

Select isolation level 3 if you are using triggers to enforce referential integrity of data across a database. Isolation level 3 prevents phantom rows from occurring in a table while a trigger is executing.

You can set the isolation level using create connection or configure connection with the dsi_isolation_level option. For example, to change the isolation level to 3 for the connection to the pubs2 database on the SYDNEY_DS data server, enter:

alter connection to SYDNEY_DS.pubs2
	set dsi_isolation_level to ’3’

Replication Server sets the isolation-level value to the rs_set_isolation_level function string using the rs_isolation_level system variable. rs_set_isolation_level executes when Replication Server establishes the connection with the replicate data server. If no value has been set, Replication Server does not execute rs_dsi_isolation_level, and instead uses the isolation level of the data server. The default isolation level for Adaptive Server is 1.