Like this article? We recommend Show
Transaction Isolation LevelsThe 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 UpdatesLost 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 ReadNon-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 ReadsDirty 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 ReadsPhantom 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
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. What is the default transaction isolation level for SQL Server?The isolation level of the transactional support is default to READ UNCOMMITTED. You can change it to READ COMMITTED SNAPSHOT ISOLATION by turning ON the READ_COMMITTED_SNAPSHOT database option for a user database when connected to the master database.
Which of the following is the most restrictive transaction isolation level?Repeatable Read :
This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read.
What is default transaction mode in SQL Server?The auto-commit transaction mode is the default transaction mode of the SQL Server.
Which isolation level is best in SQL Server?Serializable. This is the highest isolation level and prevents all possible types of concurrency phenomena in SQL Server, but on the other hand, the serializable level decreases performance and increases the likelihood of deadlocks.
|
