March 26, 2008 at 5:08 am
Hello,
I have a situation where a very large SP is being used to handle the business logic for updating numerous tables (working with a WinForms app). If 2 end-users use the same App form at the same time often the SP causes a Deadlock and part of the SP process is rolled back.
Only part of it is rolled back because I can't wrap the whole SP in a transaction because it can take 5-10 minutes to run and the tables it uses need to be accessible to other end-users during that time and it would just cause even more deadlocking.
I can't figure out how to make SQL Server wait longer before throwing a deadlock error...so my question is this: Is there some way I can check and see if a SP is currently running by another user so that I can display a message in my Windows app that states that the DB is currently busy, wait a few minutes and try again?
Or is there anything else I can do to prevent this kind of deadlocking? I'm tearing my hair out (and I don't have much left!)
I know anyone reading this is thinking WTF, but this is an inherited app and I don't have complete control over re-writing major chunks of it.
Thanks, George
(and yes, this is related to the post I submitted yesterday!)
March 26, 2008 at 6:16 am
Hi,
what about this thread: http://www.sqlservercentral.com/Forums/Topic114165-8-1.aspx
[font="Verdana"]CU
tosc[/font]
March 26, 2008 at 6:32 am
Torsten, thanks for the excellent link.
I guess is explained my problem poorly - what I really want to know is how can you run a SP with multiple instances without getting a deadlock?
Here's the setup:
A main SP calls a sub SP. The sub SP is wrapped in a Transaction because it's atomic and because I incorporated error handling into it. So if a statement in the sub SP throws an error, it is rolled back. This sub SP is often called directly by Window app. forms.
However, when I did this, I didn't realize that other SP's also call the sub SP - some call it iteratively in loops. So when 2 or more of the main SP's are running, they end up deadlocking on the sub SP because it's in a transaction.
The sub SP transaction level is the default (READ COMMITTED)...for error handling and rolling back a transaction, would a lower isolation level be better for avoiding deadlocks?
There has to be a way to have SP's in transactions and yet still allow multiple calls...isn't there?
Thanks, George
March 26, 2008 at 6:37 am
hi
maybe you could use a semaphore extended with a datetime value which you use to age the semaphore (if it shows the sp is running for x time and you just know that it cannot run for that long:
- the process might have been deadlocked and you can reset the semaphore...
- or if not.... okay, i admit, that's not great as you would still need to check if the sp is running.....)
March 26, 2008 at 7:00 am
dragos_sv,
I'm not familiar with semaphores.
However, I thought of 2 similar ideas. The link Torsten supplied had code for a function that can check if an SP is running or not. So I could either:
1. Make a call to the DB with the function from the Windows Form before executing my main SP, and then only exec it if it's not already running, or
2. Use the function in the main SP when calling the sub SP...if the sub SP is already running, make the main SP wait until it's free again.
I like #2 better..but what's the best way to get an SP to "wait"? A loop with a counter of some sort?
March 26, 2008 at 7:09 am
George Heinrich (3/26/2008)
There has to be a way to have SP's in transactions and yet still allow multiple calls...isn't there?
Hey George,
Looks like this is related to the problem you posted about the other day. It sounds like you really might have to convince the boss that this is worth a re-design - either that or you won't have much hair left!:hehe:
It looks like the deadlocking problem is happening outside of the nested sp, which isn't controlled by an explicit transaction. As you pointed out, this is because the main sp takes 5 minutes or so to run and you didn't want to lock resources for that long.
Unfortunately, there's nothing to stop you from calling an sp multiple times, except in this situation that is leading to deadlocks. The only way around this is to control your transactions (which locks resources as you know) or to re-design.
The only other thing I can think of is to create new table. At the start of the main sp, you check to see if there's a record in the table (make sure you take out a updlock, holdlock during the select). If there is a record in there then you know that the procedure is running and you return an error message. If there is no record insert a record into this table and when it finishes executing, you delete that record.
begin tran
if exists(select * from some_table with (updlock, holdlock))
begin
raiserror('Stored proc is running, please try later...',16,1)
rollback tran
return
end
else
begin
insert into some_table values('current status:running')
end
commit tran
--stored procedure code
delete some_table --remove record for some_table
To be honest that's pretty ugly and I wouldn't advise you go down that route. You'll have to be extremely careful with your error handling to make sure that, no matter what, you always delete the record from the table at the end of execution.
There's probably a better solution out there but I can't think straight right now (had a late night last night :(). Still, I think the best option might be to seriously think about re-designing. I know you don't want to hear that but....
March 26, 2008 at 7:10 am
George Heinrich (3/26/2008)
Torsten, thanks for the excellent link.I guess is explained my problem poorly - what I really want to know is how can you run a SP with multiple instances without getting a deadlock?
Here's the setup:
A main SP calls a sub SP. The sub SP is wrapped in a Transaction because it's atomic and because I incorporated error handling into it. So if a statement in the sub SP throws an error, it is rolled back. This sub SP is often called directly by Window app. forms.
However, when I did this, I didn't realize that other SP's also call the sub SP - some call it iteratively in loops. So when 2 or more of the main SP's are running, they end up deadlocking on the sub SP because it's in a transaction.
The sub SP transaction level is the default (READ COMMITTED)...for error handling and rolling back a transaction, would a lower isolation level be better for avoiding deadlocks?
There has to be a way to have SP's in transactions and yet still allow multiple calls...isn't there?
Thanks, George
The issue is not really the stored procedure or the transaction, but the type(s) of lock(s) being taken by the statement(s) executed and the amount of data being updated in the stored procedure. I would suggest looking at the locks and seeing if indexing get help increase the granularity of the locks. Also look at the size of the transaction, is there a way to break that into smaller, faster chunks that will take more granular locks and hold them for a shorter period of time. You should encounter the same issue even without having the sub-SP wrapped in it's own transaction because it is still going to take the same locks.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 26, 2008 at 7:19 am
George Heinrich (3/26/2008)
what I really want to know is how can you run a SP with multiple instances without getting a deadlock?
There is... unfortunately, it requires a rewrite of the offending code to meet the "rules" for avoiding deadlocks. The main rule is to keep transactions as short as possible. If the SP you're trying to execute that has the transaction in it is called by another routine (with loops as you said) and that other routine also has a transaction declared, then you greatly increase the chance of a deadlock.
Books Online has some excellent tips for how to avoid deadlocks, but nothing will help code that simply needs a rewrite.
There is a way to cheat on this a bit... look in Books Online for "deadlocks, customizing lock time-out". It explains a bit about error message 1222 and customizing the lock time out.
It sounds like the sprocs were written pretty much in a RBAR fashion which usually causes some form of "Death by SQL". The 1222 method will buy you some time but the real fact is that sprocs like these will need to be rewritten to do things correctly to avoid deadlocks... a set based approach usually helps. Again, Books Online has some tips for avoiding deadlocks "deadlocks, avoiding", but they say up front that "Although deadlocks cannot be avoided completely" which I totally disagree with because I've done it. Some of it was following the rules... some of it was making up my own like not using nested transactions and the like.
I feel for ya... I was hired at my current company to figure out why the code they had was causing 640 deadlocks per day with spikes to 4,000 per day. It took me a couple of months of some serious analysis to figure out all the things I needed to do. I finally found the "magic bullet" for the system (very careful/tricky rewrite of a "Get NextID" sproc on a sequence table) that brought the number of deadlocks down to about 15 a week and we've made many other changes to get the down to 0 deadlocks for a 10 day period... but it all involved rewritting code properly... some were relatively simple mods (shorten certain transactions, remove nested transactions, etc) and some where full blown rewrites to doing things in a setbased fashion.
The really cool part about the major rewrites is watching some 8 hour jobs drop to 10-30 minute jobs, watch some hour long jobs run in a minute, and one 24 hour job do 50% more work and complete in 15 minutes instead of 24 hours.
Sorry I can't identify a "Magic Bullet" for your system... recovery from a poorly written set of procs (3rd party or not) usually takes a lot more time than it did to write the crap code in the first place.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2008 at 7:29 am
An ugly way to find out is to use a "locked" table. Again - at this point, we're piling ugly code on top of ugly code, so this may not help at all.
The idea (by the way this needs to happen outside of any transaction, or you just move your deadlock to another table):
- create a table, called Locked (or pick your own pet name for it).
- at the top of the SP, check the count of rows in the table. If the count is 0, then insert a row (you're now "locking"). Ultimately it should not matter WHAT you're putting in it, just the fact that there's a row in it.
- run your transaction, doing whatever.
- after the end of the transaction, truncate the Locked table (removing all rows).
All said and done - this might buy you some time, although now you're going to have to spend time rewrite some portion of the front-end to handle the "someone else is doing something at this time" message.
Jeff - George has inherited one of your long-term Nemeses as a problem ("manual" identity fields, and yes, used RBAR).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 26, 2008 at 8:07 am
Matt Miller (3/26/2008)
Jeff - George has inherited one of your long-term Nemeses as a problem ("manual" identity fields, and yes, used RBAR).
How do you know that, Matt? Shoot, that's not such a hard fix... but where did you see that in this thread?
George, if what Matt says is true, then I need to see some code where that happens. If it uses a sequence table, then I also need to see both the CREATE TABLE statement and the data it contains to help you fix it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2008 at 8:15 am
Jeff Moden (3/26/2008)
Matt Miller (3/26/2008)
Jeff - George has inherited one of your long-term Nemeses as a problem ("manual" identity fields, and yes, used RBAR).How do you know that, Matt? Shoot, that's not such a hard fix... but where did you see that in this thread?
George, if what Matt says is true, then I need to see some code where that happens. If it uses a sequence table, then I also need to see both the CREATE TABLE statement and the data it contains to help you fix it.
It's from the OTHER thread he was mentioning.... He did say they were related....
Keep in mind that the identity has to be varchar and formatted too...
http://www.sqlservercentral.com/Forums/Topic473959-8-2.aspx#bm474187
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 26, 2008 at 8:16 am
Jeff Moden (3/26/2008)
Matt Miller (3/26/2008)
Jeff - George has inherited one of your long-term Nemeses as a problem ("manual" identity fields, and yes, used RBAR).How do you know that, Matt? Shoot, that's not such a hard fix... but where did you see that in this thread?
George, if what Matt says is true, then I need to see some code where that happens. If it uses a sequence table, then I also need to see both the CREATE TABLE statement and the data it contains to help you fix it.
Jeff, check out this post: http://www.sqlservercentral.com/Forums/Topic473959-8-2.aspx#bm474187
It started with a manual identity field problem that then became a deadlocking problem. Not sure how related the manual identity problem is to the deadlocking though because as you read through the post you'll notice that George identifed the deadlocking was happening with two completely different statements.
That said, I'm not entirely sure how related this post is to the last one - but it looks like they are somewhat related. The only thing that's certain is that it's looking like George has a bit of a mess on his hands :).
March 26, 2008 at 8:37 am
Wow...that's a lot of info. I'm starting to think I don't have the experience/know-how to rewrite this. For one, I probably would have made the same mistake by using nested SPs.
Jeff, the whole thing started when users were getting PK conflict errors (the error handling is non-existent).
So I looked into the SP that was doing it...one of the main tables in this system uses a varchar(15) for the unique identifier field and unfortunately, there really isn't a natural unique key. So the previous developers did a lot of:
1. grab highest ID field in table
2. Insert Highest ID + 1 into table
Once I jury rigged that piece of code to minimize the occurence of the PK conflict...the SP started throwing Deadlock errors.
With the help of some of the very helpful people on this forum, I tracked it down to an update statement in one of the nested SPs that the main SP was calling.
The nested SP is an atomic set of inserts and updates that need to be run together...before it wasn't running in a transaction so I added one. (At the time I added the transaction I didn't know the SP was being called by another SP).
So now I have a nested SP that keeps giving me deadlock errors and, more importantly, I'm not experienced enough to know how to rewrite it in a way that will prevent deadlocking. (Evidently, neither were the guys who developed the system). I'm more used to calling a single SP that doesn't call sub SPs...but on one hand it seems to make sense to encapsulate related code into a sub SP, whereas on the other hand it seems that this is what's giving me deadlocking issues.
The main SP is huge and the sub SP has 8-10 insert/update/delete statements in it.
I have since removed the transaction from the sub SP, but it still periodically gives me deadlocking errors. I'm not sure where to go from here...are there any good sources to read up on how to properly program SPs so that they don't do this kind of thing?
March 26, 2008 at 8:41 am
You know, actually this is what I love about SQL programming...it's always a challenge.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply