exclusive row locks on the same table

8.1.6

I had three programs run into each other today. They all locked rows in the same table with a ROW EXCLUSIVE lock when updating a number of rows. Specifically, the first program uses one or two bind variables in each of three different UPDATE statements. It needed to run each statement >100,000 times.

The second program (and third) came in (no binds) and tried running an UPDATE statement on one of the three tables. They CONNECTed between 100 and 200 times (each UPDATE having its own process). (Normally, these happen far enough apart not be run at the same time.)

There were three things that I had a hard time with.

1. Figuring out what the bind variables equaled. V$SQLArea showed bind variables, but not their values. How do I find out the current value of a bind variable?
2. I killed the second two processes so they wouldn't interfere with the first process. Yet, after I killed them, the first process didn't continue. No other locks were being held. The only locks were three ROW EXCLUSIVE locks, on three different tables all held by this one program. Is there a way I could have diagnosed why it didn't continue when I removed all other locks.
3. When two separate processes hold ROW EXCLUSIVE locks on the same table, why can they not both continue. Or are they continuing but just at a slow pace?

Brian Received on Wed Jan 02 2002 - 12:41:46 CST