Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: HELP! Row level locking

Re: HELP! Row level locking

From: <>
Date: Sat, 9 Sep 2000 15:40:00 -0400
Message-Id: <>


You've already eliminated the most likely candidate for your locking problem--the missing foreign key index. There is one other thing I can point out that you might not have considered. Do you have any bitmap indexes on the table? If so, and you are doing updates, you can get really horrendous locking behavior when the bitmap index locks all involved rows and does its update all at once.

Chris Gait

On 7 Sep 2000, at 4:40, Michael Kline wrote:

> (I believe I sent this with the wrong account and it didn't go through..=
> We are having some strange locking problems.
> Has anyone else seen this before.?
> This is "Oracle8 Release - Production" on NT....
> It's almost like we are getting BLOCK locking instead of row locking...
> Can't find anything in metalink, but maybe someone else has seen this an=
> found a solution...
> I've repeated it on personal oracle and 8.0.4 running on NT and don't ge=
> this problem...
> Our general lock scripts don't tell us which rows are locked, only that =
> lock is held... IE: If we lock for one item, or where primary_key is IN
> (a,b,c), it all looks the same. I seem to remember there was a place we
> could now look to see if a record was locked out side the old ways...
> There was one hit in metalink that said row locks appeared to lock all
> records that matched the first 17 characters of the row id, and this SEE=
> to be what's going on... We're working to get a script to list the V8 ro=
> to further test our theory, but then Tuesday after vacation it appeared =
> was back to working properly...
> This was a brand new, always 8.0.5 database created from scratch... Some
> foreign keys were involved and we found a missing index on one of the
> foreign keys which we added today.
> Then I heard that they were having a similar problem on WEBDB the other =
> on the same server, same database kernel, different database...
> ThinkSpark=A0 - Michael Alan Kline, Sr.
> Technical Consultant -=A0 Richmond, Virginia Office
> 13308 Thornridge Court; Midlothian, VA 23112, USA.
> W:=A0=A0=A0 804-744-1545
> Pager: 877-705-1155 or
> ICQ: 1009605, 975313=A0=A0=A0 PhoneFree: 1057439
> -----Original Message-----
> Sent: Friday, September 01, 2000 9:30 AM
> Hello,
> I am having problems when locking rows.
> We are running ORACLE8 on a Windows NT server.
> I am issuing a:
> SELECT project_id
> FROM project_list
> WHERE project_id =3D :chosen_project_id
> My goal is to ensure that only one user at a time can modify project
> related information.
> When the first SELECT ... FOR UPDATE happens there are no problems.
> Sometimes, when the second SELECT ... FOR UPDATE happens there are no
> problems, sometimes there are (ORA-00054: resource busy and acquire with
> NOWAIT specified).
> Sometimes I can have several rows locked, sometimes I get the ORA-00054
> error when I try to lock a second row.
> I feel that what might really be happing is that the BLOCK is being lock=
> (rather then the row) and when my second/subsequent lock attempts hit th=
> same block, I get the error.
> Can anyone tell me if row level locking is really supported, or if it is
> only block level?
> Is there any way I can get information that would prove/disprove my theo=
> that it is doing block level locking (i.e.: does ROWID provide informati=
> on what block the row is stored)?
> If anyone can provide me some insight, My client and I would really
> appreciate it.
> Robert Nall
> Technical Consultant
> ThinkSpark (Dallas)
> 972-392-0955
> 972-334-4713 (client)
> --
> Author: Michael Kline
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Sat Sep 09 2000 - 14:40:00 CDT

Original text of this message