Locking in Microsoft SQL Server
Introduction In this article, I want to tell you about SQL Server 7.0/2000 Transaction Isolation Levels, what kinds of Transaction Isolation Levels exist, and how you can set the appropriate Transaction Isolation Level, about Lock types and Locking optimizer hints, about deadlocks, and about how you can view locks by using the sp_lock stored procedure. Transaction Isolation Levels There are four isolation levels: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE Microsoft SQL Server supports all of these Transaction Isolation Levels and can separate REPEATABLE READ and SERIALIZABLE . Let me to describe each isolation level. READ UNCOMMITTED When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read. READ COMMITTED This is the d...