ISOLATION LEVELS
The thumb rule of RDBMS is every transaction should satisfy the ACID properties. Here I stands for Isolation, means isolate from other simultaneous transactions. (Do protected by or not disturb other transactions). This is achieved by putting the locks on your transaction.
First of all we’ll try to understand
what is the lock? In short a lock is a In-Memory structure having 64 bytes(on
32 bit OS) or 128 bytes(on 64 bit OS) in size with different attributes like
owner, type, resource hash etc and it links to the actual resource(row, page,
table, file, database, etc). SQL Server supports more than 20 different
lock types but below are the most frequently used and important ones.
i.
Shared Lock (S): If any resource is being
held by shared lock through a first transaction, it allows all other transactions
only to read (SELECT) operations. But other transactions can’t modify the data until
the shared lock (S) exists on the resource. And it released when the read
operation completes, unless the transaction isolation
level is set to repeatable read or etc. Shared locks can compatible with other shared
locks, so that a resource (data) can hold multiple shared locks at a time.
ii.
Exclusive Lock (X): If any resource is being
held by Exclusive lock through a first transaction, no other transaction can read
(SELECT) or modify (INSERT, UPDATE, DELETE) the data. If you want to read use
NOLOCK hint or read uncommitted isolation level. These are not compatible with
any other locks including shared locks.
iii.
Update Lock (U): For any DML operation first
it read the data next do the modification. Reading a resource the transaction applies
a shared lock (S) and next converts it to exclusive lock (X) for modification.
But more than one transactions acquire shared locks (S) on a same resource one
can attempt lock conversion to exclusive (X) must wait until existed shared
locks release because exclusive lock not
compatible with any other locks and it leads to blocking. This time if another
shared lock attempts to resource as covert to exclusive lock leads to deadlocks.
To prevent such scenarios update locks (U) are used. It can be placed on a
resource when that already has a shared lock. It is used by a resource when
they are selected for update, and compatible only with shared locks. For
example a transaction having update data under a specific condition. SQL Server
acquires update lock (U) for every row it processes while checking the
condition. When eligible row found, SQL Server converts (U) lock to (X).
iv.
Schema Lock: When transactions related
to schemas, the Schema Locks will be triggered. For example when performing DDL
operations, such as adding a column or dropping a table DB Engine uses
Schema-Modification (Sch-M) lock for preventing access on the table. Means it
block all other transactions related to this table until the lock released. And
when compiling and executing queries DB Engine uses Schema-Stability (Sch-S)
locks and it do not block any transnational locks.
v.
Intent Locks (IS, IX, IU, etc):
By using this type of locks SQL Server shows multigranular locking behavior, which
mean locking at multiple levels. Intent Locks are technically not needed
by SQL Server, because they only indicate if there is some other specific Lock
Type somewhere else within the Lock Hierarchy. What this means is that
before you hold a lock on the page or the row level an intent lock is set on
the table. This prevents other transactions from putting exclusive locks on the
table that would try to cancel the row/page lock. Intent locks include intent
shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
But what type of locks and
how they can be work decided by the isolation level that you set on database. When
you are connecting from a client application (UI) to database, there are
different layers (OLEDB Provider for SQL Server, ODBC Driver for SQL Server, or
.NET’s SqlClient class) puts these isolation levels programmatically to
the database. And also we can set explicitly by SET TRANSACTION ISOLATION LEVEL
statement.
SET command is a connection
or a session specific, which means it applies only on current session. If you
make a new connection (open a new window in the Query Analyzer) it will
be back to the default isolation level.
There are five isolation levels defined in SQL Server targeting to different scenarios of use, first four are introduced in SQL Server 2000 and the last one (Snapshot) is from SQL Server 2005.
1. Read Uncommitted: This is the lowest isolation level and do not issues any type of locks. So a transaction can read the data even another transaction not yet committed. This is called Dirty Read.
2. Read Committed: This is the default isolation level on each database. It prevents transactions to read data until the first transaction commits respective data (reads only committed data). Simply it restricts a user from seeing any intermediate, uncommitted data (dirty reads) by using shared lock or row versioning. But it won’t prevent repeatable reads and Phantom reads.
REPEATABLE READS: After the first data read the shared lock will be released and if the other transaction modifies the data (as other transactions can acquire exclusive locks) before a subsequent data read, the subsequent data read will see a different set of data than the previous data read. In other words, In between two successful reads of a same transaction’s same data might be modified by another transaction. It leads different result sets.
3. Repeatable Read: To avoid Repeatable Reads we use this isolation. In this isolation shared locks can prevents data modification by other transactions when the current transaction being hold for READ. And also prevents data read operations when the current transaction holds for UPDATE (reads after committed transaction). But it can’t prevent Phantom Reads.
PHANTOM READS: Data getting changed or new rows can be added by other transactions to current transaction. The current transactions search operation can leads different results.
4. Serializable: This isolation doesn’t allow any other transactions to read (SELECT) or write (INSERT, UPDATE and DELETE) the data until the current transaction completed. As a result, the Serializable isolation level prevents dirty reads, non-repeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels. Because it holds a read/write lock for the entire range of respective records.
5. Snapshot: In Read-Committed isolation all respective rows are locked until data gets commit these ensures accuracy but leads degrade in performance (blocking). And in Read-Uncommitted the dirty reads help to faster query executions (higher concurrency) but no accuracy in results. Except Read-Uncommitted, in all isolation's data writers (exclusive lock) block data readers (shared lock) and data readers block data writers. To avoid this Snapshot isolation is introduced. Snapshot isolation levels use a Row Versioning concept; simply it extracts the respective rows (previously committed data) to version store (tempdb) with a time stamp, and allows data read and write operations. Here SQL server will maintains multiple versions of old data from db as long as they needed by SQL server else data removed from the version store.
The difference between Read Committed and Repeatable Read is, in the Read Committed isolation level the shared lock is released once the data gets processed without waiting for transaction commit whereas in Repeatable Read isolation levels the shared lock is held until either by the transaction commits or roll backing. .....(Continuation on next post)
There are five isolation levels defined in SQL Server targeting to different scenarios of use, first four are introduced in SQL Server 2000 and the last one (Snapshot) is from SQL Server 2005.
1. Read Uncommitted: This is the lowest isolation level and do not issues any type of locks. So a transaction can read the data even another transaction not yet committed. This is called Dirty Read.
2. Read Committed: This is the default isolation level on each database. It prevents transactions to read data until the first transaction commits respective data (reads only committed data). Simply it restricts a user from seeing any intermediate, uncommitted data (dirty reads) by using shared lock or row versioning. But it won’t prevent repeatable reads and Phantom reads.
REPEATABLE READS: After the first data read the shared lock will be released and if the other transaction modifies the data (as other transactions can acquire exclusive locks) before a subsequent data read, the subsequent data read will see a different set of data than the previous data read. In other words, In between two successful reads of a same transaction’s same data might be modified by another transaction. It leads different result sets.
3. Repeatable Read: To avoid Repeatable Reads we use this isolation. In this isolation shared locks can prevents data modification by other transactions when the current transaction being hold for READ. And also prevents data read operations when the current transaction holds for UPDATE (reads after committed transaction). But it can’t prevent Phantom Reads.
PHANTOM READS: Data getting changed or new rows can be added by other transactions to current transaction. The current transactions search operation can leads different results.
4. Serializable: This isolation doesn’t allow any other transactions to read (SELECT) or write (INSERT, UPDATE and DELETE) the data until the current transaction completed. As a result, the Serializable isolation level prevents dirty reads, non-repeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels. Because it holds a read/write lock for the entire range of respective records.
5. Snapshot: In Read-Committed isolation all respective rows are locked until data gets commit these ensures accuracy but leads degrade in performance (blocking). And in Read-Uncommitted the dirty reads help to faster query executions (higher concurrency) but no accuracy in results. Except Read-Uncommitted, in all isolation's data writers (exclusive lock) block data readers (shared lock) and data readers block data writers. To avoid this Snapshot isolation is introduced. Snapshot isolation levels use a Row Versioning concept; simply it extracts the respective rows (previously committed data) to version store (tempdb) with a time stamp, and allows data read and write operations. Here SQL server will maintains multiple versions of old data from db as long as they needed by SQL server else data removed from the version store.
The difference between Read Committed and Repeatable Read is, in the Read Committed isolation level the shared lock is released once the data gets processed without waiting for transaction commit whereas in Repeatable Read isolation levels the shared lock is held until either by the transaction commits or roll backing. .....(Continuation on next post)
