May 8, 2015 at 3:34 am
Hello,
We sometimes have issues with a process that locks on itselfs, and displays LCK_SCH_M or similar as waittype and blocked by its own spid.
The waitresource points to object id of a temporary table that is created in the procedure that is being executed.
This procedure also updates some key tables and thus locking all other processes from accessing the key tables.
If KILLED, this process never rolls back and we have so far solved it by restarting SQL Server.
The query text are displaying call to the procedure, so don't know on which "sql" it's stuck on exactly.
I cannot easily post all the code because it's a lot of it, but basicly it handles reservation of products:
1. Put all products and quantities needed to be reserved into a temp table
2. Call a procedure that fetches current saldo of products from real tables.
This procedure does following:
a) Starts transaction
b) Update temptable from real tables using WITH(HOLDLOCK, XLOCK), so noone else steals saldo
c) Decreases previously reserved saldo from key tables
d) Adds newly reserved saldo by updating key tables
e) commits transaction
3. Calls another procedure that re-reserves newly "opened" saldo to real orders
Other processes are stuck on the reading / writing to key tables and showing above's process spid as block
Does anyone know of any remedys / suggestions / anything? 🙂
May 8, 2015 at 6:17 am
Based on just general outline, it's hard to make substantial suggestions.
At it's core, a deadlock is about performance. If a transaction completes extremely fast, the chances of it causing or being deadlocked are reduced. So, first off, tune the queries involved. Since this is deadlocking itself, it could be due to parallelism. Check the execution plans to see if they're going parallel. Possibly change your cost threshold for parallelism if it's still at the default value of 5. It shouldn't be left at that value.
Other than that, I'm not sure what else to suggest.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2015 at 11:26 am
Yeah unfortunately these routines are complex and in abundance :/
But, do you know how to resolve "self-deadlocks" so they don't lock everything else out?
May 21, 2015 at 12:18 pm
A deadlock won't lock people out. It'll error out. Do you mean you have blocking?
And as for resolving deadlocks, it's usually a series of things. You have to get the code right so that the access order across processes is the same. You have to try to get each process to operate as fast as possible. You can introduce things like read committed snapshot to reduce contention during updates.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2015 at 5:31 am
Hi!
I have a process that deadlocks on itself. It also locks some important tables, so other normal processes are blocked when reading these tables.
But I don't know how to "undeadlock" that process, since killing it just leaves it in limbo state and doesn't actually kill it.
So far we had to re-start server to resolve it, so i wonder if it's possible to kill this process somehow without restart. Of course it's not a solution for the long run, but it's good to know (if possible at all :))
May 22, 2015 at 5:56 am
For the process you have to be blocking itself, you're probably looking at a parallel plan on one or more of the queries involved. Here's what I'd do. First, capture all the statements for the stored procedure using Extended Events. You want the events sp_statement_completed and sp_statement_starting. That way you can tell what each and ever statement is doing and you'll know for sure which statement is causing the issues. When you set up the Extended Events make sure you include causality tracking so that it orders the events for you. You'll also want to put in some very stringent filtering by adding predicates. I'd suggest filtering first by the object id for the procedure in question. That should ensure that you only collect data for when this process runs. This will tell you exactly where the problem is occurring. After that, it's a question of determining what's wrong. Probably, as I said, parallelism is causing it to block itself as it attempts to modify stuff that it's also attempting to read. The best thing to do, first, make sure your cost threshold for parallelism is set to a realistic number. The default value of 5 is bad. Change it to something between 30 and 50. Then, tune the queries. All of them. The longer any given process takes to run, the more blocking it does. Finally, examine the logic. Could you do smaller transactions? It sounds like this transaction is very large. Smaller is better. Break it down into tinier chunks where you can. But, make each of the chunks run as fast as possible. Examine the query plans, ensure that you're using your indexes well, that the code doesn't have any standard issues, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply