ROWID Oracle 9i and record lock

[camdebuck]

We are using ROWID to update records. Everything worked fine under 8i. We upgraded the database to 9i and we now get "record locked by another user" error message. This only happens on NUMBER type columns. Does anyone know of a work around for this?

 

[Marco Kalter]

The "Record is locked by another user" will only occur if an "ORA-00054 resource busy and acquire with NOWAIT specified" error occurred. This would of course indicate that the record is indeed locked. Can you verify this?

------------------
Marco Kalter
Allround Automations

 

[camdebuck]

Unfortunately, it definately is not locked by another user. If you have Oracle 9i database (must be UTF8) create the following table:

CREATE TABLE MYTEST (MYTESTID NUMBER(10),
MYINFO VARCHAR2(35),
MYNUM NUMBER(10));

INSERT INTO MYTEST VALUES (1,'DATA',8);
COMMIT;

Once you have done this, make sure that your NLS_LANG in your registery is not set to UTF8 (but the database is created as a UTF8 characterset).

For example, here is what mine looks like:

AMERICAN_AMERICA.WE8ISO8859P1

Now, create a delphi app using DOAD and a ROWID. Try chaing one of the NUMBER colums. You will get the ora error and it is defiantely not locked by anyone else.

Now, if you chang the WE8ISO8859P1 to UTF8, then the error does not occur.

I've heard that this is an oracle bug, but have not seen an offical oracle tar.