Understanding Isolation Levels in Transaction
page 1 of 6
Published: 21 Jun 2007
This article describes Isolation Levels in Transactions.
Average Rating: 
Views (Total / Last 10 Days): 27029/ 43


The level at which a transaction is ready to accept inconsistent data is termed as isolation level. A transaction is a set of operations that must either succeed or fail as a unit. The goal of a transaction is to ensure that data is always in a valid, consistent state. To know more about transaction, please refer to my article "Understanding Transaction in ADO.NET." The isolation level is the degree to which one transaction must be isolated from other transaction. The concept of isolation level is closely related to the concept of locks. SQL server uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users and also prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect and quarries executed against that data might produce unexpected results. By determining the isolation level for a given transaction we can determine which types of locks are required.

Shared locks and exclusive locks are two basic locks, where read operations acquire shared locks and writes operations acquire exclusive locks. Shared locks allow concurrent transactions to read (SELECT) a resource. No other transaction can modify the data where shared locks exist on the data. Shared locks on a resource are released as soon as the data has been read. A shared lock will exist until all rows that satisfy the query have been returned to the client. Exclusive locks prevent access to a resource by concurrent transactions. No other transaction can read or modify data with an exclusive lock. SQL server uses exclusive (write) locks for the INSERT, UPDATE and DELETE data modification statements. Only one transaction can acquire an exclusive lock on a resource. A transaction can neither acquire a shared lock on a resource that has an exclusive lock nor acquire an exclusive lock on a resource until all shared locks are released.

View Entire Article

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-06-17 8:16:30 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search