Re: exclusive row locks on the same table

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