Transaction Isolation Levels: Concurrency vs. Integrity Transaction isolation levels dictate how visible a transaction's intermediate product (the state of the data undergoing change before it is committed to the database) is to a second process that also needs to access the resources undergoing a change. The changes result from the traditional CRUD actions (Create, Read, Update, and Delete).
The four transaction isolation levels are:
Read Uncommitted Isolation Level The benefit of Read Uncommitted is that it offers maximum concurrency; transactions don't have to wait or be blocked because no shared locks or exclusive locks are given. Under the Read Uncommitted isolation level, a second process can see data before the transaction that changes the data is complete. This is called a dirty read. Another possible con is the lost update: a change made by a first process is changed by a second one.
Read Committed Isolation Level Read Committed guarantees that the data viewed by a transaction is "real." This level holds shared locks, which prevents dirty reads, but a writing transaction could cause either an unrepeatable read or a phantom record. Read Committed is the default transaction isolation level setting in SQL Server.
Repeatable Read Isolation Level Repeatable Read locks the data viewed to guarantee its stability throughout the entire transaction. A second transaction can add new inserts to the set, however. If the process needs to revisit the data, it will see the same data it did the first time, but it could see additional, "phantom" data as well.
Read Serializable Isolation Level In the Read Serializable level, the set is guaranteed to be free of phantoms because locks are held throughout the transaction. The set under consideration cannot have any updates, inserts, or deletes that weren't there the first time. The tradeoff for serialized transactions is it has the lowest concurrency. No changes can happen to the data until it is finished.
By serializing the power to change data, the state of the data is guaranteed to be consistent (i.e., stable for each modification made). In general, the four levels illustrate the trade off in transaction isolation: maximum concurrency comes at the price of data integrity.
Ensure Integrity with Locks Locking ensures integrity in data-changing events by informing a process that its action or contemplated action will be completed. Locks block competing processes until the resources being used by the current process are released. You can apply locks to various resources. In SQL Server 2000, you can lock a row, a key, a page of an extent, an extent of a table, an entire table, and even the entire database. You also can apply locks in an escalating scope through the following locking modes:
Locks act in a hierarchy. They tell the higher levels controlling a resource that a lower-level lock is present (by placing an Intent lock at the higher levels). For example, for a row exclusively locked, the page and the table will each have an Intent Exclusive lock. Intent locks telegraph a process's actions in advance to other processes that may also require that resource.
In addition to locking a specific resource, each mode indicates the severity of the restriction and the reduction in concurrency placed on the resource. Remember that each escalating lock combination causes a reduction in concurrency and an increase in the degree of exclusivity applied to a transaction.
Orchestrate Processes with Isolation Levels and Locking SQL Server offers many commands to influence concurrency. To create the illusion of everyone working together, developers can manipulate transaction isolation levels and locking granularity and exercise coding practices to orchestrate the cooperation between processes. By doing so, they ensure that their transactions execute without invalidating each other's changes. This orchestration is an illusion because only one process or transaction can happen at a time on a resource, but enforcing isolation and locking the DBMS can guarantee consistency and concurrency.