Database

Isolation Levels

Isolation Issues

Lost Update
A lost update can be interpreted in one of two ways. In the first scenario, a lost update is considered to have taken place when data that has been updated by one transaction is overwritten by another transaction, before the first transaction is either committed or rolled back. This type of lost update cannot occur in SQL Server 2005 because it is not allowed under any transaction isolation level.
The other interpretation of a lost update is when one transaction (Transaction #1) reads data into its local memory, and then another transaction (Transaction #2) changes this data and commits its change. After this, Transaction #1 updates the same data based on what it read into memory before Transaction #2 was executed. In this case, the update performed by Transaction #2 can be considered a lost update.

Avoiding Second Lost Update
Last Update Wins” – just allow the update.
Column Compare” – all the columns to be updated are compared when updating, and if any are different to the values originally read, an error is returned (e.g. Oracle Forms does this and issues “FRM-40654: Record has been updated by another user. Re-query to see change.”).
Hash Compare” – this is a variant on the “Column Compare” method used by HTMLDB. It compares a hash of the combined data for the row.
Timestamp Compare” – the table has a “timestamp” column, which is automatically set to the last date/time when the row was inserted or most recently updated. An update will only succeed if the timestamp of the updated row is the same as the timestamp originally read.
Version Compare” – the table has a “version” column, which is set to 1 on insert and incremented on update. An update will only succeed if the version of the updated row is the same as the version originally read.

Dirty Read
If data that has been changed by an open transaction is accessed by another transaction, a dirty read has taken place. A dirty read can cause problems because it means that a data manipulation language (DML) statement accessed data that logically does not exist yet or will never exist (if the open transaction is rolled back). All isolation levels except for read uncommitted protect against dirty reads.
notes1

Non-Repeatable Read
If a specific set of data is accessed more than once in the same transaction (such as when two different queries against the same table use the same WHERE clause) and the rows accessed between these accesses are updated or deleted by another transaction, a non-repeatable read has taken place. That is, if two queries against the same table with the same WHERE clause are executed in the same transaction, they return different results. The repeatable read, serializable, and snapshot isolation levels protect a transaction from non-repeatable reads.

Phantom Reads
Phantom reads are a variation of non-repeatable reads. A phantom read is when two queries in the same transaction, against the same table, use the same WHERE clause, and the query executed last returns more rows than the first query. Only the serializable and snapshot isolation levels protect a transaction from phantom reads.

 

Isolation Levels

There are four Transaction Isolation Levels. The key to understanding Transaction Isolation Levels is to realize that all they do is control how long Shared locks are held within a transaction.

Isolation Level     Dirty Read        Nonrepeatable Read        Phantom     Lost Update (where transaction results in rollback)

Read Uncommitted     Yes             Yes                                         Yes                No
Read Committed          No              Yes                                         Yes                No
Repeatable Read          No              No                                          Yes                No
Serializable                    No              No                                          No                 No

Transactions are not handled in the persistence layer. I think they properly belong in the service layer, because service methods are what map to units of work and use cases. A single service call might involve more than one database and model object, so all of them need to be a single, ACID unit of work. That’s not possible if the persistence layer handles transactions.

Read Uncommitted
The read uncommitted isolation level is tempting to consider because it can provide great performance benefits. Unfortunately, this is at the expense of returning valid results. This isolation level only guarantees that a transaction does not experience lost updates. When executing a DML statement in the read uncommitted isolation level, SQL Server will allow dirty rows to be returned. This might cause you to read data that has never existed because the data that was read was inserted or updated by another transaction that was later rolled back.

You should consider using this isolation level only in routines where the issue of dirty reads is not a problem. Such routines usually return information that is not directly used as a basis for decisions. A typical example where dirty reads might be allowed is for queries that return data that are only used in lists in the application (such as a list of customers) or if the database is only used for read operations.

The read uncommitted isolation level is by far the best isolation level to use for performance, as it does not wait for other connections to complete their transactions when it wants to read data that these transactions have modified. In the read uncommitted isolation level, shared locks are not acquired for read operations; this is what makes dirty reads possible. This fact also reduces the work and memory required by the SQL Server lock manager. Because shared locks are not acquired, it is no problem to read resources locked by exclusive locks. However, while a query is executing in the read uncommitted isolation level, another type of lock called a schema stability lock (Sch-S) is acquired to prevent Data Definition Language (DDL) statements from changing the table structure. Below is an example of the behavior of this isolation level. This example shows the behavior of the read uncommitted isolation level.

Read Committed
The read committed transaction isolation level is the default isolation level for new connections in SQL Server. This isolation level guarantees that dirty reads do not occur in your transaction. A row is considered dirty when it has been deleted, updated, or inserted by another transaction in another connection where the transaction has not yet been committed or rolled back. If your connection is using the read committed isolation level and SQL Server encounters a dirty row while executing a DML statement, it will wait until the transaction that currently owns the row has been committed or rolled back before continuing execution.

By default, there is no timeout for these waits. However, you can specify a timeout by executing the SET LOCK_TIMEOUT statement. This statement requires one parameter: the maximum number of milliseconds to wait. Note that if you set it to 0, it will not wait at all. Instead, you will immediately receive an error message, and the executing DML statement will be stopped and rolled back.

In the read committed isolation level, shared locks are acquired for read operations, but they are released as soon as they have been granted. The shared locks are not held for the duration of the transaction. The following is an example of the behavior of this isolation level. The rows in the following table show the order in which the statements are executed. Note that the ALTER DATABASE statements are used to make sure that the correct settings are used for the database. These ALTER DATABASE statements require that no connections exist against the database.

Repeatable Read
In the repeatable read isolation level, SQL Server not only guarantees that dirty reads do not happen in your transaction, but it also guarantees that if you issue two DML statements against the same table with the same WHERE clause (one query could be a select and the next could be an update), both queries will return the same results. This is not entirely true because the latter queries might return more rows than the first query. A repeatable read protects against deletes and updates of accessed rows, but not against inserts that match the specific WHERE clause, which is the phantom rows concurrency problem. Note that phantom rows might also occur if you use aggregate functions, although it is not as easy to detect.

The first query might, for example, include the sum over four rows, while the second query includes the sum over five rows. One important thing to note is that, because the repeatable read isolation level is achieved using blocking of the other transaction, the use of this isolation level greatly increases the number of locks held for the duration of the transaction. This, in turn, can cause serious performance problems due to blocked transactions. It also greatly increases the risk for deadlocks. A deadlock is encountered if different connections are waiting for locks held by each other. In the repeatable read isolation level, shared locks are acquired for read operations and are also held for the duration of the transaction, not allowing changes to the data by other transactions.

Serializable
The serializable isolation level guarantees that none of the concurrency problems mentioned earlier can occur. Because this isolation level also protects from the occurrence of phantom reads through blocking, the use of the serializable isolation level increases the risk for blocked transactions and deadlocks even more than using the repeatable read isolation level. This isolation level guarantees that if you issue two DML statements against the same table with the same WHERE clause, both queries will return exactly the same results, including the same number of rows. To protect the transaction from inserts, SQL Server will need to lock a range of an index over a column that is included in the WHERE clause with shared locks. If such an index does not exist, SQL Server will need to lock the entire table.

 

References:-

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s