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
] 
Blog
Search
Help
Members
Register
Login
Home
