April 21, 2014 at 9:36 pm
Hi,
We were able to diagnose deadlock issue in our development machine. There are two tables A and B. B is a master table say an employee table and A stores Password History of all the employees i.e. B
We have a stp in which A is referred first.(Select query, and based on results of select query Insert or update stmt). Then couple of fields from table B are updated. On Update of B, trigger of B is called in which one column of A is referred. This happens within an explicit transaction. This stp is called when an employees password is reset.
Now if user resets password of more than one employee simultaneously, some times deadlock issue arises. We were able to simulate this every time by putting delay in the stp. However even by putting this delay, the deadlock is not getting simulated in other machines (QA's machine)
Also we tried and restored db backup of QA's m/c but were unable to simulate it where as the QA was able to simulate by restoring our database.
Kindly please reply on what properties to set in order get the above problem solved.
Thanks & Regards,
Saumik Vora
April 22, 2014 at 1:35 am
Is read committed snapshot isolation enabled on one database and not the other?
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases
Also, when posting, please use full words rather than acronyms like "stp" (stored procedure? step? stop?) and "m/c" -as this makes it harder to understand the question.
April 22, 2014 at 4:06 am
Any deadlock trace is on? Any information related to deadlock in sql log?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 22, 2014 at 4:18 am
Deadlocks are fundamentally a performance issue. So, are the other databases the same size as the one that's getting the issue? Are the same indexes in place? Are the statistics the same? Have the statistics been updated in the same way? Any of these issues will change the behavior. That's before we get to things like the connection isolation level (such as using read committed snapshot, a great idea by the way), ANSI settings, cost threshold for parallelism, max degree of parallelism or some of the more obscure things that might affect execution plans and therefore performance. It's likely to be something from the list above.
As far as capturing deadlocks goes, the system_health extended event session is running by default in all servers 2008 or better and automatically captures deadlock graphs. You don't have to enable trace flags or specifically do anything to capture deadlock information any more.
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply