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.
SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
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!