Advertisement

04.04.2008 at 07:50AM PDT, ID: 23296219
[x]
Attachment Details

why is this a deadlock?

Asked by gdemaria in MS SQL Server

Tags: sql server 2005, deadlock

Hello!
I have a lot of troubles with deadlocks.   Recently, I used sql tools to get each query involved in a deadlock to try to really understand and resolve it.    I simplied the code a bit by removing some additional columns in the update/insert statements and changed the table names to more clearly show that I have two tables, a "parent table" and a "child table"

I really don't understand why these sql statements would deadlock with each other.  Notice that the last one is a SELECT statement which doesn't involve the same records as the other statements.  

If it can deadlock on something like this, how could I ever avoid deadlock??

Note that in the code, I indicate which process ID it is, the code for that process and which other process it is waiting for.

Thanks for any insights!!!
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:

------------------------------
I am process 78 waiting on process 60
 
   update ParentTable 
    SET firstname = 'Mary'
      , lastName = 'Lowe'
    where parentTable_ID = 28
 
------------------------------
I am process 60 waiting on process 53
 
    update ParentTable 
     SET processed_yn = 'N'
    where parentTable_ID = 28	
    ;
    update childTable 
      SET isRunSuccessful = 0
	   ,  runErrorText = NULL
    where parentTable_ID = 28
 
------------------------------
I am process 53 waiting on process 65
	
    update ParentTable 
     SET processed_yn = 'N'
    where parentTable_ID = 27	
    ;
    update childTable 
      SET isRunSuccessful = 0
	   ,  runErrorText = NULL
    where parentTable_ID = 27
 
------------------------------
I am process 65 
	
  select childTable_ID
   from childTable
  where parentTable_ID = 26
  and   product_id = 1627
 
Keywords: why is this a deadlock?
Title
1 deadlocks
2 DEADLOCK
3 Is this a deadlock ?
4 DEADLOCK
5 ORA-000060 Deadlock
6 Why Deadlock?
 
Loading Advertisement...
 
[+][-]04.04.2008 at 08:06AM PDT, ID: 21282518

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: MS SQL Server
Tags: sql server 2005, deadlock
Sign Up Now!
Solution Provided By: momi_sabag
Participating Experts: 4
Solution Grade: A
 
 
[+][-]04.04.2008 at 08:14AM PDT, ID: 21282595

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.04.2008 at 08:19AM PDT, ID: 21282650

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.04.2008 at 08:27AM PDT, ID: 21282739

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.04.2008 at 08:30AM PDT, ID: 21282776

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.06.2008 at 12:35PM PDT, ID: 21292866

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.06.2008 at 12:42PM PDT, ID: 21292888

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]04.06.2008 at 02:59PM PDT, ID: 21293293

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_EXPERT_20070906