ACID Properties of Sqlserver 2005

Posted: March 4, 2009 in Sqlserver

Transactions

A transaction is one or more actions that are defined as a single unit of work. In the Relational Database Management System (RDBMS) world they also comply with ACID properties:

§  Atomicity: An atomic transaction means either all the actions happen or none of them. If an action fails half-way through a transaction, then all previous actions in the transaction must be rolled back as if they never happened.

§  Consistency: A transaction cannot break the integrity rules of the database; it must leave the database in a consistent state. For example, you might specify that stock levels cannot be a negative value, a spare part cannot exist without a parent object, or the data in a sex field must be male or female.

§  Isolation: SQL Server is designed to service many concurrent users, but from the viewpoint of each user, the data set must look like the user is the only one on the system. Each transaction must be entirely self-contained, and changes it makes must not be readable by any other transaction. SQL Server allows flexibility in the degree of isolation you can specify for your transaction so that you can find a balance between the performance and business requirements.

§  Durability: When a transaction is committed, it must persist even if there is a system failure immediately afterwards. When you commit a transaction in SQL Server, the information needed to replay it is physically written to the transaction log before the commit is returned to the user as successful.

Atomicity, consistency, isolation, and durability are inherent properties of SQL Server transactions. Isolation has a degree of flexibility and is a choice which requires a more detailed understanding, so we will explore it further in the following section.

Isolation Levels

There are five transaction isolation levels available in SQL Server 2005 that provide increasing levels of isolation.

§  Read uncommitted: This is the least isolated and best performing level, but it does allow dirty reads, non-repeatable reads, and phantoms. It can be used when you don’t care about dirty reads and you want to read the data with the lightest touch possible. It doesn’t hold any locks on the data when reading.

§  Read committed: This is the default isolation level for SQL Server and usually provides the best balance between performance and business requirements. It does not allow dirty reads, but non-repeatable reads and phantoms are still possible. Any locks held are released when the statement that caused the read operation is complete, even within a transaction. SQL Server 2005 also has a new flavor of read-committed based on row versioning called read-committed snapshot which is covered later in this chapter.

§  Repeatable read: A repeatable read is possible by holding read locks for the duration of a transaction to prevent other transactions from modifying the data so you can have a repeatable read. It prevents dirty reads and non-repeatable reads but phantoms can still occur.

§  Serializable: This serializes access to data and prevents all of the side effects by holding locks for the duration of the transaction and effectively locking rows that don’t even exist yet through key range locks. This is the most isolated level and the most damaging to high concurrency.

§  Snapshot: The snapshot isolation level is the only optimistic isolation level available and uses row versioning rather than locking. It prevents all of the previously mentioned side effects just like serializable, but it does allow for an update conflict to occur that wouldn’t if transactions were run serially. This conflict occurs when data to be changed inside a snapshot transaction is changed concurrently by another transaction. Detection occurs automatically and causes a rollback of the snapshot transaction to prevent a lost update. Snapshot and row versioning will be covered in more depth after looking at locking in more detail.

summarizes the isolation levels and possible anomalies.

 Isolation Levels and Possible Anomalies

Isolation level

Dirty Read

Non-Repeatable Read

Phantom Read

Update Conflict

Concurrency Model

Read Un-Committed

Yes

Yes

Yes

No

Pessimistic

Read Committed

 

 

 

 

 

1 – Locking

No

Yes

Yes

No

Pessimistic

2 – Snapshot

No

Yes

Yes

No

Optimistic

Repeatable Read

No

No

Yes

No

Pessimistic

Snapshot

No

No

No

Yes

Optimistic

Serializable

No

No

No

No

Pessimistic

To use a transaction isolation level other than the default read-committed, run the SET TRANSACTION ISOLATION LEVEL T-SQL command. For example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

GO

 

Comments
  1. chitta ranjan mishra says:

    hi

    the information is too good…
    still we need some real time examples

    regards
    CRM

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