FOR UPDATE NOWAIT request in case of FK in Oracle

Question

I have a table tab1 (t_id, status) and a table tab2 (id, t_id, status) , where in t_id.tab2 there is a foreign key reference to tab1.t_id .

Suppose an Oracle s1 session receives a SELECT FOR UPDATE NOWAIT write lock on tab1 using t_id=123 .

While the NOWAIT lock still holds s1 on tab1 , can a new s2 record update the record in tab2 using tab2.t_id=123 (FK)?

Answer 1

As noted in the comments, a row lock issued using the read SELECT ... FOR UPDATE ( SELECT ... FOR UPDATE ) does not extend to the child table where the FOREIGN KEY declared.

Here is an example that demonstrates this:

 -- Set up schema CREATE TABLE tab1 (t_id NUMBER(10), status VARCHAR2(10), PRIMARY KEY (t_id)); CREATE TABLE tab2 (id NUMBER(10), t_id NUMBER(10), status VARCHAR2(10), PRIMARY KEY (id), CONSTRAINT fk_tab1 FOREIGN KEY (t_id) REFERENCES tab1 (t_id)); INSERT INTO tab1 (t_id, status) VALUES (123, 'Status1'); INSERT INTO tab1 (t_id, status) VALUES (234, 'Status1'); INSERT INTO tab2 (id, t_id, status) VALUES (1, 123, 'Status2'); INSERT INTO tab2 (id, t_id, status) VALUES (2, 234, 'Status2'); COMMIT; 

The following script, which uses the AUTONOMOUS_TRANSACTION pragma to issue a new transaction, is successfully executed (for example, in Oracle SQLDeveloper):

 SET autocommit 0; SELECT * FROM tab1 WHERE t_id=123 FOR UPDATE NOWAIT; DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN UPDATE tab1 SET status = 'Status2' WHERE t_id = 234; UPDATE tab2 SET t_id = 234, status = 'Status2' WHERE t_id = 123; COMMIT; END; 

Thus, you can change other rows in tab1 , as well as the foreign key column in tab2 , which points to a locked row in tab1 .

As expected, trying to update a locked row:

 SELECT * FROM tab1 WHERE t_id=123 FOR UPDATE NOWAIT; DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN UPDATE tab1 SET status = 'Status2' WHERE t_id = 123; COMMIT; END; 

... will happen with the error message:

  ORA-00060: deadlock detected while waiting for resource