Home » SQL & PL/SQL » SQL & PL/SQL » FOR UPDATE WAIT n (ignores timeout on IOT)
FOR UPDATE WAIT n [message #412057] Tue, 07 July 2009 10:04 Go to next message
foxius
Messages: 4
Registered: July 2009
Junior Member
Hi.

I want to enforce a timeout on SELECT..FOR UPDATE statements, so the application aborts the transaction with error instead of hanging if the row is locked by another session. The recommended solution would be to use "SELECT ... FOR UPDATE WAIT n" but it seems that on IOT tables the timeout is ignored.

For example:

-- Create table
create table TEST
(
  TEST_ID NUMBER(10) not null,
  ACTIVE  NUMBER(1),
  constraint PK_TEST primary key (TEST_ID)
);

-- Populate with some data
INSERT INTO test (test_id, active) VALUES(1, 1);
INSERT INTO test (test_id, active) VALUES(2, 1);
INSERT INTO test (test_id, active) VALUES(3, 1);
COMMIT;


In Session#1 we run:

DECLARE
resource_busy EXCEPTION;
resource_timeout EXCEPTION;

PRAGMA EXCEPTION_INIT (resource_busy, -54);
PRAGMA EXCEPTION_INIT (resource_timeout, -30006);
  
v_id NUMBER;
v_active NUMBER;
BEGIN
SELECT test_id, active INTO v_id, v_active FROM test WHERE test_id=1 FOR UPDATE WAIT 5;
EXCEPTION
  WHEN resource_busy THEN
    RAISE_APPLICATION_ERROR (-20001, 'resource is busy!');
  WHEN resource_timeout THEN
    raise_application_error(-20002, 'lock timeout!');
  WHEN OTHERS THEN
    RAISE;
END; 


We run the same code in Session#2 and get "lock timeout!" exception. That is fine.

Let's try on an IOT:

-- Create table
create table TEST
(
  TEST_ID NUMBER(10) not null,
  ACTIVE  NUMBER(1),
  constraint PK_TEST primary key (TEST_ID)
)
organization index;

-- Populate with some data
INSERT INTO test (test_id, active) VALUES(1, 1);
INSERT INTO test (test_id, active) VALUES(2, 1);
INSERT INTO test (test_id, active) VALUES(3, 1);
COMMIT;


Run the test code in Session#1 - it'll successfully lock the row. Then run the code in Session#2 - it'll wait indefinitely.

Is this a bug or am I missing something here?

This problem is reproducible in Oracle 9.2, Oracle 10g, Oracle 11g on Windows.

Thanks in advance
Re: FOR UPDATE WAIT n [message #412062 is a reply to message #412057] Tue, 07 July 2009 10:29 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bug 4133808. No fix, no fix date.

Regards
Michel

[Updated on: Tue, 07 July 2009 10:29]

Report message to a moderator

Previous Topic: create excel
Next Topic: Need Query tunning Tips for performance (merged 2)
Goto Forum:
  


Current Time: Sun Dec 11 00:26:09 CST 2016

Total time taken to generate the page: 0.12799 seconds