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

Search Discussions

  • Cjgait_at_earthlink.net at Sep 9, 2000 at 7:40 pm
    Michael,

    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.

    Regards,
    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 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 an= d
    found a solution...

    I've repeated it on personal oracle and 8.0.4 running on NT and don't ge= t
    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 SEE= MS
    to be what's going on... We're working to get a script to list the V8 ro= wids
    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=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
    Michael.Kline_at_ThinkSpark.com
    Pager: 877-705-1155 or 8777051155_at_page.metrocall.com
    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 8.0.5.1.1 on a Windows NT server.

    I am issuing a:

    SELECT project_id
    FROM project_list
    WHERE project_id =3D :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 lock= ed
    (rather then the row) and when my second/subsequent lock attempts hit th= e
    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= ry
    that it is doing block level locking (i.e.: does ROWID provide informati= on
    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
    INET: maklinesr_at_home.com

    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: ListGuru_at_fatcity.com (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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 7, '00 at 11:39a
activeSep 9, '00 at 7:40p
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase