Transaction Mode and Transaction Isolation Level

Transaction Mode

Transact-SQL or unchained (default mode)
To define a transaction needs explicit statement pair of Begin Tran and Rollback or Commit Tran

Chained or ANSI-Compatible
Implicitly begin trans defore any data retrieval and data modification statements. this statements include:

delete, insert, update open, fetch, select

you must explicitly end the transaction with commit or rollback

set chained on

it will set to chained mode. @@tranchained retains 0 for unchained 1 for chained.


in different mode the following code results differently.

insert into publishers values ('9999', null, null, null)
begin transaction
delete from publishers where pub_id = `9999'
rollback transaction


Transaction Isolation Level
@@isolation returns 1, 2, 3, representing three different isolation levels:

Level 1: dirty reads.
occur when one transaction reads a row and then a second transaction modifies that row. If the first transaction rolls back the change, the information read by the second transaction becomes invalid. Transact-SQL never allows this to happen.

Level 2: non-repeatable reads(hold lock)
occur when one transaction reads a row and then 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.

Level 3: phantoms(hold lock)
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.


The SQL92 standard requires that level 3 be the default isolation between transactions. This prevents "dirty reads," "non-repeatable reads," and "phantoms." To enforce this default level of isolation, Transact-SQL provides the transaction isolation level 3 option of the set statement. This instructs SQL Server to automatically apply a holdlock to all select operations in a transaction.