A long time ago, in a land far away, there existed a mythical place called California, where the weather was always perfect, the rich and famous roamed hither and yon, the beach beckoned using the voice of the ocean waves, and everything cost ten times more than what it was worth.
But I digress.
In the land of California long ago, I worked at a school in administration. We had software which kept track of student attendance and grades. We couldn’t believe that computers could actually do all of this for us! No more paper!
And we were thankful.
Just one hitch…
Whenever a teacher or an admin had to enter information, there would come a call from down the hall: “Is anyone in the grading software? If so, get out! I need to synch my data!”. Everyone would flee until the person was done synching, then one by one, we would tentatively reenter the GUI, input our data, issue the call to evacuate, and commit our changes so that no one could overwrite us.
And when it happened sometimes (ummmm, frequently) that our data was overwritten by someone who didn’t hear the call and we had to clean up the mess and start over from scratch, we were most certainly notthankful.
Welcome to the wonderful world of concurrency. We have come a looooong way in the last 15 years.
SQL Server over the years has developed a tier of ways to deal with concurrency, relieving you of the responsibility of straining your vocal cords countless times a day. You get to pick what will work for you, based on the needs of the business you serve. But choose wisely. A bad choice of isolation levels will most definitely make your business users unthankful.
READ UNCOMMITTED – The quick and dirty solution
READ UNCOMMITTED: All bets are off. Readers can see data modified in another transaction but not yet committed (dirty data). Data could also be modified (changed, deleted) underneath you in a different transaction involving the same data, and you wouldn’t know the difference. There are no shared locks to guarantee anything concerning the data. This is okay if your data doesn’t change (think historical data, data warehouses, etc.), but not if it does change, or if you care about it.
READ COMMITTED – It’s kinda sorta better. Kinda.
READ COMMITTED: One step up (and the default for SQL Server). A query in the current transaction can’t read data modified by someone else that hasn’t yet committed. No dirty reads. BUT….data could be changed by others between statements in the current transaction, so the data may not look the same twice. READ COMMITTED uses shared locks to prevent dirty reads, but that’s about all you get. You still get non-repeatable reads and phantom reads here (more on phantom reads below).
REPEATABLE READ: A ghost of a chance that all will be well.
REPEATABLE READ: So now, there won’t be dirty reads, AND no other transactions can modify data that you are reading until your transaction completes. So, goodbye to non-repeatable reads. BUT, there is still the possibility of being haunted by erroneous data. Data can still be modified in BETWEEN executions within a transaction. So if you run the query twice, you may see the changes in the forms of rows appearing out of “nowhere” (called phantom rows).
SERIALIZABLE: The California School Situation (+15 years)
SERIALIZABLE: One at a time processing. Your query cannot read data changed by another uncommitted transaction. Nothing else can modify data being used by your transaction until you’re done. Nothing can insert new rows that would match the search condition of your query until you’re done. Use with caution. This really slows things down, and will not earn you friends if you use it too much.
SNAPSHOT: Versioning, Version 1
SNAPSHOT: This uses versioning to ensure that data will be consistent throughout the transaction. You get your own “version” of the data generated in tempdb, and if another transaction changes the data, you won’t see it. Readers don’t block writers and writers don’t block readers. You won’t get dirty reads, non-repeatable reads or phantom reads, but you might see concurrent update errors.
READ COMMITTED SNAPSHOT: Statement Based Versioning (or SNAPSHOT: Part Deux)
READ COMMITTED SNAPSHOT: A permutation of SNAPSHOT, which uses row versioning. Works at the statement level rather than the transaction level. Like SNAPSHOT, it helps to ensure better consistency. Very helpful for preventing blocking situations, but you need to watch tempdb, to ensure that it doesn’t bloat from all the versioning.
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT: For all your memory-optimized tabular needs
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT: (SQL Server 2014+). This one is for memory-optimized tables. Uses SNAPSHOT for all interpreted T-SQL on memory-optimized tables, whether or not the isolation level is set explicitly.
So, that’s how I keep isolation levels straight. How do you do it?