Isolation Level!  
Author Message
JasonWalker





PostPosted: Sat Oct 15 21:25:53 CDT 2005 Top

SQL Server Developer >> Isolation Level!

The topic 'Isolation Levels' in BOL states that "The level at which a
transaction is prepared to accept inconsistent data is termed the
isolation level. A lower isolation level increases concurrency but at
the expense of data correctness. Conversely, a higher isolation level
ensures that data is correct but can negatively affect concurrency".
Can someone explain me this, preferably with an example?

Moreover why would one allow a transaction to accept inconsistent data?

Thanks,

Arpan

SQL Server66  
 
 
--CELKO--





PostPosted: Sat Oct 15 21:25:53 CDT 2005 Top

SQL Server Developer >> Isolation Level! This is a comlex topic and you are not going to get a real answer in a
newsgroup. Get a textbook on transactions; there is a good one from
Morgan-Kaufmann.

 
 
Brian





PostPosted: Sat Oct 15 22:24:09 CDT 2005 Top

SQL Server Developer >> Isolation Level! The transaction isolation level specifies the effect transactions on other
connections can have on the current transaction. If your transaction is in
the process of selecting from a table, and before it's finished, another
transaction updates rows in the same table, then depending on whether the
update happened to rows that have already been read, rows that have yet to
read, or both, it's possible that the results of the select will not reflect
the state of the database at a specific point in time. Sometime it's
important for the rows that were just read out to remain unchanged until the
entire transaction completes. Other times it's important that not only the
rows that were just read out to remain unchanged, but also that no rows be
inserted between the first and last row that was just read.

With READ UNCOMMITTED, no shared locks are obtained, nor are any exclusive
locks honored on any resource that is to be read. (A schema stability lock
IS applied, which prevents other transactions from altering the structure
while the SELECT is proceeding.) Another transaction may be in the process
of updating rows at the same time as the SELECT statement--which means that
the result set may not reflect the correct state of the database at any
given point in time. For example, if you're selecting the sum of the
quantity sold for a month, and halfway through the read, another transaction
rolls back a change that was made to a row that was already read out, then
the sum will be incorrect.

With READ COMMITTED, exclusive locks are honored and shared locks are
obtained, but the shared locks are only held long enough to read each row.
The locks walk through the table or index, one page or row at a time, and
then are released. READ COMMITTED eliminates the problem described above,
because rows that are in the process of being changed block the current
transaction until either the changes are committed or rolled back. There
are other problems that can occur with READ COMMITTED, however. For
example, let's assume that you're transferring money from your savings
account to your checking, and at the same time you're trying to find out how
much money you have altogether. If the update occurs at the same time as
the select, then it's possible to change rows that have already been read
out and rows that are yet to be read. The SELECT may not block. This can
lead to incorrect results as well. If your checking account had already
been read before the deposit, but the savings account hadn't yet been read
when the withdrawal occurred, then the results of the SELECT will be off by
the amount of the transfer, since it will appear that the money hadn't made
it into the checking, but had already been deducted from savings.

With REPEATABLE READ, exclusive locks are honored, and shared locks are
obtained and held for the duration of the transaction. This prevents the
incorrect balance in your checking account described above since other
transactions are prevented from changing any rows read out by the
transaction. However, REPEATABLE READ does not prevent additional rows from
being inserted or changed so that they fall within the range of rows that
were just read out. If another transaction inserted a row that meets the
criteria of the SELECT, then using the results to UPDATE another table could
lead to inconsistencies between the tables, since the results of the SELECT
are already stale at the time of the UPDATE.

To deal with the problem just described, use the SERIALIZABLE transaction
isolation level. With SERIALIZABLE, exclusive locks are honored, and shared
range-locks are obtained and held for the duration of the transaction. The
range locks prevent INSERTs within the specified range, and also UPDATEs
that would cause the row to fall within the specified range. This is the
most restrictive transaction isolation level, and can dramatically increase
blocking and can also increase the probability of deadlocks.

For most queries, READ COMMITTED is sufficient for reporting, and REPEATABLE
READ is sufficient for summary SELECTs prior to UPDATEs. It should be noted
that if there isn't a relationship between rows--in other words, if you're
not summarizing data from multiple rows in the same table in order to update
a second table, then READ COMMITTED is more often sufficient for SELECTs
prior to UPDATEs. It doesn't matter if the employee's mailing address
changes if you're just reading out the department code to build a new labor
transaction. Even if the department changed for the employee after it was
read and before the new labor transaction was inserted, it may not be a
problem, since at the time that the procedure that creates the labor
transaction was initiated, the employee's department hadn't yet been
changed. As you can probably see, determining the correct isolation level
must be done on a case-by-case basis.

The default isolation level for SQL Server 2000 is READ COMMITTED.



> The topic 'Isolation Levels' in BOL states that "The level at which a
> transaction is prepared to accept inconsistent data is termed the
> isolation level. A lower isolation level increases concurrency but at
> the expense of data correctness. Conversely, a higher isolation level
> ensures that data is correct but can negatively affect concurrency".
> Can someone explain me this, preferably with an example?
>
> Moreover why would one allow a transaction to accept inconsistent data?
>
> Thanks,
>
> Arpan
>


 
 
Tibor





PostPosted: Sun Oct 16 01:28:28 CDT 2005 Top

SQL Server Developer >> Isolation Level! Surely you don't seriously expect a developer to read Gray and Reuter's "Transaction Concepts and
Techniques" for a pretty basic and simple question? As great as the book is, there are many places
where this is described in a shorter manner. For instance Brian's post.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/




> This is a comlex topic and you are not going to get a real answer in a
> newsgroup. Get a textbook on transactions; there is a good one from
> Morgan-Kaufmann.
>

 
 
Arpan





PostPosted: Wed Oct 19 14:09:16 CDT 2005 Top

SQL Server Developer >> Isolation Level! Thanks, Brian, for such an in-depth explanation & thanks to Celko &
Tibor as well.

Regards,

Arpan