Simulating Lock TimeOut

Simulating LOCK TIMEOUT with INSERT/UPDATE/DELETE Statements

The SELECT statement has a FOR UPDATE clause, which allows you to lock the selected rows so that other users cannot lock or update the rows until you end your transaction. The FOR UPDATE clause has the optional keyword NOWAIT. NOWAIT returns control to you if the SELECT statement attempts to lock a row that is locked by another user. If you omit this clause, Oracle waits until the row is available and then returns the results of the SELECT statement.

INSERT/UPDATE/DELETE statements do not have (and probably will never have) a NOWAIT keyword or any sort of LOCK TIMEOUT keywords.

This is not a big problem with UPDATE/DELETE statements, because you can call SELECT … FOR UPDATE NOWAIT before you call UPDATE/DELETE:
— First database session
BEGIN
UPDATE dept
SET dname = dname
WHERE deptno = 10;
END;
/
— Second database session
DECLARE
l_rowid ROWID;
BEGIN
SELECT ROWID INTO l_rowid
FROM dept
WHERE deptno = 10
FOR UPDATE NOWAIT;

UPDATE dept
SET dname = dname
WHERE ROWID = l_rowid;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -54 THEN
— ORA-00054: resource busy and acquire with NOWAIT specified
DBMS_OUTPUT.PUT_LINE (‘DEPT is locked – you can”t update!’);
ELSE
RAISE;
END IF;
END;
/
DEPT is locked – you can’t update!

Calling SELECT … FOR UPDATE NOWAIT before INSERT, however, has no effect because SELECT in the second database session can’t see the non-committed row:
— First database session:
BEGIN
INSERT INTO dept
(deptno, dname)
VALUES
(99, ‘DEPT 99’);
END;
/
— Second database session:
DECLARE
l_dummy NUMBER;
BEGIN
SELECT 1 INTO l_dummy
FROM dept
WHERE deptno = 99
FOR UPDATE NOWAIT;

DBMS_OUTPUT.PUT_LINE (‘DEPT exists!’);
ROLLBACK;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO dept
(deptno, dname)
VALUES
(99, ‘DEPT 99’);
WHEN OTHERS THEN
IF SQLCODE = -54 THEN
DBMS_OUTPUT.PUT_LINE (‘DEPT is locked – you can”t insert!’);
ELSE
RAISE;
END IF;
END;
/
(SELECT found nothing and INSERT waits)
However, Oracle has the parameter DISTRIBUTED_LOCK_TIMEOUT which specifies the number of seconds that a distributed transaction will wait for locked resources, (the default is 60 seconds). Note that you can’t change the value of this parameter in session.

We don’t have a distributed transaction, but we’ll execute a quasi-remote statement using a local database link:
CREATE DATABASE LINK local_db_link
CONNECT TO scott IDENTIFIED BY tiger using ‘local_alias’
— alias for your local database
/

— First database session:
BEGIN
INSERT INTO dept
(deptno, dname)
VALUES
(99, ‘DEPT 99’);
END;
/
— Second database session:
BEGIN
INSERT INTO dept@local_db_link
— QUASI-REMOTE STATEMENT!!!
(deptno, dname)
VALUES
(99, ‘DEPT 99’);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE (‘DEPT exists!’);
WHEN OTHERS THEN
IF SQLCODE = -2049 THEN
— ORA-02049: timeout: distributed transaction waiting for lock
DBMS_OUTPUT.PUT_LINE (‘DEPT is locked – you can”t insert!’);
ELSE
RAISE;
END IF;
END;
/
DEPT is locked – you can’t insert!
–(message when first session COMMITs after time defined
–in parameter DISTRIBUTED_LOCK_TIMEOUT)
Compliments of Zlatko Sirotic

7 thoughts on “Simulating Lock TimeOut

  1. You can use the DBMS_LOCK procedure to create a virtual lock before doing the insert; this will then cause the second session to act similarly as if you were doing the select for update nowait – and no waiting for timeouts and no distributed transactions. However, it is limited – pe4rformacne degrades as the number of virtual locks increases (no more than hundreds).

  2. Es gibt jede Menge Müdigkeit Ursachen, welche daran schuld sein können, dass die Güte des Schlafes während der Nacht nicht genügend ist.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s