Blog

How to change isolation levels without losing your marbles.

A client said the coolest thing to me the other day. He said, “We talked before about why we would want to start using optimistic locking in our code. How do we get there?”

If you’re not a SQL Server geek, that comment probably doesn’t even make sense. But to some of us, when you get an environment to the point that it can handle optimistic locking AND you have developers who are ready to make a few code changes to use it to their advantage, it’s exciting!

I am a huge fan of optimistic locking in SQL Server and have seen dramatic improvements in performance when it’s implemented well. For the right database and the right team, a little hard work makes scaling the application easier in the long run.

Optimistic Locking 101: A Quick Review

SQL Server Isolation Levels Poster: cartoons make the crazy terminology less painful.

If you’re new to Isolation Levels in SQL Server, this post may be hard to follow. Consider taking a quick break to download our free poster on Isolation Levels and browse through our big list of references on Isolation Levels in SQL Server around the web.

Here’s a very simplified refresher for those who know their isolation levels, but need to brush out the cobwebs:

SQL Server uses “pessimistic” locking in user databases unless you tell it to do otherwise. Your default isolation level in SQL Server is READ COMMITTED. Although that sounds reassuring– who doesn’t want a little commitment from their database?– it’s a form of pessimistic locking. Under the READ COMMITTED isolation level readers can block writers, and writers can block readers. Sometimes the default isolation level isn’t strong enough to provide the right level of consistency, so people need to use higher (“more pessimistic”, if you will) isolation levels to prevent phenomena like non-repeatable reads and ghost records.

As load increases in a database, then tension between ensuring transaction isolation / correct results and also supporting concurrency increases. You start to battle with blocking and deadlocks. People start using the NOLOCK hint to work around problems, often with disastrous results.

Enter optimistic locking. SQL Server 2005 introduced two new isolation levels to help you in your mission towards ever greater concurrency: SNAPSHOT and READ COMMITTED SNAPSHOT isolation (this second option is often called “RCSI”). These two isolation levels can be implemented independently or together. They both use versioning in the tempdb database to allow some or all of your queries to speed on by “in flight” transactions and read a previously committed version of the data.

Readers Don’t Block Writers, Writers Don’t Block Readers

Pessimistic marbles

The simplest, purest benefit from optimistic isolation levels in SQL Server is just this: you can allow heavier, reporting style queries to run without blocking your critical writes under OLTP. You suddenly have another option between tuning every expensive query, using dirty reads, or having to run queries against a secondary server.

Sometimes you can decide, “This query isn’t the greatest, but it’s OK to run if it doesn’t block anyone and just reads previously committed data.”

And yes, you can do more than that. You can use the SNAPSHOT isolation level for data modification queries, but then you need to start detecting and handling update conflicts. (Note: this does not apply to READ COMMITTED SNAPSHOT.) There’s nothing wrong with doing that, but in most environments it is not the “biggest bang for your buck” change to implement.

How Do You Implement Snapshot or Read Committed Snapshot Isolation… Safely?

Moving to optimistic locking sounds great, but it’s not a quick change. Rush in too soon and you may suffer big performance problems, loss of availability, and incorrect query results.

That list of potential problems scares off most people. That list makes a project manager think, “I’ve got 99 problems and optimistic locking ain’t one.”

But it really isn’t that hard to test and implement optimistic locking. It just takes a little time and a good plan. Here are three steps I always include when planning out an Isolation Level change.

1. Measure the Effect that Enabling Optimistic Locking has on SQL Server Performance

When you have an existing code base and you’re evaluating optimistic locking, at first it seems that you have a little bit of a chicken and egg problem.

  1. You’re not sure if your production server can handle the load
  2. Testing resources are required to make sure the change won’t create bad data

Nobody wants to invest in testing if the environment can’t handle the load. And people don’t know how to test the impact of the feature safely if it hasn’t been proven to not create bad data. It may seem like a project management deadlock.

Optimistic marbles

Never fear– there’s a method to testing the load first! The key lies in understanding the difference between the two isolation levels. If you enable SNAPSHOT isolation on a database, SQL Server starts implementing versioning technologies so that queries can set their isolation level to snapshot. (Queries have to do this explicitly– as in run a command ‘SET TRANSACTION ISOLATION LEVEL SNAPSHOT’, or have it set in their connection string.)

Here’s the bit that’s easy to miss. As soon as you enable SNAPSHOT isolation on a database, SQL Server waits for running transactions to complete, then immediately starts using versioning for data modifications. You start using an extra 14 bytes per row on tables in the database itself. Also, versions are created in the tempdb version store to hold the previous value of data for updates, deletes, and some inserts. This happens even if no queries are run using SNAPSHOT isolation. Seriously.

This may seem a little bit like a rip off. Yeah, you’ve gotta pay for the performance even if you’re not using SNAPSHOT! But it’s for a good reason– your SQL Server does not yet have the feature of being psychic. It can’t predict if at any instant you’re going to start a query using SNAPSHOT, and it’s got to make sure that the database is ready if you do that at any time.

But this can also be a useful feature. You can test the performance impact of versioning for a database just by setting ALLOW_SNAPSHOT_ISOLATION to ON. When you issue the command to change this, SQL Server will wait for all active transactions to complete before returning a status to you (Books Online explains why and how to handle it here.) You can then monitor performance for as long as you have planned and then reverse the process.

As long as you haven’t changed any code and are using the ALLOW_SNAPSHOT_ISOLATION option, no queries will actually USE the changed isolation level and you can simply monitor performance impact of optimistic locking.

One gotcha: I’ve mentioned a specific setting on purpose. The READ_COMMITTED_SNAPSHOT option is named similarly, but is very different! That setting changes your default isolation level to use optimistic locking without code changes on your queries. As soon as you turn it on, some queries could start getting unexpected results or writing incorrect data. (An example of this is below in #3.) The process of turning on  READ_COMMITTED_SNAPSHOT also works differently. For a single instant the connection issuing the command to enable RCSI must be the only active command in the database. (I go into more detail on this in the “gotchas” section at the end of this guide.)

Because of both of these reasons, ALLOW_SNAPSHOT_ISOLATION is much more suitable to dip your toe in and find out how enabling optimistic locking impacts your workload. Still follow common sense: test first at low volume times and work your way up to busier times. Know that disabling and going back to “normal” may not always happen as fast as you want if you have long running transactions or a very heavy workload.

2. Plan to Prevent Disaster with Snapshot Isolation

The second step to identify what might get out of hand if something unusual happens– like a rogue session that leaves a transaction open. This isn’t a far fetched scenario. I’ve had this happen due to both human error as well as bugs in application frameworks. It’s really not that rare and it could happen to you.

Your plan needs to identify the biggest risks from the change for your business and what may be challenging to the team who responds to incidents. Then set things up so that if weird things happen, people get notified and know how to triage the issue.

Here’s an example of two things you may identify as your biggest concerns if you implement optimistic locking in SQL Server:

  • Tempdb could fill
  • Long version chains could be created, causing query performance to get super slow

How you handle these will vary depending on your monitoring system, tools available, and documentation. Even if you don’t have fancy tools, you can use SQL Server’s performance counter Alerts to let you know if used space in Tempdb rises above a given water line. You can also alert on performance counters for longest running transaction time, or use DMVs to monitor the version store in detail. (Note that not all these DMVs are lightweight to use when the version store gets large.)

Your mission is simple: identify the simplest way for you to monitor the conditions you’re concerned about. Test that it works on a non-production system. Most importantly, write down documentation for the alert that will help someone decode the problem if you’re not around to handle everything and take basic actions.

3. Choose Carefully Between Snapshot and Read Committed Snapshot Isolation (RCSI)

If the marble on the left is being updated to white, queries using optimistic locking will see the previous version of the marble.

OK, now this is where the developers come in. You can use optimistic locking in SQL Server by using either of these database options– or both. Which one is right for you?

The biggest consideration with an existing code base is how much testing is available for the code. I think the READ COMMITTED SNAPSHOT isolation level is great, but it is going to change your default isolation level for every query right away. This may lead to incorrect results, depending on how your code is written.

Don’t believe me? Here’s an example. This is based on Craig Freedman’s great example. I’ve just adapted the example to show the same phenomenon for READ COMMITTED SNAPSHOT, since his original example demonstrates the issue for SNAPSHOT.

First, set up the a database to test. Note that I’m enabling READ_COMMITTED_SNAPSHOT here. To compare test results with READ COMMITTED, run the test again with that set to OFF.

CREATE DATABASE IsoTest;
GO
ALTER DATABASE IsoTest SET READ_COMMITTED_SNAPSHOT ON;
GO

USE IsoTest;
GO
CREATE TABLE dbo.marbles
    (
      id INT PRIMARY KEY ,
      color CHAR(5)
    );
GO

INSERT  dbo.marbles VALUES  ( 1, 'Black' );
INSERT  dbo.marbles VALUES  ( 2, 'White' );
GO

Now fire up your test. In Session 1, run:

USE IsoTest;
GO
DECLARE @id INT;
BEGIN TRAN

SELECT  @id = MIN(id)
FROM    dbo.marbles
WHERE   color = 'Black';

UPDATE  dbo.marbles
SET     color = 'White'
WHERE   id = @id;

Open a second session. In Session 2, run:

USE IsoTest;
GO
DECLARE @id INT;
BEGIN TRAN

SELECT  @id = MIN(id)
FROM    dbo.marbles
WHERE   color = 'Black';

UPDATE  dbo.marbles
SET     color = 'Red'
WHERE   id = @id;

COMMIT TRAN
GO

Now, back in Session 1, run:

COMMIT TRAN
GO

Finally, check your marble colors. Run this in either Session 1 or Session 2:

SELECT  * FROM    dbo.marbles;

Your results will vary based on whether or not you have READ_COMMITTED_SNAPSHOT turned on.

  • With READ_COMMITTED_SNAPSHOT ON, you get one red marble and one white marble.
  • With READ_COMMITTED_SNAPSHOT OFF, you get two white marbles.

If those marbles are worth something, that difference would be a really big deal.

Not all coding patterns are prone to this issue. However, with an existing code base, validating all the code and ensuring that you don’t have any legacy code that’s prone to this issue isn’t always a simple thing. If you have the ability to make changes to your code and you already know a class of queries which you’d like to use with optimistic locking, you may prefer to introduce SNAPSHOT isolation into your environment first, instead of READ_COMMITTED_SNAPSHOT.

To implement SNAPSHOT isolation on some statements, you need to first enable it using the ALLOW_SNAPSHOT_ISOLATION database option. You can then implement SNAPSHOT isolation for some queries– you do this by setting the isolation level in a connection string or using ‘SET TRANSACTION ISOLATION LEVEL SNAPSHOT’. It’s easier to implement this at first for read queries which you’ve tested and know aren’t prone to data issues similar to what we showed above. This means that you may need to use separate connection strings for those queries, or make sure you reset and check the isolation level that you’re operating at after statements complete.

In other words, isolation level is a session level setting. You need to make sure that don’t use SNAPSHOT isolation by accident for more queries than you intend to.

Gotchas With Implementing Snapshot and Read Committed Snapshot Isolation Levels

There’s a few little facts that I’ve found over the years which I didn’t expect. They make total sense, I just hadn’t thought of them until I worked with optimistic locking in SQL Server.

    • Hints in your code still apply. Let’s say you have a problem with locking. Over the years NOLOCK hints are added in many places to help make this better. You finally get confirmation from your dev team that READ_COMMITTED_SNAPSHOT is safe for your applications and your change is approved, so you turn it on. You’re spending all those performance resources on versioning, but guess what? Those NOLOCK hints are still causing queries to do dirty reads instead of using the data versioning! The NOLOCK hints gotta go.
    • Update conflicts aren’t the same as deadlocks. Update conflicts are only possible when you use SNAPSHOT isolation for data modification queries– you don’t have to worry about these with READ_COMMITTED_SNAPSHOT. However, it’s often more practical for people to implement SNAPSHOT because of the testing issues I outline above. Even if you’re only implementing SNAPSHOT for read transactions, familiarize yourself with the error codes and messages for update conflicts and make sure your code handles error 3960 (“Snapshot isolation transaction aborted due to update conflict…”).
    • Enabling READ_COMMITTED_SNAPSHOT on a busy system is harder than it sounds. As I mentioned before, turning READ_COMMITTED_SNAPSHOT on or off is a little unusual. You don’t technically have to put the database into single user mode, but to get the command to complete you need to be running the only active command at the moment. The simplest way to do this is to use the ‘WITH ROLLBACK IMMEDIATE’ clause of the ALTER DATABASE command. However, I have not found this to run predictably or easily on very high transaction systems. I recommend planning a change to turn the READ_COMMITTED_SNAPSHOT setting on or off in a database in a very low volume time if you need to keep things predictable.
    • Rolling back and disabling SNAPSHOT requires more code changes. In order to stop row versioning, you need to disable SNAPSHOT — and as soon as you do that, queries that set the isolation level to SNAPSHOT and try to run will fail with Error 3292: “Snapshot isolation transaction failed accessing database ‘dbname’ because snapshot isolation is not allowed in this database”

Isolation Level Changes Are Not Actually Rocket Science

Isolation levels in SQL Server are complicated. But if you’re new to this, don’t think that it’s only optimistic locking that makes it complicated. It turns out that the read committed isolation level isn’t as straightforward and predictable as you might think, either!

As I work with SQL Server more and more, I continue to learn that isolation levels are complex, whether you’re using pessimistic or optimistic locking. The critical thing is to use a smart testing and implementation plan to safely find the settings that work for your team and applications.

More Tools for Slow SQL Servers

sp_Blitz®: Free SQL Server Health Check – You’ve inherited a SQL Server from somebody, and you have no idea why it’s slow. sp_Blitz® gives you a prioritized list of health and performance issues, plus gives you URLs for more details about each issue.

Our Free 6-Month DBA Training Plan – Every Wednesday, you get an email with our favorite free SQL Server training resources. We start at backups and work our way up to performance tuning.

SQL Critical Care® – Don’t have time to learn the hard way? We’re here to help with our quick, easy 3-4 day process that gets to the root cause of your database health and performance pains. Contact us for a free 30-minute sales consultation.

↑ Back to top
  1. Your optimistic marbles look a little to much like Jack from the Jack in the Box commercials….{-:

    Good article!

  2. Kendra – Great post!

    We dove into snapshot and RCSI on a project last year…and luckily since it was still in the development phase, we didn’t have to worry about disrupting a production environment. But there was still a bit of code review involved.

    Some of the tricky issues we came across dealt with the effects (of snapshot isolation) on trigger-based constraints. A detailed explanation (with simple examples) can be found in Defensive Database Programming with SQL Server, by Alex Kuznetsov…which is a free book, btw!

  3. Great article! It cleared up a question that I was asked today, and I appreciate it!

  4. Pingback: Something for the Weekend - SQL Server Links 11/01/13

  5. Hi Kendra,

    Must say a good article, beautifully describes both of these isolation levels and other reference material also add to its magic.

    Regards
    Gurpreet Singh Sethi

  6. Hi Kendra,

    Great article thanks! Could you clarify one point for me please………. in your article you have said

    “You start using an extra 14 bytes per row on tables in the database itself. Also, versions are created in the tempdb version store to hold the previous value of data for updates, deletes, and some inserts. This happens even if no queries are run using SNAPSHOT isolation. ”

    Does this mean that once snapshot is enabled 14k immediately gets added to every row in the DB? (This is what I inferred from your article) This does not appear to be the case in my testing. Or does this mean that from this point onwards each newly updated row contains an extra 14k?

    Thanks for your help!

  7. When we switched on RCSI on a large and very busy database we started getting a lot of deadlocks in certain areas of the system. What had been blocking locks no longer blocked, which allowed the code to continue on different paths until there was a deadlock. Ick.

    However, the fix was not too hard. It involved creating new indexes and the deadlocks went away.

  8. Pingback: Deadlock usando TransactionScope – @lekaverta

  9. Hi, quick question for you regarding SNAPSHOT isolation: I’m a bit confused by the “Update conflicts aren’t the same as deadlocks” warning in your “gotchas” section. If I’m understanding the SNAPSHOT isolation setting correctly, even though it’s always preparing data for snapshot queries, it only actually comes into play with an explicit ‘SET TRANSACTION ISOLATION LEVEL SNAPSHOT’ command, either inline or in the connection string, correct? If that’s true, and you do not set that level on your connection, how could you get an update conflict on an update if you did NOT explicitly set that level? If your only use of SNAPSHOT isolation is when you explicitly turn it on for reads, would you ever get an update conflict error?

    Thanks for your help! Great article — our system has grown rapidly over the past year, and I’m wrestling with the deadlock beast, so I need all the advice I can get!

    • Hi Landon!

      Yes– you only get the UPDATE conflict when you are using SNAPSHOT iso (and have explicitly requested it) and you are modifying data. (Won’t happen if you’re using read committed snapshot, and won’t happen if you are using Snapshot and NOT doing a modification.)

      Hope that helps,
      Kendra

      • Kendra,

        Yes indeed, that does help — thanks for the quick reply. I thought I was understanding it correctly, just wanted to make sure.

        And, if you have the time, here’s a follow-up question. If I’m not ready to go very optimistic (RCSI), which is a more appropriate first line of defense: using NOLOCK in my reads, or enabling SNAPSHOT and using that explicitly in my reads? I mean, I’m working on a schema re-design right now that should alleviate some deadlock pressure by removing some ugly read/update combination procs, but I’d like to safeguard against the problem as much as possible.

        Thanks again for your help — I really appreciate it.

        Cheers,
        Landon

        • I’m not the biggest fan of NOLOCK, but I think there can be a time and a place for it in small quantities. NOLOCK can be OK when:

          * Dirty reads (incorrect data) is OK to return to the user
          * It’s OK if a query sometimes fails with an allocation order scan error (this can happen with read uncommitted)
          * You can go into the code and pull out the NOLOCKs later on when you move to optimistic locking

          When you look at that list of three things, NOLOCK is pretty depressing. Queries can fail, can return wrong data, and end up with hardcoded hints that you have to clean up later when you do it a better way? That’s just kind of a drag. But it is a pretty quick fix AND it doesn’t require all the work of making sure that your tempdb can support versioning/ doesn’t run out of space/ etc.

          So the answer to this depends on how well you can support versioning in your environment. Do you have a strong enough IO subsystem and enough DBA support to handle turning on optimistic locking for SNAPSHOT at this point (everything from monitoring down)? If so, I would start there.

          • Thanks for your advice — it’s a big help. I think my true first line of defense is going to be a schema redesign to try to keep application processes from stepping on each other, and if that’s not enough, we’re moving to a bigger DB server, so I can try SNAPSHOT isolation. Wish me luck!

  10. Great Post, very informative

  11. Excellent information. An interesting side effect of enabling Read Committed Snapshot (RCS) I’ve found that SQL doesn’t always release data when deleting data from tables that contain LOB data (varchar(max), varbinary(max) etc) . We have a table that gets purged on a monthly basis and contains large amounts of data in the LOB columns, as soon as we enabled RCS the space the DB (and table) used stopped decreasing after the delete. As soon as you turn off RCS sql starts freeing decreasing the space the table is using.

  12. Thanks for the great article!

    If i enable SNAPSHOT isolation on my database, does this mean that only queries that now specify ‘SET TRANSACTION ISOLATION LEVEL SNAPSHOT’’ will always read the snapshot version of the data in tmpedb?

    If so, then is it fair to say that usually only SELECT statements get re-jigged to use the snapshot so that ‘readers don’t block writers and vice versa’?

    Thanks

    • Maybe i should have been more clear, my bad, i meant if i enable ‘ALLOW_SNAPSHOT_ISOLATION ‘ on…

    • Hi Mark,

      This is a bit complicated. There are two settings, and they are named very similarly, so that’s the source of most of the confusion.

      1. SNAPSHOT – This can be enabled for a database and it starts row versioning. Only queries that specify ‘SET TRANSACTION ISOLATION LEVEL SNAPSHOT’ will use it (and the generated versions of data). This lets you use optimistic locking at a transaction level (can be more than one statement). You *can* use this one for update transactions, I don’t recommend starting there however.

      2. READ COMMITTED SNAPSHOT – This can be enabled for a database and ANY query that uses read committed will automatically start using this isolation level and the generated version. This lets you use optimistic locking at a single statement level. If you have NOLOCK hints in your code or other hints setting isolation levels to things other than read committed, those will continue to be honored.

      Hope this helps!

      • Thank you Kendra for the quick response! Without knowing too much more, i guess its safe to say (as you mentioned), TEST TEST first using ALLOW SNAPSHOT ISOLATION ON, and using SELECT queries and determining the performance impact first (tempdb, long version stores, etc..) before going any further.

        Have a good weekend!

      • Hi Kendra,

        Great article! I have a follow up question to this.

        From (http://technet.microsoft.com/en-us/library/ms189050(v=sql.105).aspx):
        When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

        >logical copies (versions) are maintained for all data modifications performed in the database.

        This part worries me. If I set ALLOW_SNAPSHOT_ISOLATION to ON then will *all* data modifications have row versions in tempdb, even default READ COMMITTED ones? My ideal would be to have only SNAPSHOT ISOLATION transactions keep row versions. But if this were the case, I guess this would mean that an update under READ COMMITTED would still block a select under SNAPSHOT ISOLATION.

        Anyway, so my question is: If ALLOW_SNAPSHOT_ISOLATION is ON, then will all data modifying transactions of any isolation type create row versions in tempdb?

        Thanks

        • Yes, versions have to be created for all modifications, regardless of their isolation level.

          To simplify it down, let’s just talk about ALLOW_SNAPSHOT_ISOLATION for a second. When someone is updating data, SQL Server has no idea if a transaction using SNAPSHOT isolation might start at any instant– so it has to do versioning for the modification just in case. Because how could it know?

  13. READ COMMITTED transactions will escalate to snapshot isolation if you set the READ_COMMITTED_SNAPSHOT option as Kendra describes.

    This works fine for single atomic queries but not so great if you want to do multiple reads on a set of snapshot tables, but it depends on whether you care about reading those published pieces of data as sets’

    You can read my post on this for more details:
    http://brettwgreen.wordpress.com/2013/03/21/reading-from-snapshot-databases-with-multiple-tables/

  14. Thank you for a great article! I have a question about locking behavior when you use RCSI. If row level locking is used, I see no issues, since only the changed row snapshot will be created. But if sql decides to use page(s) locking, does it mean that it has to create a snapshot for every row in those pages? I hope the answer is no, because if yes, I would seriously consider disabling PLL. Could you please clarify?

    • Hi Ilya,

      This is implemented as row versioning– and the rows versioned are the rows modified (not the rows locked). You can prove this yourself in a test database (far far away from production) by using modification queries with locking hints that force page locks or table locks, and snooping around in the actual version store records in sys.dm_tran_version_store. (Be very very careful with that in production, as books online notes it’s SUPER detailed.)

      http://msdn.microsoft.com/en-us/library/ms186328(v=sql.105).aspx

      Hope this helps,
      Kendra

  15. please send me new notes….

  16. Quick question… let’s say I have a stored proc that calls another stored proc that in turn implements “SET TRANSACTION ISOLATION LEVEL SNAPSHOT”; when the stored proc returns, is the isolation level still set to SNAPSHOT, or does it retain that of the current stored proc before the call? Or, is the scope of the isolation level… isolated … to the scope of the called stored proc?

    • Hey Greg,

      Short answer: “Isolation levels or lock hints set in stored procedures or functions do not change the isolation level of the connection that calls them and are in effect only for the duration of the stored procedure or function call.” (From: http://msdn.microsoft.com/en-us/library/tcbchxcb.aspx)

      So the scope should just be to the “child” stored procedure.

      Longer story: I’ll demo this in an upcoming blog post to show how you can prove that it’s working yourself.

  17. Thank you K for this article. I am going to do a 3 part training on locking, Blocking and noLocking for my developers (for me too.) would you suggest a book and maybe a Youtube Video?

  18. I think I’m in love!! ;-)

    This was a FANTASTIC article!! I’ve been struggling trying to understand the nuances of the whole snapshot isolation paradigm and couldn’t find anything that really broke it down for me in easy to swallow nuggets…VOILA!!

    I can’t believe I haven’t run across your blogs in my ten years of working with SQL Server.

    You have a new fan!!

    A thousand thank you’s!!

  19. Great article!! I noticed you mention that the isolation level can be set in the connection string. Do you have an example of how to set via the connection string?? I can only find examples directly setting within each individual query using “SET TRANSACTION ISOLATION LEVEL SNAPSHOT” or by using the ADO.NET SqlConnection.BeginTransaction method. For example: If I wanted certain reports to quit blocking DML operations, it would be much easier to add (or remove as part of a rollback plan) snapshot isolation in the connection string of a few shared DataSources on a reporting server vs. changing each query for each dataset in each report.

  20. Pingback: DBCC USEROPTIONS: See Your Session Settings in SQL Server | LittleKendra.com

  21. The link to Alex Kuznetsov’s Defensive Database Programming book has moved:
    http://www.red-gate.com/community/books/defensive-database-programming

  22. this is a monograph please be brief when you explain something

    • Hey Miguel,

      On complex how-to topics, we do sometimes choose to put lots of detail into a single post. Otherwise you end up with 12 part posts where folks have a hard time finding everything.

      If you need a brief explanation of how to do what we describe in this article, I’m fairly certain the feature isn’t a good fit for you. There are a lot of gotchas, and you’re likely to end up in an outage situation.

      Best of luck,
      Kendra

  23. Hi,

    For implicit TSQL commands (INSERT/SELECT) that are not wrapped inside an explicit transaction, will simply turning on snapshot isolation be sufficient or Do I need to explicitly wrap it with a BEGIN TRAN ?

    I don’t want to turn on read committed snapshot option as it applies for entire DB activity.

    Thanks.

    • Hi Praveen,

      To use SNAPSHOT, you must explicitly set the isolation level for the session to snapshot– but you don’t have to stop using autocommitted transactions as long as you do that.

  24. One of the issues with RCSI referenced in books online (http://msdn.microsoft.com/en-us/library/ms188277(v=sql.105).aspx) is that “•Update performance can be slower due to the work involved in maintaining row versions”. You mention that this is also true for some inserts. What about bulk insert methods that are non-logged (BULK INSERT, INSERT INTO … WITH (TABLOCK), etc)?

    • Hey Tim,

      Bulk inserts are sometimes minimally logged, but they do get logged. Typically an insert won’t generate new records in tempdb because there’s no “old version”, but there’s the extra overhead from the on-row pointer.

      Still, I would look at data modification performance on a case by case basis. The amount of work the version store is doing varies by workload, and of course in many workloads data modifications may be faster with RCSI due to reduced blocking!

      If you’re concerned about a specific case with bulk load then I would just do some testing with a restored copy of the database and time how long it takes with both settings and your specific bulk commands.

      The most common risk I’ve found with optimistic locking is that long-running transactions cause version store pileup, and that causes query performance problems with anything using versioning (which is a lot!). So that’s still one of the main things I’d monitor for.

  25. Hi Kendra,

    Fantastic article, I really learnt alot from this. But I have some follow up questions:

    If I enable RCSI, do I NOT have to set session level transaction isolation?

    If my application use’s bcp to do an insert into temporary tables, will I need to configure the loader script to use bcp bulk insert with a TABLOCK hint in order for it NOT to conflict?

    Do you have an example of bcp statements when RCSI is enabled?

    If RSCI is enabled and I’m using bcp, should I consider modifying batchsize to reduce the amount of time bcp locks my table? Do you have any examples of how you would do this (optimum batchsize based on record count)

    • Hi Andrew– glad it’s helpful!

      If you enable RCSI it changes the default isolation level for queries using the database. So you don’t have to set it on every session. However, if you’re already setting it to something like READ UNCOMMITTED with statements or hints, that’ll still take effect.

      For your BCP question, if they are temp tables as in tempdb then I’m not sure they’d be impacted? Perhaps I’m misunderstanding. But either way I would just encourage you to do some testing.

  26. Hi Kendra
    Have added Transaction Isolation Level Read Uncommitted to all Stored Procedures.
    Also, Have added WITH(NOLOCK) Table hints in SPs.

    These are added to avoid Blocking and Deadlock – We are facing lot of issues because of deadlock.

    If i go for above 2 implementations then what would be the Pros and Cons of Uncommitted isolation ?

    Note : We cannot go to SNAPSHOT Isolation and at the same time i need to the better Concurrency.
    Your help would be much appreciated.

    • Hi Praveen,

      This post isn’t really about deadlocks or dirty reads specifically. Check out Jeremiah’s posts on deadlocks here: http://www.brentozar.com/archive/2011/07/difficulty-deadlocks/

      There are a lot of downsides to read uncommitted. We have a video on that here: http://www.brentozar.com/archive/2011/11/theres-something-about-nolock-webcast-video/

      Kendra

      • Hi Kendra,
        That was really good article about deadlocks indeed. But i have few followup questions about No Lock.
        1.To eliminate deadlocks, I am using no-lock/uncommitted in my queries,but again this will cost Dirty read and concurrency would be affected. I cannot go for RCSI as of now, cos that would need extensive testing and would be stuck in FULL Tempdb scenario. So, what is the best and quickest way to mitigate the deadlocks ?
        Its for sure that i am going to work with few architectural patterns to use in an application to avoid deadlocks as stated in your links.

        Thanks

        • Praveen – to get the right answer for your scenario, we would need to know more about the underlying tables and the queries involved. It’s a little beyond what we can do quickly in a blog post comment, but if you can post example schema, code, and deadlock graphs to http://DBA.StackExchange.com showing the problems, you might find folks willing to help for free. Enjoy!

  27. Hi Kendra,
    Just wanted to confirm my understanding of your marbles(!) example..

    With READ_COMMITTED_SNAPSHOT ON, the reason why your marble is RED is:
    – it uses transaction sequence numbers against the objects being updates, so Transaction 2 has a more recent transaction sequence number than Transaction 1, so when T1 gets committed, the real value is that of the latest transaction sequence number for that object (T2)

    WITH READ_COMMITTED_SNAPSHOT OFF, the reason why your marble is WHITE is:
    – T2 is blocked by T1, so when T1 commits, it changes the black marble to white. Then T2 is allowed to go through but there is no more black marble to update to red.

    Do I understand correctly? Thanks in advance and happy holidays!

  28. We have implemented SNAPSHOT for only 3 main/long-running reports in our ASP.NET app which tend to cause deadlocks with other portions of the system (previously we used NOLOCK, but removed that and started using SNAPSHOT)

    At first, everything seemed great. SNAPSHOT was used ONLY for read-reports (as the first line of the SQL query) and never for any inserts/updates.

    Here is the problem: Even though our SNAPSHOT argument is ONLY used for the read-reports (as a first line in the query), we are getting occasional exceptions for other queries such as:

    Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.tbl_Name’ directly or indirectly in database ‘db_name’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

    But, we are NEVER using the SNAPSHOT argument for these insert/update queries. I realize that the snapshot setting is kept per session, but we open/close our database connections properly for every individual query (This is an ASP.NET web application) so any insert/update query gets a fresh sql connection, always.

    Can you offer any suggestions? I would really like to avoid going back to NOLOCK.

    Thanks

  29. Kendra,

    Thanks for the response. Yes, we use methods like this to connect to the database and view data…

    Dim SqlConnection As New SqlConnection(“connection string here…”)
    Dim SQLstring as string =
    “SET TRANSACTION ISOLATION LEVEL SNAPSHOT;” & vbcrlf &
    “Select * From SomeTable;”
    Dim SqlCommand As New SqlCommand(SQLstring, SqlConnection)
    SqlConnection.Open()
    Dim sqlreader As SqlDataReader = SqlCommand.ExecuteReader()
    Do While sqlreader.Read()
    ‘// Some process to prepare data for viewing in aspx page
    Loop
    sqlreader.Close()
    SqlConnection.Close()

  30. Kendra,

    We use the same connection string for everything (set in the web.config)

    If I understand you correctly, for any snapshot transactions, I could simply add something to that connection string (even if I am adding an unnecessarily redundant option) to change the string so that it wont be used for the other queries which can’t use snapshot. Is that correct?

    Thanks,
    Chris

  31. Yep– pooling is very finicky and variations in the string will cause different pools to be use. In the Stack Overflow item the poster suggests a couple of options (logins, an option on the pool), but even capitalization, ordering of options, and spacing can cause different connection pools to be used in .NET.

  32. Kendra,

    Thank you so much. You really helped me out of a jam.

    Regards,
    Chris

  33. Pingback: Snapshot Isolation | My MSSQL snippets

  34. Kendra,

    Unfortunately, through trial and error (and found MS documentation stating same) we found that changing a case or adding something to a connection string does not separate that unique connection string. It is a myth. The only way to accomplish this goal and separate the connection pool to only certain queries which use that connection string is to use another SQL Server Login/User.

    Hopefully, this helps someone else.

    Regards,
    Chris

  35. Hi Kendra,
    Thanks for the article, I love reading your books and articles. I have one simple question. After doing some test on a dev server set on read committed and reading some other articles online, I’m having trouble finding a scenario where a reader blocks a writer in read committed. In one query window I am running queries like (TABLE_A has millions of rows)
    BEGIN TRAN
    SELECT *
    FROM TABLE_A
    WHERE COLUMN_A = 1

    or

    BEGIN TRAN
    SELECT TOP 100000 FROM TABLE_A

    and in another window I’ll run
    UPDATE TABLE_A
    SET COLUMN_B = ‘updated’
    WHERE COLUMN_A = 1

    and it always works even if I leave the previous transaction open, even if I put the select statement in a while loop. Shouldn’t it be putting a shared lock on the whole table and not allowing an update?

    • Mike – just because you have a select statement open in a transaction doesn’t mean you’ve locked the rows. You might be thinking of the REPEATABLE READ isolation level – in that one, you’re putting locks on selects because you want to be able to repeat your read again and get exactly the same data.

    • Hey Mike,

      SELECT statements under read committed will take out locks, but I like to say it “Loves the one it’s with” (whether it be a row or page lock). So it is releasing the locks quickly (even with the transaction open), and it’s tricky to reproduce with just a couple of windows in management studio.

      Just off the top of my head, for demo purposes you could inflate a really large table (add a lot of rows, maybe do something awful like rebuild it with fillfactor=1), make the select do a table level lock (by a hint or finagle a query that’ll do lock escalation), and then run an update and you should be able to see blocking if you time it right and the reads are slow enough.

      I have occasionally written demos that rely on blocking, and using small demo databases and having SSDs on my laptop definitely makes it much harder. I’ve periodically put demo databases on rotational drives connected by USB to make it a bit easier by slowing down storage response time. (Also requires limiting the memory on the VM, of course.)

      KL

      edit: added more info on why this is tricky

  36. Thanks so much for the quick response Brent and Kendra! I’m not sure if I needed the Begin Tran there for my little demo, but I’m just using ReadCommitted right now. I was reading an Erin Stellato (who is also awesome!) article http://erinstellato.com/2012/03/isolation-levels-effects-on-select-statements/
    where she says that Readcommitted only locks until the resource is read. So amidst my confusion I ran the above query, except with no where condition (just SELECT * FROM TABLE_A) and then in another window I queried the tran locks DMO

    SELECT * FROM sys.dm_tran_locks

    and I could see two rows had been added – one with an IS lock for the OBJECT TABLE_A and an S lock for the data page. But I could still update.

    So I added the hint WITH TABLOCK and the page lock went away and the IS lock for the object turned into S lock and I could not update.

    So in short I think I’m coming away that it’s a little uncommon to have updates blocked by readers if readcommitted is isolation level because the locks are dropped so quickly that the update will be fine, And one of the ways the update can be blocked if its a table lock on a large table.

    Thanks so much both of you for your help in understanding this!

  37. Fantastic article Kendra. One question I have (I’m evaluating the use of Transaction level snapshots where a long read of an audit table for reporting is locking the audit table, so that it cannot be updated by other online transactions, and client connections are therefore timing out) is:

    For how long does a version of a row exist in the tempdb version store? When is an old version deleted?

  38. Great article Kendra,

    I’m investigating the use of TRANSACTION ISOLATION LEVEL SNAPSHOT for a long-running read against an audit table for reporting purposes, as the current SELECT query is locking the audit table so that is cannot be updated by other online transactions, causing Client connections to timeout.

    So my question is, for how long does a row version exist in the tempdb version store? When is an old version deleted?

    Thanks,
    Jools.

    • Thanks!

      Cleanup will kick in based on the longest running transaction. So if you have transactions that might stay open for hours or days, that can cause quite the pileup if the system has lots of changes. There’s performance counters you can monitor for longest running transaction time and version store size.

      Here’s what to do next: experiment on your test system by generating activity, leaving some transactions open, and watching those counters. Use that to design your monitoring around it.

      Hope this helps!
      Kendra

  39. Pingback: Optimistic or Pessimistic Locking | DAILAN

  40. Kendra, great article!!! One question. so if you have Snapshot Isolation enabled on the DB and you still have NOLOCK in the queries, will the query bypass the versioning in tempdb?

    • Ooo, great question! I was 99% sure I knew the answer to this, but I love to test this stuff, so here’s what I did:

      In my copy of the StackOverflow database, this returns 1 row:
      SELECT COUNT(*) from dbo.Posts (NOLOCK) where Id=9346444

      In session 1, I ran:
      BEGIN TRAN
      DELETE FROM dbo.Posts where Id=9346444

      In session 2, I ran:
      create proc #test
      AS
      SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
      SELECT COUNT(*) from dbo.Posts (NOLOCK) where Id=9346444
      GO

      exec #test

      (The stored procedure wasn’t strictly necessary, I just did it that way.)

      It returned: 0

      So it read the dirty data of the non-committed delete, and also honored the NOLOCK hint.

      Hope this helps!

  41. There’s a lot of really great stuff in both the post and the comments/replies, will be a lot for me to ponder.. thanks for writing!

    Something very simple that I’m still too much of a newb to figure out quickly — if I had a chunk of code running at READ UNCOMMITTED, part of which was a SELECT that didn’t care about the above-outlined issues with dirty reads, and afterwards there was an UPDATE or INSERT statement, does SQL Server ignore the TRANSACTION ISOLATION LEVEL and still grab the locks that it needs to do said UPDATEing / INSERTing? Or will this throw errors? Known issues?

    Thanks again,

    J

    • That’s a great question! Inserts, updates, and deletes have to take out exclusive locks, even if you’re in read uncommitted. (It’s implemented in a pretty fancy way involving intent locks, update locks, etc.)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php