Mike this might or might not help, depending on your situation. But either way it's a quick overview on transaction isolation levels that every SQL Developer should at least be aware of

There are five isolation levels in SQL. You can implement them in stored procs or in ADO.NET code.

- Dirty read (read uncommitted)
- Read Committed (default)
- Repeatable read (rarely used)
- Serializable
- Snapshot (added in SQL 2005)

here's a quick scenario breakdown: There's more than just this brief summary but it's a start.

1) - Dirty read: absolutely no locking at all. Very risky: you could start a transaction that writes to 3 tables. In the split second in between your posting to the 3 tables, I read the 3 tables. I might get an incomplete picture of what you posted. That's why they call it "dirty read".

Only practical use is in highly controlled environments (or if you're just sampling data and you don't care about integrity)


2)- read commited: default in sql server

two behaviors to be aware of:

Behavior: User A starts a transaction and starts to update a row, but doesn’t commit for several seconds. User B tries to read the row – is locked out until User A’s process either commits or rolls back.

Behavior: User A starts a long query that reads many rows and takes a minute to run. User B will be locked out from updating any of those rows while User A’s query is running.

However, two people "can" read at the same time


3) repeatable read - rarely used. I

Ensures that no one can change values in between reads that take place in the same transaction.
A reader need a shared lock to be able to read, but it also holds the lock until the end of the transaction.


Behavior: Using REPEATABLE READ, if User A’s transaction reads 1,000 rows in a transaction (e.g. Select * from Customers where Status = 1), and then does other things in the transaction, User B won’t be able to update any of the 1,000 rows – because User A has a repeatable read lock on the rows. User A’s transaction is guaranteed that a subsequent read of the 1,000 rows will return the same values as the initial read. User B won’t be able to update the rows until User A’s transaction finishes.


4) serializable

closest to a file lock - so that others not only can't modify data, they also can't add new data that would change the original user's query


5) Snapshot:

unlike the first four approaches, this one uses versioning. It provides the benefits of the first 2 isolation levels without the side effects (getting locked out or getting dirty data)

If you're updating a row, and I retrieve the row while your update occurs....I neither will get locked out, nor will I get your pending update....I get the last good committed version. Now....if I take that last good committed version and I try to do an update after you do your update, I'm not allowed, because I'm now updating based on a "stale" and outdated version. there's more to that story, but that's the bottom line.