Re: exclusive row locks on the same table

On 8 Jan 2002 06:36:40 -0800, mark.powell_at_eds.com (Mark D Powell) wrote:

>SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote in message news:<3c35b8cf.3973720359_at_news.alt.net>...
>> On 3 Jan 2002 07:11:54 -0800, nospam.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
>
>The v$ views information is point in time so if the above information
>was taken while the holdup was occuring then it is valid otherwise it
>isn't. Assuming the v$ query information is from the hold-up period
>then v$session shows that the session is not waiting on a row so if
>the not shown lockwaited column was not null then the lock was on a
>resource (table level, transaction, space management etc...). If it
>was null then the problem is not a locking issue.
>
>The v$session_wait message says that the Oracle process was waiting
>for a message from the client. This is a normal message except since
>this is a batch process and as such there shouldn't be any messages
>from the client except commands to process and communications for the
>transfer of data back to the client.
>
>This brings up the possiblility that you have an external to Oracle
>problem especially if the client is remote: backups being sent accross
>the network, ftp of large files, non-Oracle heavy hitter processes
>being ran either on db server or client machine.....
>
>Now if these queries were ran the next moring the results are
>meaningless. In this case write a pl/sql script to find the sid for
>the target session and query v$session and grab the lockwaited and
>rowid columns every minute or couple of minutes. Also grab the
>v$session_wait description. Set up this pl/sql to be ran the same
>time as the target job so that if another problem occurs you have some
>valid information to look at. This can help determine when your
>target is having to wait for locks held by other processes, but once
>the other process commits the target would continue but the total run
>time would be long.
>
>I hope this helps.
>
>-- Mark D Powell --

The data was from the next time the process held up. I think this is becoming a recurring problem, though the wait isn't as long. Now it may be for only an hour or so.

I will try to look at the system and network the next time this happens.

Brian Received on Tue Jan 08 2002 - 09:08:17 CST