IBM Mainframe Forum

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?

Hello,

Yes, it is possible if they are running concurrently and are locking rows.

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?

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.

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

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:

Deadlock, right?

Now consider changes to P2 so that it uses the resources in the same order as P1:

No dead lock, right?

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.

Hello,

"Some" reads may be ok. . . It is the reads that lock rows that cause the problem.