Re: exclusive row locks on the same table

On 3 Jan 2002 07:11:54 -0800, mark.powell_at_eds.com (Mark D Powell) wrote:

>SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote in message news:<3c335112.3816091359_at_news.alt.net>...
>> 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
>
>Multiple sessions can hold row level exclusive locks without impacting
>performance as long as the sessions do not go after 1- the same rows
>and 2- adequate transaction work areas exist in the block to handle
>all sessions that want to update a row in the block. Indexes update
>activity required by the table update activity is likely to cause more
>problems than the table update activity itself.
>
>Unless your sessions were lockwaited there was no need to terminate
>them. If they were lockwaited on each other then you may have gotten
>into a deadlock situation. If a deadlock was detected by Oracle then
>Oracle would have chosen one of the sessions to be rolled back. Also
>once you killed a session rollback would have been required before
>processing could have been resumed. Did you check your remaining
>process to see if it was being rolled back?
>
>What did v$session_waiting show for the competing sessions? This view
>should be beneficial if the problem is not the need for all the
>processes to update the same row but in enqueue related or IO related
>etc.... Also in the case where the conflict is for a specific row
>v$session should show the rowid being waited upon.
>
>-- Mark D Powell --

OK, it happened again. This time, no second process. Only one process, holding locks on three different tables. It ended up going away after about an hour, however.

It is a process that should finish by morning. When it is finsihed, it emails a log file. If the log is not received, something happened. So, we checked the logfile, and it was not fully printed to, and that led me to the session in Oracle.

This process has been run almost daily for quite a while, and this problem was only noticed yesterday. Today's lockup was for about an hour, while yesterday's was for more than that.

The queries that were held up were not the first runs of them, they use bind variables, and run many of them. It varies daily, between hundreds, and hundreds of thousands.

I checked v$Session_Wat. The Event was, "SQL*Net message from client". The state was "WAITING". P1Text was, "driver id".

There were about 20 other sessions with the same data, except one had a state of "WAITED UNKNOWN TIME". P1 contained the same data for all of them, except one which had a different driver id. The two that had different data were both my sessions, one in SQL*PLUS, one in PL/SQL Developer.

V$Session showed:

 ROW_WAIT_OBJ#      :  -1
 ROW_WAIT_FILE#     :  0
 ROW_WAIT_BLOCK# :  0
 ROW_WAIT_ROW#    :  0

As I said, the session ended up finishing. But that did seem to be an abnormal slowdown. What might I look at to diagnose this issue?

Brian Received on Fri Jan 04 2002 - 08:55:12 CST