Choose an Isolation Level

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 executing. Higher levels include the restrictions imposed by the lower levels.

Level 0 – ensures that data written by one transaction represents the actual data. Level 0 prevents other transactions from changing data that has already been modified (through an insert, delete, update, and so on) by an uncommitted transaction. The other transactions are blocked from modifying that data until the transaction commits. However, other transactions can still read the uncommitted data, which results in dirty reads.
Level 1 – prevents dirty reads. Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid. Level 1 is the default isolation level supported by SAP ASE.
Level 2 – prevents nonrepeatable reads, which occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield different results than the original read.

SAP ASE supports level 2 for data-only-locked tables. It is not supported for allpages-locked tables.
Level 3 – ensures that data read by one transaction is valid until the end of that transaction, preventing phantom rows. SAP ASE supports this level through the holdlock keyword of the select statement, which applies a read-lock on the specified data. Phantom rows occur when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies the data (through an insert, delete, update, and so on). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows.

You can set the isolation level for your session by using the transaction isolation level option of the set command. You can enforce the isolation level for only a single query as opposed to using the at isolation clause of the select statement. For example:

set transaction isolation level 0

Default Isolation Levels for SAP ASE and ANSI SQL
By default, the SAP ASE transaction isolation level is 1. The ANSI SQL standard requires level 3 to be the default isolation for all transactions. This prevents dirty reads, nonrepeatable reads, and phantom rows.
Dirty Reads
Applications that are not impacted by dirty reads may have better concurrency and reduced deadlocks when accessing the same data if you set transaction isolation level 0 at the beginning of each session.
Repeatable Reads
A transaction performing repeatable reads locks all rows or pages read during the transaction. After one query in the transaction has read rows, no other transaction can update or delete the rows until the repeatable-reads transaction completes.
Find the Status of the Current Isolation Level
The global variable @@isolation contains the current isolation level of your Transact-SQL session. Querying @@isolation returns the value of the active level (0, 1, or 3).
Change the Isolation Level for a Query
Change the isolation level for a query by using the at isolation clause with the select or readtext statements.
Isolation Level Precedences
Precedence rules apply to different methods of defining isolation levels.
Cursors and Isolation Levels
SAP ASE provides three isolation levels for cursors.
Stored Procedures and Isolation Levels
System procedures always operate at isolation level 1, regardless of the isolation level of the transaction or session.
Triggers and Isolation Levels
Since triggers are fired by data modification statements (like insert), all triggers execute at either the transaction’s isolation level or isolation level 1, whichever is higher.

Parent topic: Transaction Mode and Isolation Level