Michael J. Swart

August 10, 2011

ACID Properties By Example (And Counterexample) Part Three: Isolation

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , , — Michael J. Swart @ 12:00 pm

ACID Properties By (Counter) Example

So the third ACID property of database transactions is I for Isolation. This is the only ACID property that deals with behaviour of a transaction with respect to other concurrent transactions (all the other properties describe the behaviour of single transactions). Haerder and Reuter describe it as:

Isolation: Events within a transaction must be hidden from other transactions running concurrently.

It’s not super-rigorous, but I think of it like this: No looking at works-in-progress

Back to the drawing board.

(Actually, I don’t always believe in that advice, but it helps the cartoon)

So there are different kinds of database isolation. Even with the the guideline: no looking at other transactions in progress. And now these levels of isolation are well defined. I wrote a series on those earlier, the different levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. By the way READ UNCOMMITTED is the only isolation level here that is not really isolated, more on that later.

Isolation in SQL Server

SQL Server supports all of these isolation levels. It enforces this isolation using various locks on data (fascinating stuff actually), processes will wait to maintain isolation. In contrast, Oracle supports only SERIALIZABLE and a kind of READ COMMITTED that is closer in behaviour to SQL Server’s SNAPSHOT isolation. No matter how it’s implemented, READ COMMITTED is the default isolation level in both SQL Server and Oracle.

Unisolated Transactions:

So it is possible for other transactions to see the effects of a transaction in-flight (i.e. as it’s happening, before it’s committed). This is done with NOLOCK hints or with the READ UNCOMMITTED isolation level. In fact, I learned recently that when using NOLOCK hints, you not only can see the effects of an in-flight transaction, but you can see the effects of an in-flight statement. This is an Isolation failure and it boils down to this: SQL Server transactions are atomic, but when using NOLOCK, it might not seem that way. So take care.

Example

Today’s example and counterexample both come from the newspapers headlines of Chicago.

For the example – a fictional example – I explain a situation that’s all about not making assumptions. It’s all about being cautious and not committing to a decision while the jury’s still out. This immediately brought to mind a scene from the movie Chicago [spoiler alert!] :

The movie (and play) is about a court case. The main character Roxie is on trial for murder. It’s a sensational trial and the papers are eager to publish the results of the trial. The papers are so eager in fact that the papers have printed out two editions of their newspapers. One headline read “She’s Innocent” the other headline read “She’s Guilty”. But those two stacks of papers are just sitting there in the van. The man in the newspaper van waits for a signal from the courthouse. Once he got the proper signal, he cracked open the innocent edition and gave them to a paper boy to hand out.

It’s about not acting on information while the jury is still out. The jury is isolated from the world and no one can act on what the jury has to say until they’ve committed to a verdict.

Counter-Example

Our counter-example comes from non-fiction. In reality, the assumptions we make tend to be correct. Our assumptions are only interesting when they turn out to be incorrect. This counter-example comes from the most incorrect newspaper headline I can think of:

“Dewey Defeats Truman”

Click through for Wikipedia’s page on cool piece of newspaper history (Chicago newspaper history). It’s a great example of what can go wrong when we act on tentative (uncommitted) information. The Chicago Tribune published the wrong presidential candidate as the winner.

But the really really cautious reporters would report neither candidate as the winner. They’d be waiting at the Electoral College convention. They’d be keen on seeing how that turns out.

3 Comments »

  1. I’ve come to consider isolation to be a unicorn. I’ll believe it when I see it.
    The idea is that your transaction doesn’t see any other transaction while it is in progress. The basic idea of read uncommitted is that I don’t care about what you are about to do, I just want to see what is, now. That can work quite well at times. You can get impacted with this command too. You bypass the locks and read the data, the other transaction makes its change, and for some reason SQL decides to go back to where it found the record, but it isn’t there anymore and SQL decides to blow up rather than find the data again.
    Well, read committed makes sure you wait until the lock on the data you want to look at is released. So that solves everything, right? Here unicorn, where are you? You try to update a record using the clustered primary key to find your record while a maintenance routine is using a non-clustered unique index to find the records to be deleted. It puts a lock on the index that’s going to lose some data and then tries to lock the main table, but it can’t because you have it locked. You try to complete your unrelated update, but that requires modification of the index that the delete has already locked. Deadlock. You did everything very efficiently so your cost isn’t high, you get to be the deadlock victim. Yea, punish you, because you did things better.
    What was that about not seeing work in progess?

    Comment by Ken — August 20, 2011 @ 3:12 am

  2. Thanks for stopping by Ken, Nothing like a good analogy.
    I think I understand your point of view. You’re arguing that READ UNCOMMITTED is not unwise. It’s a reasonable point of view. I just don’t share it.

    Comment by Michael J. Swart — August 22, 2011 @ 8:23 am

  3. Why the ACID properties are not so ACID at all!
    http://blog.josedacruz.com/2012/12/17/the-not-so-acid-transaction-properties

    Comment by José Cruz — December 19, 2012 @ 2:48 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress