 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008deadlocks: does clustered index is more prone to deadlocking than
Hi folks I see plenty of deadlocks in my application. They do not happen inside of transactions as many books like to describe. But: 1. locking mode is serializable. 2. 2 similar update statements collide with each other. There is something like: update my table where guid=@id ... and one more like this one. There is a cl. index on guid columns, non-cl. index on some other columns. What i like to know your opinion on: 1. Does serializable mode contributes to deadlocks. 2. If i changed index on guid column from cl. to non-cl. index, would it reduce passobility of deadlocks. 3. do you think snapshot isolation mode would help in this particular situation - update against another update. Thank you, Gene. >Hi folks >I see plenty of deadlocks in my application. They do not happen inside of [quoted text clipped - 6 lines] >What i like to know your opinion on: >1. Does serializable mode contributes to deadlocks. Yes. >2. If i changed index on guid column from cl. to non-cl. index, would it >reduce passobility of deadlocks. I presume it is also the PK, and still would be? I do not think it would matter. >3. do you think snapshot isolation mode would help in this particular >situation - update against another update. An interesting question, I have not yet had the chance to use it. Under serializable mode, I think not. Of course, I suppose you can try it! I *suspect* that what your problem is, is not really the updates colliding, but that in serializable mode it is the search for eligible rows that is getting tangled. Adding an index on the other critical columns in the query might be the better fix. Does one update even by itself, run for a long time? Josh >Thank you, Gene. Josh Your guess is right: guid has PK clustered constraint. It executes in serializable mode and deadlock happens on update of pages: one process is trying to get cl. index pages update, another one is trying to get non-cl. index page. Can you please explain why serializable you think contributes to deadlock. While i am not sure, but I would think it contributed to lower concurency and lower deadlocking. Here is why: exclusive locks are placed on range of rows and index rows. That way potential deadlock candidates are not given any locks and wait to aquire locks. Timeouts would be more probable scenario here. I am going to check if row level locking is allowed on both indexes. Who knows, these databases were upgraded from 2k and before from version 7. thanks, Gene. > >Hi folks > >I see plenty of deadlocks in my application. They do not happen inside of [quoted text clipped - 32 lines] > > >Thank you, Gene. Gene 2. 2 similar update statements collide with each other. There is something like: update my table where guid=@id ... and one more like this one. I assume you do have more complicated scenario rather than simple UPDATE statement. I mean you should have number of DML opeartions under SERIALIZABLE level is that right? Do you enable trace to get out deadlock's info? UPDATE is doing a seek on the clustered index , then it finds a row, locks it and modifies it. Perhaps it is because one of the columns being modified is a key column in the nonclustered index, it then has to move to the nonclustered index (which another update/select holds already) and update that index, too. This requires a second X key lock on the nonclustered index > Josh > [quoted text clipped - 56 lines] >> >> >Thank you, Gene. >Can you please explain why serializable you think contributes to deadlock. Because the easiest way SQL Server has of implementing serializable, is to turn the whole database single-threaded. It doesn't *quite* do that, but in serializable mode, if it has any doubt as to who is doing what, it locks tables (or other resources) in a way that is often premature, easily leading to deadlocks. J. > Josh > [quoted text clipped - 56 lines] >> >> >Thank you, Gene. Hi You don't say how the GUID is generated. If you use NEWSEQUENTIALID as a default there will be fewer page splits and fragmentation making the inserts quicker and should also help with updates. John >You don't say how the GUID is generated. If you use NEWSEQUENTIALID as a >default there will be fewer page splits and fragmentation making the inserts >quicker and should also help with updates. Especially if the logic does an insert, and then an update soon thereafter. Josh I have to disagree slightly with Josh on #2. Since the guid is basically a random value there is a lot of chances for page splits with the clustered index on a guid. By changing this to a nonclustered index you don't have all the data to deal with for splitting etc. You should also ensure you have a proper fill factor and try using sequential guids instead. But I definitely agree with the others that serializable is your biggest problem when it comes to blocking in general.  SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >>Hi folks >>I see plenty of deadlocks in my application. They do not happen inside of [quoted text clipped - 32 lines] > >>Thank you, Gene. >I have to disagree slightly with Josh on #2. Since the guid is basically a >random value there is a lot of chances for page splits with the clustered [quoted text clipped - 3 lines] >agree with the others that serializable is your biggest problem when it >comes to blocking in general. OK, but in my defense (!), I read it as asking about updates not inserts, I didn't ask if he was updating the PK (probably not), and if he makes something else the CK, we'd have to ask about the odds of that key causing page splits. And there might be some room to prepopulate varchar fields or something, if indeed the updates expanding records and causing page splits are what's causing deadlocks. Josh Josh, I wasn't concerned with the updates and yes you are right that is what he posted as an example. But I have to assume there are also inserts happening as well. Those would cause the splits and hinder the updates at the same time. I think we both in the same ballpark:). SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >>I have to disagree slightly with Josh on #2. Since the guid is basically >>a [quoted text clipped - 16 lines] > > Josh Thank you guys for respond. I am having problem to post from work pc. Anyway, I see deadlocks with inserts, updates which look exactly the same - different parms. I see deadlock on indexes - PK index which may be clustered or not and non.cl index. I was wondering if index pages follow table lock schema. It means that if row locks are allowed, does it mean that index pages do also allow row locks. And another question: If i prevent page locks and leave only row locks on indexes, will it help? > Josh, > [quoted text clipped - 23 lines] > > > > Josh Can you give us more information? How long does it take for one of these updates (or inserts) to run? How many rows are inserted or updated at a time? How many rows in the total table? Any other performance issues on the server? Josh >Thank you guys for respond. I am having problem to post from work pc. Anyway, >I see deadlocks with inserts, updates which look exactly the same - different [quoted text clipped - 33 lines] >> > >> > Josh Hi Josh Here are answers: > Can you give us more information? > > How long does it take for one of these updates (or inserts) to run? I am not sure about those which caused deadlock, but usually fast, nothing seems ubnormal. > How many rows are inserted or updated at a time? Usually 1 row. > How many rows in the total table? In one of them is 160k, in another instance, 2 processes were deadlocked over 2 tables -- > indexes. 120 rows in each. > Any other performance issues on the server? I see only Page life expectency is around 150. I know it's low. cashe hit ratio is 99.4, disks are fine. > Josh > [quoted text clipped - 35 lines] > >> > > >> > Josh > Hi folks > I see plenty of deadlocks in my application. They do not happen inside of [quoted text clipped - 12 lines] > > Thank you, Gene. 1. Yes. Basically both updates in serializable mode acquire shared locks, then try to promote them to exclusive ones. 2. No. 3. No. I described this scenario in a couple of articles: http://www.simple-talk.com/sql/t-sql-programming/close-those-loopholes-stress-te st-those-stored-procedures/ http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes-in-your-d atabase-testing/ You can play with the repro scenarios in the articles and see for yourself Hi All I looked at some deadlock scenarios. One which I can't explain is next: INSERT INTO "t1" ("guid","document","bin","filledOn") VALUES (@P1,@P2,@P3,@P4) INSERT INTO "t2" ("guid","bin","document","filledOn") VALUES (@P1,@P2,@P3,@P4) Yes, those are different tables. No foreign keys defined in any of them, no triggers. Each table has about 120 rows. Deadlock happened on non-cl. indexes belonging to 1st and 2nd table. Any clues? Gene. > > Hi folks > > I see plenty of deadlocks in my application. They do not happen inside of [quoted text clipped - 24 lines] > You can play with the repro scenarios in the articles and see for > yourself GUIDs are absolutely horrible for clustered indexes (and for other reasons too!). If you must use them, consider sequential guids. Serializable locking will present serious issues with concurrency. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Hi folks > I see plenty of deadlocks in my application. They do not happen inside of [quoted text clipped - 12 lines] > > Thank you, Gene. Hi Kevin They use merge replication so guid is nessesity. what is the sequential guid? I never heard of it. Gene. > GUIDs are absolutely horrible for clustered indexes (and for other reasons > too!). If you must use them, consider sequential guids. [quoted text clipped - 17 lines] > > > > Thank you, Gene. Did you try a web search?? I tried "sequential guid sql server" and got some very useful and informative hits. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Hi Kevin > [quoted text clipped - 29 lines] >> > >> > Thank you, Gene. Yes, i got it. Thank you. Does it really makes a difference if i query just 1 value? I undestand that guid values are random. But b-tree would not have problem to place and retreive it in/from right physical position, correct? Page splits are unavoidable but which way it's differ from any other insert into indexed table? Gene. > Did you try a web search?? I tried "sequential guid sql server" and got > some very useful and informative hits. [quoted text clipped - 32 lines] > >> > > >> > Thank you, Gene. You are correct in that ANY inserts into a table will cause splits when the page they are to go in is full. The problem with normal guids is that their values are spread throughout the entire range of existing values due to their random nature. Thus page fragmentation will occur on the entire table. Sequential guids at least simply cause splits at the tail of the table. This allows for easier/faster defrag evolutions among other things. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Yes, i got it. Thank you. Does it really makes a difference if i query > just 1 [quoted text clipped - 46 lines] >> >> > >> >> > Thank you, Gene. > Sequential guids at least simply cause splits at the tail of the table. And such a split isn't really a ... split. It is just linking a new page to the and of the linked list - nothing is moved. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > You are correct in that ANY inserts into a table will cause splits when the page they are to go in > is full. The problem with normal guids is that their values are spread throughout the entire [quoted text clipped - 46 lines] >>> >> > >>> >> > Thank you, Gene. Hmm. I thought if it was a clustered index that wasn't defined on something like identity PK it would still split that last page 50/50. Is this incorrect? SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net >> Sequential guids at least simply cause splits at the tail of the table. > [quoted text clipped - 61 lines] >>>> >> > >>>> >> > Thank you, Gene. >Hmm. I thought if it was a clustered index that wasn't defined on something >like identity PK it would still split that last page 50/50. Is this >incorrect? If it is at the "end" of the page, it just starts a new page for the row that would not fit. If it has to go in the middle the page can split. Roy Harvey Beacon Falls, CT I am not sure if anybody had any experience to work with serializable locks. It's not that one needed to have anyway. But what i see is totally weird. Different tables are getting deadlocked. These are tables which may have only 100rows, don't have any FK constraints or triggers. i simply can't relate these participants in deadlock event (many of them), but I trust errorlog output. What i read in bol regarding serializable mode is 5% of what i see. Gene. > Hi folks > I see plenty of deadlocks in my application. They do not happen inside of [quoted text clipped - 12 lines] > > Thank you, Gene. > I am not sure if anybody had any experience to work with serializable locks. > It's not that one needed to have anyway. [quoted text clipped - 23 lines] > > > Thank you, Gene. Actually this is not uncommon. Did you read the articles I posted links to? >I am not sure if anybody had any experience to work with serializable locks. >It's not that one needed to have anyway. [quoted text clipped - 4 lines] >errorlog output. >What i read in bol regarding serializable mode is 5% of what i see. Yes. My experience with serializable locks has been equally vague and unpleasant, was a while ago so I can't quote you details, probably was pre-2005 anyway, just remember that SQL Server is (secretly) lazy and to guarantee serializable, will sometimes (try to) single-thread the entire database. What you are seeing seems particularly bad, since it is only system resource (eg index page) contention, not user-entity contention that you could fix with different coding, that is causing the problem. It's not exactly a secret, but Microsoft is not proud of some of this, and thus leaves it out of BOL. Josh SQL Server is (secretly) lazy and > to guarantee serializable, will sometimes (try to) single-thread the > entire database. The definition of serializable isolation is that even read locks are held until the commit of the transaction, which means that any other transaction that attempts to access data locked by that transaction will be blocked. Further, range locks are used to ensure that no rows are inserted into the range of rows being locked. The net effect is a drastic reduction in concurrency with a corresponding increase in isolation. This means that if all of your data access is on common rows, the by-product may seem like "single-thread[ing] the entire database" -- but, it's not SQL Server being deceptive; it's the job of the server to guarantee the isolation level you request. This might help: http://blogs.msdn.com/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolat ion-level.aspx Thanks, Ryan Stonecipher Senior SDE Lead, SQL Server Engine >>I am not sure if anybody had any experience to work with serializable >>locks. [quoted text clipped - 22 lines] > > Josh Ryan, Thanks, your comments and that blog are helpful. Between read locks and "pessimistic concurrency", you can easily be surprised by the physical limits of even simple logical queries since a plan may do a scan and lock the entire table, based only on the statistics for a key! Given the cost in practice, I never have understood just when serializable isolation should be used (eg as the default) rather than read committed. I have to wonder if OP tried things under read committed, if he might have fewer (none!) deadlocks and otherwise be OK. Josh >SQL Server is (secretly) lazy and >> to guarantee serializable, will sometimes (try to) single-thread the [quoted text clipped - 44 lines] >> >> Josh Josh, Many people use serializable mode either for the wrong reasons (basically don't understand it) or unknowingly. Some drivers use serializable by default. Most operations don't need that level of locking and that is why SQL Server uses read committed as the default. But there are definitely times when you need this level of locking and if you do you have to account for it or you will wreak havoc in a heavily used multiuser app. One good example is when dealing with bank transactions. You certainly would not want one transaction to read or change any values in your account while another is making decisions based on the data it sees. This is why SQL Server supports 4 levels (6 with the newer snapshot stuff) of isolation as per the ansi specs. Then the developer can pick and choose the correct one for their needs. But too often people choose serializable without realizing how it actually works. The default in Oracle is to use the behavior in which readers don't block writers and visa versa. This is great for the developers since they almost never get blocked or block others and is the equivalent to one of the newer snapshot levels in SQL Server. But the down side is that many of the developers don't realize they are potentially and often reading old data when they make their decisions. So in that same example above if they were doing a banking app you can end up with accounts that are not in sync very easily. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Ryan, > [quoted text clipped - 64 lines] >>> >>> Josh > Ryan, > [quoted text clipped - 65 lines] > > >> Josh Also serializable may use significantly more CPU. http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/07/12/cpu-overhead-for -higher-isolation-levels.aspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.