SET TRANSACTION ISOLATION LEVEL

Like I’ve mentioned before, unless you use SET TRANSACTION ISOLATION LEVEL, the server will default to READ COMMITTED. What this means is by default, your queries will only be able to interact with records that have been committed. If another query is affecting those records, you’re not able to see them in any way. I am willfully ignoring the NOLOCK and READUNCOMMITTED hints right now. If you want to change the default behavior, and interact with these records, then you can use the query hints, or you can change the isolation level for your batch.

Since I’ve already covered the hints method before… I’m going to show you how to change the isolation level for your batches.

You can choose any of these options. Let me explain what each one means.

READ COMMITTED — This is the default case for SQL Server. You can only read records that have been committed, and are not currently being affected by other connections (queries).

READ UNCOMMITTED — this allows you to make “dirty” reads. You can see data that is uncommitted, or is in the middle of change. If you read this data and make a decision based on that data, you have to know that the data could change at any time, and if you run your query again, you’re not going to see the same thing (most likely). This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ — Locks are put on any data used in the query. This will prevent other uses from updating the data while you’re using it. But, please keep in mind new rows could be added into the data set by another user. As long as their transactions are committe, these rows will show up later in your transaction, since that read would be repeatable… Yes, I realize I’m using the phrase to define itself, but it helps me remember it… Be careful using this option…It can be useful, but you can also get yourself into trouble!

SERIALIZABLE — I’ll be honest, I rarely find a use for this option where READ COMMITTED wouldn’t accomplish the same goal. This option places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Please note you can only use one of these options at a time. If you need to change back and forth, I recommend using query hints. They’re a better option for the fast switch.

At this point you’re definitely getting into the fine-tuning aspects of SQL Server. This is where you need to proceed with caution. Definitely have a second set of eyes check your code before going into production. Otherwise you could really foul up your datasets! If you have any questions, send them in. I’m always here to help!

Shannon Lowder is the Database Engineer you've been looking for!
Look no further for expertise in:
Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

3 Comments on "SET TRANSACTION ISOLATION LEVEL"

Thanks this built for exciting reading. I really like your wordpress theme,I often come back again right here and that i dont know why. I just actually like your website lol… I just study anything much like this on one more website. I think they may well of stolen the blog?

Amazing! It is like you read my mind! You seem to know a lot about this, like you wrote the book in it or something. I think that you could do with some pics to drive the content home a bit, but other than that, this is outstanding blog post. A good read. I will certainly revisit again.