Reads & Updates in SQL-Server

Following are the problems that occur if you do not implement locking properly in SQL SERVER.

Lost Updates

· Lost updates occur if you let two transactions modify the same data at the same time, and the transaction that completes first is lost.

· You need to watch out for lost updates with the READ UNCOMMITTED isolation level.

· This isolation level disregards any type of locks, so two simultaneous data modifications are not aware of each other.

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.

· 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.

· 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..

Thanks & Regards,

Arun Manglick || Tech Lead