Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

HELP! Row level locking

From: Michael Kline <maklinesr_at_home.com>
Date: Thu, 7 Sep 2000 07:39:24 -0400
Message-Id: <10612.116393@fatcity.com>


(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 8.0.5.1.1 - 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 and found a solution...

I've repeated it on personal oracle and 8.0.4 running on NT and don't get this problem...

Our general lock scripts don't tell us which rows are locked, only that a 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 SEEMS to be what's going on... We're working to get a script to list the V8 rowids to further test our theory, but then Tuesday after vacation it appeared it 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 day on the same server, same database kernel, different database...

ThinkSpark  - Michael Alan Kline, Sr.
Technical Consultant -  Richmond, Virginia Office 13308 Thornridge Court; Midlothian, VA 23112, USA. W:    804-744-1545
Michael.Kline_at_ThinkSpark.com
Pager: 877-705-1155 or 8777051155_at_page.metrocall.com ICQ: 1009605, 975313    PhoneFree: 1057439

-----Original Message-----
Sent: Friday, September 01, 2000 9:30 AM Subject: Row level locking

Hello,

I am having problems when locking rows.

We are running ORACLE8 8.0.5.1.1 on a Windows NT server.

I am issuing a:

     SELECT project_id
     FROM   project_list
     WHERE  project_id = :chosen_project_id
     FOR UPDATE NOWAIT;

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 locked
(rather then the row) and when my second/subsequent lock attempts hit the
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 theory that it is doing block level locking (i.e.: does ROWID provide information on what block the row is stored)?

If anyone can provide me some insight, My client and I would really appreciate it.

Robert Nall Received on Thu Sep 07 2000 - 06:39:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US