DB2 Select statements causing deadlocks?



IBM's flagship relational database management system

DB2 Select statements causing deadlocks?

Postby paulmac1968 » Wed May 01, 2013 8:12 pm

In a single thread, I have two queries running consecutively using the same Websphere pool connection. They are both SELECTs and both use some of the same tables (not my design, it was inherited). Is it possible for one of these selects to cause the other to deadlock?
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: DB2 Select statements causing deadlocks?

Postby dick scherrer » Wed May 01, 2013 8:47 pm

Hello,

Yes, it is possible if they are running concurrently and are locking rows.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DB2 Select statements causing deadlocks?

Postby paulmac1968 » Wed May 01, 2013 8:56 pm

What's the best way to mitigate the risk? Could we change the Isolation level? It's currently at Read Stability. Would we be better off setting it to Cursor Stability?
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: DB2 Select statements causing deadlocks?

Postby dick scherrer » Wed May 01, 2013 9:05 pm

Hello,

The only way a deadlock (not to be confused with a timeout) can occur is when 2 processes try to lock the same rows in a different order. The way to prevent this is to make sure the processes access the tables in the same order.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DB2 Select statements causing deadlocks?

Postby paulmac1968 » Wed May 01, 2013 11:45 pm

I'm not sure what you mean. Do you have a simple example?
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: DB2 Select statements causing deadlocks?

Postby Akatsukami » Thu May 02, 2013 12:03 am

paulmac1968 wrote:I'm not sure what you mean. Do you have a simple example?

Process P1 locks resource R1, uses it, and unlocks it, then R2; process P2 locks R2, then R1.

They attempt to do this:
P1       P2
===      ===
Lock R1
         Lock R2
Lock R2 (can't, because P2 has it locked)
         Lock R1 (can't, because P1 has it locked)

Deadlock, right?

Now consider changes to P2 so that it uses the resources in the same order as P1:
P1         P2
===        ===
Lock R1
           Lock R1 (Can't, because P1 has it locked)
Unlock R1
Lock R2
           Lock R1 (Now works, because P1 no longer has it locked)
Unlock R2
EOJ        Lock R2
           Unlock R2
           EOJ

No dead lock, right?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: DB2 Select statements causing deadlocks?

Postby paulmac1968 » Thu May 02, 2013 1:03 am

Crystal clear. Thx. I've been running for a long time on the assumption that locks prevented actions that manipulated data. Inserts, updates, deletes. That reads were okay.
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: DB2 Select statements causing deadlocks?

Postby dick scherrer » Thu May 02, 2013 1:35 am

Hello,

"Some" reads may be ok. . . It is the reads that lock rows that cause the problem.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post