Re: locked for update

From: <mmellin_at_my-dejanews.com>
Date: Mon, 02 Nov 1998 14:51:18 GMT
Message-ID: <71kgt6$na3$1@nnrp1.dejanews.com>

In article <71evav$1c8q$1_at_whale.gu.net>,   "Alex Buzunov" <sun_at_goldentele.com> wrote:
> Hi, All
>
> Could you pls. tell me how I could check in stored procedure whether a row
> in a table locked for update?
>
> Thanx in advance!
>
> AB
>

Alex, I just figured out a way to implement such a procedure in our application. We have a 3 tier app where the database is static. We have implemented Oracles row level locking as well as using a row version to control who has the latest version of the object.

/* The procedure takes the unique value of the object, the table name, field name, and the rowversion. A dynamic string is built with this information and executed to do a "SELECT FOR UPDATE NOWAIT" If the statement returns a value then the procedure ends and the user still holds the lock so that the application can run an update. If there is a lock on the record from another user Oracle will raise 'ORA-00054: resource busy and acquire with NOWAIT specified' If there is no record returned and there is no lock then the rowversion is incorrect and "(-20010, 'Invalid rowversion')" is raised. */

CREATE OR REPLACE PROCEDURE RESPONSENOW.LOCK_OBJECT

(objectid_in       IN VARCHAR2,
 tablename_in      IN VARCHAR2,
 fieldname_in      IN VARCHAR2,
 rowversion_in     IN INTEGER)
 IS
  select_cursor      INTEGER;
  open_rec           INTEGER := 0;

  BEGIN

• prepare a cursor to obtain the lock on the table the table select_cursor := dbms_sql.open_cursor; DBMS_SQL.PARSE(select_cursor, 'SELECT rowversion FROM '||tablename_in||' WHERE '||fieldname_in||' = :objectid and rowversion = :rowversion for update nowait', DBMS_SQL.V7); dbms_sql.bind_variable (select_cursor, ':objectid', objectid_in); dbms_sql.bind_variable (select_cursor, ':rowversion', rowversion_in); open_rec := dbms_sql.execute_and_fetch(select_cursor); IF open_rec = 0 THEN RAISE_APPLICATION_ERROR (-20010, 'Invalid rowversion'); ROLLBACK; END IF; DBMS_SQL.CLOSE_CURSOR(select_cursor); END;

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Nov 02 1998 - 08:51:18 CST