Anyone have ideas how to solve deadlock problems
-
I already posted on the ms access forum to see if an open view would contribute, but I will go more in depth here.
We have a db that has new files coming in continuously. In addition when certain status levels change, serveral triggers fire to update supporting data. In addition, an interface was just setup to update the supporting data when its changed on external systems. I think some of my problems occur on table locking here. There's a service that polls the files table every 2 seconds or so as well.
I've read about the with nolock and rowlock etc. features, but I don't really want to start that arduous task until I get some recommendations.
Thank you in advance!
KM
-
The first thing is to figure out what is deadlocking what... my recommendation would be to lookup [Deadlocks,troubleshooting] and select [Troubleshooting Deadlocks] in Books Online to start... there, you will find out how to setup trace flag 1204 and how to read the results from the server log file.
I feel for you... when I first reported to my current job, they had an average of 640 deadlocks per day with spikes to 4000 per day about once a week. It took months and a hefty amount of code changes to do some of the things you say (NoLock, etc) but, when we finally found and rewrote the single proc that was the source of 99.9999% of the mess, deadlocks dropped to about 6 per week. It's takes time and patience...
The use of WITH (NOLOCK) helps the deadlock situation but only within a transaction...
Once you have trace flag 1204 operational, look for procs associated with the deadlocked tables that do UPDATE/SELECT within the same transaction (BEGIN TRAN/COMMIT). Chances are, that's where you'll find the culprit(s).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Jeff,
Sounds like you had heck of a time cleaning up.
I started putting NOLOCK and ROWLOCK where I thought applicable. So far no deadlocks this morning. I am still waiting for the results from the trace flag from our Senior DBA so we can see how deep the rabbit hole goes. Can you give me some advice on syntax?
are the bellow statements the best approach?
UPDATE x WITH (ROWLOCK)
SET ...
FROM x WITH (ROWLOCK)
JOIN y WITH (NO LOCK)
JOIN z WITH (NO LOCK)
INSERT INTO x WITH (ROWLOCK)
(...)
SELECT ...
FROM y WITH (NOLOCK)
JOIN z WITH (NOLOCK)
-
I've found that "UPDATE x WITH (ROWLOCK)" does very little in containing SQL Server's escalation of locks to rows instead of pages or extents. It sure doesn't hurt to have it there, though.
So far as "INSERT INTO x WITH (ROWLOCK)" goes, same thing...
The best thing for both is to make sure that you don't update the primary key or insert lots of "out of order" rows in the presence of a clustered primary key. If it's a highly transactional table with lot's of inserts or updates, it's best to use a non-clustered primary key with a fill factor of 70 or 80 (80 is the default on most installations). The exception to THAT rule is if the Primary Key is on an auto-numbering IDENTITY column.
The NOLOCKs are a very good thing and you've appropriately NOT used them on the object of the update or insert.
A couple of folks have suggested that you use a table alias in the UPDATE... like this (borrowing from you fine example)....
UPDATE x WITH (ROWLOCK)
SET ...
FROM TableX x WITH (ROWLOCK)
JOIN TableY y WITH (NO LOCK)
JOIN TableZ z WITH (NO LOCK)
...where "x" is the table alias for "TableX". I normally try to avoid that so I can more effectively search SysComments for procs that update given tables. Since I normally do avoid that, I haven't checked execution plans nor SysProcesses to see if there is an anti-deadlock benefit or a performance benefit. Simple duration testing seems to indicate no benefit although I have seen it where a derived table (why does everything think THAT's such a bad thing?) can be avoided which does simplify the code, in some cases.
Just so you know, you may run into someone that suggests the use of "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" which effectively and correctly is the equivelent to setting NOLOCK on the correct tables as you have in your examples. In a word (or three), DON'T DO IT! I can't tell you when you'll run into it but you will run into an "Isolation Level Conflict" eventually. This is particularly true in writable views. Make your Developers consciously and correctly apply WITH (NOLOCK) to each table used (avoiding, of course, objects of updates, inserts, and deletes when joined).
If you just started adding WITH (NOLOCK) where you thought appropriate and you've killed the deadlock problem, all I can say is will you buy a lotto ticket for me?
Folks aren't usually that lucky when it comes to deadlocks. In my case, the Developers had written some God awful RBAR (Row By Agonizing Row) code that, in many cases, consisted of as many as 28 stored procedures called at as many as 10 different levels within a single main stored procedure. Because of their lunacy with transactions, some of the stored procedures actually deadlocked themselves! 
Now, THAT's a trick!The really big problems occur with things like the following...
BEGIN TRANSACTION
UPDATE sometable
FROM sometable x,
JOIN someothertable y
ON somecondition
SELECT wuggu-wugga
FROM sometable
IF somecondition
ROLLBACK
ELSE
COMMIT
... more deadlocks than you can shake a stick at
because, sure as shootin', some genius will write the SELECT first followed by an update somewhere. Adding WITH (NOLOCK) in the right spots will help, but unless you absolutely gotta have a transaction here (most of the time, you don't), don't use one. It's better to use error checking to detect if the update actually occurred or not and, if you look for it, can many times be used as a substitute for a transactional pair.You might want to lookup SET XACT_ABORT ON... if you don't see the need for it, there's a pretty good chance that you also don't need a transactional pair. People just go too crazy with trully unnecessary transactions. When transactions are necessary, make sure they are just as short as you can possibly make them... for example... this is what I call stupid...
BEGIN TRANSACTION
SELECT something
UPDATE something
EXEC aproc
SELECT something else with 8 table joins
UPDATE something
EXEC another proc that's 3 or 4 levels deep
SELECT a couple more times
etc, etc,
COMMIT
First, the rule of thumb should be "IF THERE IS NO ROLLBACK CODE, YOU DON'T NEED THE TRANSACTION". The second rule should be "IF SET XACT_ABORT ON IS NOT APPROPRIATE FOR THE TRANSACTION, QUESTION IF THE TRANSACTION IS WORTH IT." Besides, it's too long.
Slightly different focus... If someone was foolish enough to build an Oracle-like "sequence table" (for next ID's and the like), first, find them and shoot them out of a cannon butt first into a stone wall.
Then, write back and I'll show you how to fix the bloody thing. Most of the deadlocks I found revolved around how the "NextID" sequence table that a 3rd party vendor built was being updated by the vendor code and our code. The fix is actually pretty simple but I'd need to see your proc and table schema for the update to fix it. It's actually a very common problem to have one of these bloody tables because lot's of folks work in Oracle and other RDBMS's that don't have auto-numbering columns and don't realize that SQL Server has that ability. Another reason might be that they want an alpha-numeric key and they don't realize how to make a surrogate column using an auto-numbering column to get the same thing without using a sequence table.Sorry about the soap-box and all... it's just that if you have a database where the developers (can't really call them that, sometimes) or 3rd party vendors were allowed to run rampant & there were no code reviews, and now you have to resolve the deadlocks that's causing, I really do feel for you. And, get this... the DBA's are partially at fault for letting that junk anywhere near their server. I feel for them, too, because they're damned if they do and damned if they don't. Sooner or later, the whole company pays for bad code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This has been one of the most informative threads I have come across wrt locking.
How bout setting the right Tran Isolation levels / Transation? Should'nt that help in managing the locks as well?
What I hear I forget, what I see I remember, what I do I understand -
Jeff,
Just when you thought you knew how to develop a database, this is thrown at you. I wonder why this is not more common knowledge. If I were to teach a class, it would be an entire lecture right after explaning what a join was.
I guess I should go out and buy a lotto ticket, because we have yet to experience a deadlock since I began the lock protection work. Although, I was already headed in the correct direction, I cannot thank you enough for explaining, in-depth, what to look for. I have plenty of work ahead of me tweaking and optimizing.
Cheers!
KM
-
You bet, Kevin. Thank you and Niles for the feedback...
Kevin, could you tell us the actions you took? Bet, lot's of folks would like to hear what the lickiest guy in the world has to say about deadlocks.
Niles, read the section of my post on why you shouldn't set the transaction isolation level.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I should have waited before I spoke, but I think I alieviated the problem in one place and it is still in another.
All I have done so far is the NOLOCK and ROWLOCK. I am about to do the trace on 1205 myself.
could this also be a product of performance (either hardware, system, or sql server setup)?
-
Strangely enough, you'll usually find a direct correlation between poor performance code and deadlocks and, with the exception of not allocating enough memory, hardware is not usually the problem nor is the system or the SQL Server setup... you actually have to go pretty far out of your way to screw those things up (although I've seen some pretty far out people go really far out of the way).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
IMHO in case of bad performance due to hardware, deadlocks can be greater nuisance because every action requires more time - but the reason of deadlocks is still bad code. Problems with hardware generally cause everything being slow, sp_who can show many blocked processes, but blocking and deadlocks are not the same.
That being said, I think that checking the hardware and SQLS configuration isn't a bad idea, although it most probably won't solve the deadlocks. It is always good to know possible weak points and bottlenecks before they start to cause serious problems.
-
Jeff M: >If someone was foolish enough to build an Oracle-like "sequence table" (for next ID's and the like), first, find them and shoot them out of a cannon butt first into a stone wall.
Unless they need to do multiple-row inserts with known key values. Or would a nice little sp that inserts each record and returns scope_identity() be more to your liking? Perhaps using identity_insert with DBCC?
Obviously, you wouldn't want to involve such a hotspot table in a long transaction, but a simple
update t set @id = t.id + 1, t.id = @id from table t with(tablockx)
is not going to cause contention on a 1-row table. The only possible problem is 'lost' sequences which have been reserved but for one reason or another not used. If that is not an issue, then under the circumstances described, a 'sequence' table is an excellent idea.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Ermmm, the infamous Oracle like sequence table seems to be an integral part of our architecture. It was already in place before I took over, and changing it now will be a hugh headache.
In its defense, we haven’t faced any bottle necks, yet, but i believe its only a matter of time before the workload increases and we hit a brick wall.
What I hear I forget, what I see I remember, what I do I understand -
It's only a problem if access to this table is done in a multiple-statement transaction, or if for some bizarre reason you used a massive query to read and write from the table. Even a modest transaction which consists of reserving keys then inserting them shouldn't have a great impact on performance. The wait to do the the insert would be more significant than the wait to get the keys. (Of course blocking could be reduced if you don't have a clustered index on an IDENTITY column....)
So if you're happy to reserve keys that might not be used (i.e. the process of reserving them cannot be rolled back), there need be no problem with a sequence table. See the additions to my post above - you posted yours while I was adding to mine. If you have multiple-row inserts yet the calling application needs to know what key values the rows inserted have, and especially if the multi-row insert forms a single logical unit of work, you will find significant problems with the IDENTITY property. Luckily the headache involved is too massive (really?), so you won't have to go down that route.
BTW, you should be very sure of yourself before using NOLOCK or READ UNCOMMITTED in a production system. You are effectively saying that you don't care if the data is corrupt. If NOLOCK is to be of any use, it is by forcing SQL Server to give you data that is in the middle of being updated. Is that OK?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Because all inserts have to occur at the 'same' point i.e. at the end.
Of course there are about 587 other considerations which also feed into the decision process...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply