When Snapshot Isolation Helps and When It Hurts

When Snapshot Isolation Helps and When It Hurts

ou know SQL Server 2005’s new snapshot isolation feature can help you reduce lock contention and deadlocks, but did you know that it also can help you fix intermittent errors in reports and detect lost updates? You know that snapshot isolation requires some effort from your DBA, but did you know it may introduce some breaking changes into applications? Its benefits are well known, but SQL Server developers need to understand the downstream ramifications of snapshot isolation. This article discusses a couple of common real-world scenarios that illustrate the often-unanticipated consequences of using this feature.

The scenarios use the following sample tables, which you should create in your database for reference:

CREATE TABLE Users(UserId INT NOT NULL,  FirstName VARCHAR(8),  LastName VARCHAR(8),  LotsOfComments CHAR(5000), CONSTRAINT PK_Users PRIMARY KEY(UserId))goINSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(1, 'Jill',  'Larson', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(2,  'Brian', 'Taylor', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(3,  'Lester', 'Rash', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(4, 'Sue',  'Miller', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(5,  'Justin', 'Rowler', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(6, 'Joe',  'Hanson', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(7,  'Brenda', 'Tyler', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(8, 'Drew',  'Wellner', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(9, 'Sue',  'Hogan', 'Top Notch Pro')INSERT INTO Users(UserId, FirstName, LastName, LotsOfComments) VALUES(10,  'Xavier', 'Sanchez', 'Top Notch Pro')goCREATE TABLE Tickets(TicketId INT NOT NULL,   AssignedTo INT NOT NULL,   Priority VARCHAR(10),  Status VARCHAR(10),  LotsOfComments CHAR(5000),  CONSTRAINT PK_Tickets PRIMARY KEY(TicketId),  CONSTRAINT FK_Tickets_Users FOREIGN KEY(AssignedTo) REFERENCES Users(UserId))GOINSERT INTO Tickets(TicketId, AssignedTo, Priority, Status, LotsOfComments)  VALUES(1, 1, 'High', 'Opened', 'Cannot login')INSERT INTO Tickets(TicketId, AssignedTo, Priority, Status, LotsOfComments)  VALUES(2, 8, 'High', 'Opened', 'Reports hanging')INSERT INTO Tickets(TicketId, AssignedTo, Priority, Status, LotsOfComments)  VALUES(3, 10, 'High', 'Opened', 'Cannot save changes')go

Scenario 1. Reports in OLTP Environment Have Inconsistencies
Clearly the database structure guarantees a ticket to be assigned to one and only one user at a time. However, sometimes a simple query like this:

SELECT FirstName, LastName, TicketId, Priority, Status,SUBSTRING(Tickets.LotsOfComments, 1, 25) Comments   FROM Users LEFT OUTER JOIN Tickets ON Users.UserId = Tickets.AssignedTo

returns obviously incorrect results:

FirstName LastName TicketId    Priority   Status     Comments--------- -------- ----------- ---------- ---------- -------------------------Jill      Larson   1           High       Opened     Cannot login             Brian     Taylor   NULL        NULL       NULL       NULLLester    Rash     NULL        NULL       NULL       NULLSue       Miller   NULL        NULL       NULL       NULLJustin    Rowler   NULL        NULL       NULL       NULLJoe       Hanson   NULL        NULL       NULL       NULLBrenda    Tyler    NULL        NULL       NULL       NULLDrew      Wellner  2           High       Opened     Reports running very slowSue       Hogan    NULL        NULL       NULL       NULLXavier    Sanchez  1           High       Opened     Cannot login             Xavier    Sanchez  3           High       Opened     Cannot save changes      

Why is ticket number 1 assigned to both Jill Larson and Xavier Sanchez? This intermittent problem manifests itself only when the data is selected and modified simultaneously. If you query only static data, you can never reproduce this problem. The following instructions show you how selecting data that is being modified can cause this elusive error. (Note that so far all queries run under the default isolation level, READ COMMITTED.)

In SQL Server Management Studio, open three tabs?that means three connections. Use the database in which you created sample tables. The query in each tab starts with SELECT @@SPID, so that you can identify these connections in the Activity Monitor. In the first tab, modify the Tickets table as follows:

SELECT @@SPIDGOBEGIN TRANSACTIONUPDATE Tickets SET LotsOfComments = 'Reports running very slowly' WHERE TicketId = 2

In the second tab, start your report as follows:

SELECT @@SPIDGOSELECT FirstName, LastName, TicketId, Priority, Status, SUBSTRING(Tickets.LotsOfComments, 1, 25) Comments   FROM Users LEFT OUTER JOIN Tickets ON Users.UserId = Tickets.AssignedTo

Note that your report hangs. If you open Activity Monitor, you’ll see that the report is blocked by the uncommitted update in the first tab. While the report is still waiting, make another modification against the Tickets table in the third tab as follows:

UPDATE Tickets SET AssignedTo = 10 WHERE TicketId = 1

Note that this update completes right away. Go to the first tab and commit. Note that the report in the second tab completes right away, and the results are incorrect (precisely the ones I listed at the beginning of this section). To make sure the report returns correct results, you can use a higher isolation level for it. That will increase lock contention.

For instance, if you repeat the same scenario but run the report under the REPEATABLE READ isolation level, the update in the third tab will not complete right away. It will be blocked by the report. You can open Activity Monitor and see that for yourself. Although the report will return the correct results, the price tag for consistency prior to SQL Server 2005 may be quite steep?increased lock contention. If you are already using SQL Server 2005, you can utilize one of the two new isolation levels, either SNAPSHOT or READ COMMITTED SNAPSHOT. (If you need detailed information for configuring your database to enable snapshot isolation, refer to “Choosing Row Versioning-based Isolation Levels” in MSDN.)

No matter which snapshot isolation level you choose, the report will return right away. Note that the report will show the old value for the description of ticket number 2. Instead of “Reports running very slow,” you will see “Reports hanging.” The uncommitted changes from the first tab are invisible to the report. In this particular case, the problem with a report was very easy to notice. In many similar cases, you may get slightly incorrect totals that go unnoticed for quite a while.

Scenario 2. How Snapshot Isolation Prevents Lost Updates
In this scenario, a long-running transaction can detect lost updates under SNAPSHOT but not under READ COMMITTED SNAPSHOT. Also, in this scenario a query running under snapshot isolation exhibits intrinsic optimistic locking behavior. Consider a long batch job that archives old tickets into a history table called TicketsArchive with the same structure as the Tickets table. Assume that archiving is implemented as one large transaction that users need to be able to abort. The implementation of aborting is simple: if a row is inserted into the CancelArchiving table while the archiving transaction is running, the archiving transaction is supposed to detect it and abort.

To start the batch, begin by inserting old tickets into a history table (TicketsArchive) as follows:

DELETE FROM CancelArchivingBEGIN TRANSACTIONINSERT INTO TicketsArchive(TicketId, AssignedTo, Priority, Status,LotsOfComments)  SELECT TicketId, AssignedTo, Priority, Status, LotsOfComments  FROM Tickets WHERE TicketId < 10

The next step involves archiving various attachments, and it takes considerable time. Suppose that while the process is running, a different connection inserts a request to cancel out:

INSERT INTO CancelArchiving(ReasonForCancel) VALUES('Monthly Report For CEO Not Started Yet')

Other connections continue to modify the data in the range that is going to be deleted, as follows:

UPDATE Tickets SET AssignedTo = 4 WHERE TicketId = 1INSERT INTO Tickets(TicketId, AssignedTo, Priority, Status, LotsOfComments) 
VALUES(5, 5, 'Medium', 'Opened', 'Aggregates In Reports Do Not Match')

Eventually, the archiving transaction gets to the point when it tries to check if there is a request to cancel it:

DECLARE @IsCancelled INTSET @IsCancelled = (SELECT COUNT(*) FROM CancelArchiving)PRINT @IsCancelled 

Under READ COMMITTED SNAPSHOT, the request to cancel is detected, because it was committed before this select started. Under SNAPSHOT, however, the request goes unnoticed, as are any other changes committed after the transaction has started?and this can be a breaking change! This is an important difference that you need to consider when choosing which snapshot isolation level to use. Suppose, however, that the transaction has no requests to cancel and proceeds to delete:

DELETE FROM Tickets WHERE TicketId < 10

Again, the results under READ COMMITTED SNAPSHOT and those under SNAPSHOT are very different. Under READ COMMITTED SNAPSHOT, the delete succeeds because the changes (the insert and the update in the range that is going to be deleted) were committed before this delete?this particular statement?started. As a result, the updates to tickets 1 and 5 disappear without any warning. This is yet another manifestation of a well-known problem of lost updates. On the other hand, under snapshot isolation, the problem is detected and the delete errors out:

Msg 3960, Level 16, State 2, Line 1Snapshot isolation transaction aborted due to update conflict. You cannot usesnapshot isolation to access table 'dbo.Tickets' directly or indirectly indatabase 'Sandbox' to update, delete, or insert the row that has been modifiedor deleted by another transaction. Retry the transaction or change the isolationlevel for the update/delete statement.

As you have seen, snapshot isolation conveniently provides optimistic locking for you. This capability may come in very handy in some situations. However, this feature may also cause breaking changes. Besides, in this particular case, it might be better to use the SERIALIZEABLE isolation level for large transactions. Anyway, snapshot isolation exposed a serious problem with lost updates. It is entirely up to you to decide how to fix it. (Note: the debate about pessimistic vs. optimistic locking is beyond the scope of this article. If you choose to use pessimistic locking, you can use higher isolation levels.)

The SNAPSHOT or READ COMMITTED SNAPSHOT Isolation Level?
MSDN recommends using READ COMMITTED SNAPSHOT rather than SHAPSHOT under most circumstances. It might also be easier to use. Once enabled, it becomes your default isolation level. However, in some cases, READ COMMITTED SNAPSHOT is not your best choice. Because READ COMMITTED SNAPSHOT, once enabled in the database, becomes the default isolation level, you can end up with breaking changes in you applications. For example, suppose READ COMMITTED SNAPSHOT is not enabled in the database and you want to assign one more ticket to a person, but only if that user does not already have high priority tickets:

BEGIN TRANSACTIONUPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 1AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')--- do not commit yet

Note that you have not explicitly specified an isolation level, so your transaction runs under the default READ COMMITTED level. If another connection issues a similar update:

UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 2AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')

it will hang in a lock waiting state. Once you commit your first transaction the second one will complete, but it will not assign ticket 2 to user 6, which is the correct behavior as designed.

Suppose your DBA has enabled READ COMMITTED SNAPSHOT. When you rerun your two queries, you will end up with two high-priority tickets assigned to the same person?not as designed, and you will not receive any warning messages whatsoever! (For more examples of breaking changes introduced by snapshot isolation, refer to Hugo Kornelis's series of articles on sqlblog.com and to my previous DevX article "Avoid These Common Business Rules Implementation Mistakes.")

As you have seen, if you rely on the default isolation level in your queries, you might prefer to use SNAPSHOT over READ COMMITTED SNAPSHOT, at least until you have explicitly specified READ COMMITTED whenever you assumed it. Also note that in some cases transactions behave differently under the SNAPSHOT isolation level than they do under the READ COMMITTED SNAPSHOT isolation level. In any case, explicitly specifying transaction isolation levels prevents your code from breaking after database configuration changes.

devx-admin

devx-admin

Share the Post:
Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India,

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1