Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-07-09 : 11:07:29
|
| 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
440 Posts |
Posted - 2007-07-10 : 09:21:35
|
| 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. |
 |
|
|
tony_hayes
Starting Member
1 Post |
Posted - 2007-07-19 : 08:59:33
|
| HiI 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... |
|
|
|
surfandswim
4 Posts |
Posted - 2008-04-09 : 10:25:59
|
| 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! |
|
|
|
jfuex
Starting Member
29 Posts |
Posted - 2010-02-12 : 16:36:07
|
| 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. |
|
|
|
triq200
Starting Member
1 Post |
Posted - 2011-03-05 : 14:42:23
|
| 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 |
|
|
|
|
|
|
|
|
Article: Transaction Isolation and the New Snapsho