- SQL Server Locking Mechanism
- Transaction Isolation Levels
- Lock Types and Compatibility
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Transaction Isolation Levels
The way SQL Server acquires and releases locks is also greatly affected by the TRANSACTION ISOLATION LEVEL. In a transactional application, you try to optimize concurrency because you have many users constantly running INSERT, UPDATE, and DELETE statements. Therefore, you try to avoid table-level locks on huge tables that everyone tries to modify. Often, the concurrency issues can be avoided (or at least reduced) by good application design or by improving the logical/physical data model. Occasionally, even the well-designed applications experience the concurrency problems. In such cases, you can choose to change the default TRANSACTION ISOLATION LEVEL. By default, SQL Server supports the READ COMMITTED isolation level. If you decide to change the isolation level, be aware that you might experience one of the occurrences described in the following sections.
Lost Updates
Lost updates occur if you let two transactions modify the same data at the same time, and the transacton that completes first is lost. You need to watch out for lost updates with the READ UNCOMMITED isolation level. This isolation level disregards any type of locks, so two simultaneous data modifications are not aware of each other. Suppose that the beginning balance on my checking account is $1000. I deposit a $500 check on my checking account at 10:00 a.m., and my wife withdraws $200 from the ATM at 10:00 a.m. If all is well, our ending balance should be $1000 + 500 – 200 = $1300. However, if the transaction isolation level is set to READ UNCOMMITTED, and my wife's transaction is committed after mine, my ending balance at 10:01 a.m. will be $1000 – 200 = $800. Not good!
Non-Repeatable Read
Non-repeatable reads occur if a transaction is able to read the same row multiple times and gets a different value each time. Again, this problem is most likely to occur with the READ UNCOMMITTED isolation level. Because you let two transactions modify data at the same time, you can get some unexpected results. For instance, suppose that I deposited $500 in my checking account at 10:00 a.m., when my beginning balance was $1000. Also suppose that I have a credit card issued by my bank, on which I owe $1000. While I'm in a process of depositing the check, my wife is applying for a loan over the phone. The loan officer takes down the account number, and runs a stored procedure that returns my checking account and credit card balance. The procedure also brings back the difference between my checking account and credit card balance. The first query brings back $1000 for the checking account and $1000 for the credit card balance because it reads my checking account before the deposit. The second query hits the same table after my deposit is complete and the difference between the two balances shows $500. So the same procedure reads the same row twice and gets two different results, thereby confusing the loan officer.
Dirty Reads
Dirty reads are a special case of non-repeatable read. This happens if you run a report while transactions are modifying the data that you're reporting on. Therefore, you might see the changes that haven't been committed. Suppose that I intend to withdraw $200 from ATM, and my beginning balance is $1000. I punch in my account number and PIN, and the ATM reduces my balance to $800. At this point, I check my wallet, and am pleasantly surprised that my wife hasn't spent $300 from the last paycheck. I cancel my transaction immediately because I don't feel safe walking around with $500 in my wallet. If a banker were to run a report after I started my transaction but before I cancelled it, my balance would show up as $800, even though I never did withdraw the $200.
Phantom Reads
Phantom reads occur due to a transaction being able to read a row on the first read, but not being able to modify the same row due to another transaction deleting rows from the same table. For example suppose that a banker would like to offer a low interest rate on an equity loan to all individuals with a balance of $3000 or higher on their checking account. The banker runs a stored procedure that executes two queries. The first query returns names of individuals with a balance of $3000 or higher. The second query brings back the names and addresses of all such individuals. The first query brings back Mr. Green. Mr. Green closes his account at 10:00 AM since he's about to move out of town. The second query does not show Green's address on the list since he no longer holds an account with this bank.
Interestingly, the phantom reads can occur even with the default isolation level supported by SQL Server: READ COMMITTED. The only isolation level that doesn't allow phantoms is SERIALIZABLE, which ensures that each transaction is completely isolated from others. In other words, no one can acquire any type of locks on the affected row while it is being modified.
Table 1 shows the various levels supported by SQL Server and what you should expect from each.
Table 1 Transaction Isolation Levels Supported by SQL Server 2000
|
Transaction Isolation Level |
Description |
|
READ COMMITTED |
The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. Other transactions can insert and modify data in the same table, however, as long as it is not locked by the first transaction. |
|
READ UNCOMMITTED |
No shared locks and no exclusive locks are honored. This is the least restrictive isolation level resulting in the best concurrency but the least data integrity. |
|
REPEATABLE READ |
This setting disallows dirty and non-repeatable reads. However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be read by the transaction. |
|
SERIALIZABLE |
This is the most restrictive setting holding shared locks on the range of data. This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed. |
Changing the isolation level is easy; all you have to do is execute the following statement:
/* change "SERIALIZABLE" with the desired isolation level */ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
The transaction isolation levels represent a tradeoff between data integrity and consistency. Therefore, be careful to consider all pros and cons before altering the default isolation level.