Isolation levels
If low level of
Isolation is set, it allows multiple users to access the resources concurrently
but it may result in many concurrency related problems like phantom reads,
dirty reads etc.
If higher levels of
Isolation is set then it eliminate the concurrency related problem but it
results in less number of concurrent access and it may result in data blocking
Dirty reads: This situation happens
when a transaction tries to read a data by some other
concurrent transaction which is not committed yet. There is a risk, that
this other transaction may never be committed, leaving the original
transaction with wrong data.
Lost updates: It happen when more than one transaction tries to update the same data. In Lost update, a successfully updated value of a data written in the database is overrides accidentally by some another transaction updating the same data concurrently.
Repeatable reads: Repeatable reads condition occurs when a transaction tries to read a data multiple times and between the two reads, another transaction modified that data. Therefore when the original transaction tries to read that data second time, it find different value for that data. In other words, the original transaction reads two different values for the same data.
Phantom reads: This condition happen when a transaction needs to execute a same query twice (or multiple times) and it gets different set of rows from what it get earlier due to the execution of the query first time. This happens if some another transaction add or delete rows between the two executions of the query and these added/deleted rows are the part of the record set reruns by the execution of the query.
Lost updates: It happen when more than one transaction tries to update the same data. In Lost update, a successfully updated value of a data written in the database is overrides accidentally by some another transaction updating the same data concurrently.
Repeatable reads: Repeatable reads condition occurs when a transaction tries to read a data multiple times and between the two reads, another transaction modified that data. Therefore when the original transaction tries to read that data second time, it find different value for that data. In other words, the original transaction reads two different values for the same data.
Phantom reads: This condition happen when a transaction needs to execute a same query twice (or multiple times) and it gets different set of rows from what it get earlier due to the execution of the query first time. This happens if some another transaction add or delete rows between the two executions of the query and these added/deleted rows are the part of the record set reruns by the execution of the query.
READ
UNCOMMITTED
BEGIN TRAN
UPDATE Isolation SET
A = 2
WAITFOR DELAY
'00:00:10'
ROLLBACK
SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM
Isolation
READ
COMMITTED
BEGIN TRAN
UPDATE Tests SET Col1 = 2
--Simulate having some intensive
processing here with a wait
WAITFOR DELAY '00:00:10'
ROLLBACK
SELECT * FROM IsolationTests
REPEATABLE
READ
The data can change between 2 queries if more
records are added.
Possible to get phantom
reads
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
BEGIN TRAN
SELECT * FROM IsolationTests
WAITFOR DELAY '00:00:10'
SELECT * FROM IsolationTests
ROLLBACK
UPDATE IsolationTests SET Col1 = -1
SERIALIZABLE
This isolation level takes Repeatable Read and
adds the guarantee that no new data will be added eradicating the chance of
getting Phantom Reads.
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
BEGIN TRAN
SELECT * FROM IsolationTests
WAITFOR DELAY '00:00:10'
SELECT * FROM IsolationTests
ROLLBACK
INSERT INTO
IsolationTests(Col1,Col2,Col3)
VALUES (100,100,100)
SNAPSHOT
This
provides the same guarantees as serializable.
ALTER DATABASE IsolationTests
SET ALLOW_SNAPSHOT_ISOLATION ON
0 comments: