Getting A Handle On Isolation Levels

Robert Bishop explains some of the basics behind transaction isolation levels:

Lost Updates

It is possible for two or more transactions to modify the same row. While the subsequent transactions are reading the data, the 1st transaction commits its changes. Then the subsequent transaction makes its changes and commits, thereby possibly losing the changes committed by the 1st transaction. In this scenario, the last transaction always wins.

Dirty Read

A dirty read is when a SELECT statement will read against a table that is currently being modified and not yet committed to the database. By default, SQL Server will protect against this on all isolation levels, except for Read Uncommitted or by using NOLOCK in your queries.

This is the first in a series.

Related Posts

Range Locks On Multi-Table Indexed Views

Erik Darling looks at the kinds of locks taken when updating an indexed view: So what causes Range Locks? Just ask Sunil. He knows everything (this assumes the serializable isolation level): Equality Predicate If the key value exists, then the range lock is only taken if the index is non-unique. In the non-unique index case, the […]

Read More

Visualizing Deadlocks In SQL Sentry & Plan Explorer

Aaron Bertrand shows off new functionality in SQL Sentry and SentryOne Plan Explorer around deadlock visualization: There’s a lot going on there, but much of it is noise. There is a whole bunch of contention on the table SqlPerf.Session — session 342 is trying to perform an update, but it is stuck waiting on shared locks taken […]

Read More

Categories

January 2017
MTWTFSS
« Dec Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031