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.

Well, I believe they were not going after the same rows. Or at least, they *shouldn't* have been. I checked a few and they were different. I didn;t check them all.

How does one check the "transaction work areas"? Unfortunately, I am not familiar with that.

>
>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?

The process should have run the one UPDATE query. Being it didn't seem to run, what would have been needed to be 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.

I believe you mean v$session_wait. I could not find one with "ing" on the end.

I did not check these. Thank you.

Brian Received on Thu Jan 03 2002 - 12:46:13 CST