Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

When Snapshot Isolation Helps and When It Hurts

The benefits of SQL Server 2005's new snapshot isolation feature are well known, but SQL Server developers also need to understand the downstream ramifications of snapshot isolation.


advertisement
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)) go INSERT 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') go CREATE 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) ) GO INSERT 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



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap