SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Transaction Isolation and the New Snapsho
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/09/2007 :  11:07:29  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Concurrency and transaction isolation are a prickly subject, difficult to explain with any kind of clarity without boring the reader and leaving their poor brain in a complete muddle. Therefore, it is often ignored in the vain hope it won't affect us and we can forget all about it. Well you can't ignore it any more and with SQL Server 2005 there's a whole new isolation level added to the four that already exist. So I donned my leather gloves to make a brave attempt to make this subject approachable and get you to the end, without putting you to sleep or letting you wander over to the more interesting flames going on at Slashdot. So here it goes.

Read the article

Jim77
Constraint Violating Yak Guru

United Kingdom
440 Posts

Posted - 07/10/2007 :  09:21:35  Show Profile  Reply with Quote
Very good article explaining the new read committed snapshot isolation level better than I have ever heard it explained before and allowing me to resolve alot of my blocking problems.
Thanks alot.
Go to Top of Page

tony_hayes
Starting Member

1 Posts

Posted - 07/19/2007 :  08:59:33  Show Profile  Reply with Quote
Hi

I am on a site where we use sql 2000/2005 db2 and oracle.

I think this is an excellent article. Microsoft have certainly identified their weak concurrency problems and tried to mimic what oracle has done for years(, which is why oracle has always done better with thousands of users connecting doing updates, no locks.

Unfortunatley its a not quite mimiced what oracle does, because you would not get update errors like sql2005 does so it stll lags behind..

Having said that its a sep in the right direction.

But what your article did not go into detail with is the cost in terms of i/o requests to the tempdb database. I know from experience you can get locking on the tempdb if 2 users happen to right to the same page.... so if you have thousands of users what happens....

I guess wait for sql2008...


Go to Top of Page

surfandswim
Starting Member

USA
4 Posts

Posted - 04/09/2008 :  10:25:59  Show Profile  Reply with Quote
This is a good article, it looks like snapshot will reduce deadlocks in some cases.

I've awlays preferred to use SERIALIZABLE when inserting or updating, particularly when you're updating a master record with detail records simulataneously. The problem is that when you get a large number of users on the system you will get deadlocks, but at least your data isn't corrupt.

In our web-based products we use an object oriented design in a C# class library to represent all of our database tables. The objects all inherit from the same base class and they all implement a load, save and delete methods. The methods handle transactions on a save operation accross the main objects and detail objects when they need to be insert and updated simulataneously. This ensures that the data is not corrupted. Occassionally we'll see a deadlock in a very highly contested table with alot of users. One thing we've done is added the ability to catch a deadlock in specific operations and restart the operation when unsuccessful for a configurable number of retries and time to wait before retrying. This seems to work well in alot of cases, but really that only masks the problem.

I think we'll try the new snapshot with read committed to see if that reduces deadlocks in certain circumstances. I wonder if it really would help in some cases since we tend not to make reads and updates in the same transaction as some of the examples appeared above.

I will probably continue to think that in most cases, it's better to be pessimistic for safety, and to only look at the less pessmimistic cases when they are appropriate because in most cases corrupt data is worse than higher contention.

At any rates this is a great article, and our entire team will take a look at this and talk about it in our next development meeting. Thanks for writing it!
Go to Top of Page

jfuex
Starting Member

USA
29 Posts

Posted - 02/12/2010 :  16:36:07  Show Profile  Reply with Quote
Excellent article!

My brain was starting to hurt trying to understand the vagaries of transaction isolation and locking using only MSDN, but this made it crystal clear.

Thanks for posting it.
Go to Top of Page

triq200
Starting Member

1 Posts

Posted - 03/05/2011 :  14:42:23  Show Profile  Reply with Quote
Very useful article thanks. One minor error, in the SnapShot isolation section it says "Run these together starting statement 1 first. The short update will complete quickly. But, statement 2 will raise an error that the data has been modified in between reading the data and then attempting to update it." - this should say that statement 1 will raise the error.

Thanks again
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000