Sql – Oracle SQL- Lock rows with INSERT INTO

lockingoracleplsqlsql

I'm running into the following issue in Oracle SQL. gt_general2 is a global temp table. 

INSERT INTO gt_general2 (n_1, n_2, n_3)
select p.x_id, a.y_id, a.dollar_amt
from table_P p, table_A a
where p.x_id = a.x_id
  and a.h_date = i_date
  and a.dollar_amt > 0
for update of p.x_id, a.dollar_amt nowait; --this is the problem statement

Oracle doesn't like when I try to use the INSERT…SELECT syntax with the SELECT… FOR syntax.
I would like to lock the rows while doing this insert because the real query is actually fairly complex (and does some checks against other views to make sure parent records exist, etc.) and this way I can insert into the temp table while doing the row lock in a single pass.

Is there some other syntax I'm missing that would allow me to do this? On a conceptual level, I don't see any reason why Oracle wouldn't allow me to select rows from tables P and A, lock them in the process, and insert the values into another table Z. I already can do this after all: 

select p.x_id, a.y_id
BULK COLLECT into x_id_list, y_id_list
from table_P, table_A
where ...
for update nowait;

It's of course possible to lock the rows by doing this query twice- once to lock, and once to insert. Previously, I did this and used the lock query to retrieve the "x_id" values in an array to help out the INSERT INTO query. But the problem I ran into was that the x_id value wasn't enough to identify the rows I wanted properly; I needed the pair of (x_id, y_id) and can't create a new nested table type to store it.

Best Answer

You can't combine an INSERT with a SELECT FOR UPDATE in a single statement. You can however use a regular SELECT FOR UPDATE and use bulk collect with bulk insert to achieve nearly similar performance.

You also don't need to create a new permanent SQL type object if you declare the collection in the PL/SQL code.

Here's an example, first the setup:

SQL> CREATE TABLE table_a (x_id NUMBER, y_id NUMBER, dollar_amt NUMBER);
Table created
SQL> CREATE TABLE table_p (x_id NUMBER);
Table created
SQL> INSERT INTO table_a VALUES (1, 1, 0);
1 row inserted
SQL> INSERT INTO table_a VALUES (2, 1, 10);
1 row inserted
SQL> INSERT INTO table_a VALUES (3, 1, 20);
1 row inserted
SQL> INSERT INTO table_p VALUES (2);
1 row inserted
SQL> INSERT INTO table_p VALUES (3);
1 row inserted
SQL> INSERT INTO table_p VALUES (4);
1 row inserted
SQL> CREATE GLOBAL TEMPORARY TABLE gt_general2(
  2     n_1 NUMBER, 
  3     n_2 NUMBER, 
  4     n_3 NUMBER);
Table created

The lock and insert procedure (tested on 11.2.0.2.0):

SQL> DECLARE
  2     CURSOR cc IS
  3        SELECT p.x_id, a.y_id, a.dollar_amt
  4          FROM table_P p, table_A a
  5         WHERE p.x_id = a.x_id
  6           --AND a.h_date = i_date
  7           AND a.dollar_amt > 0
  8           FOR UPDATE OF p.x_id, a.dollar_amt NOWAIT;
  9     TYPE table_rec IS TABLE OF cc%ROWTYPE;
 10     l_table table_rec;
 11  BEGIN
 12     OPEN cc;
 13     LOOP
 14        -- bulk fetch
 15        FETCH cc BULK COLLECT INTO l_table LIMIT 100;
 16        EXIT WHEN l_table.count=0;
 17        -- bulk insert
 18        FORALL i IN 1..l_table.count
 19           INSERT INTO gt_general2 (n_1, n_2, n_3)
 20              VALUES (l_table(i).x_id,
 21                      l_table(i).y_id,
 22                      l_table(i).dollar_amt);
 23     END LOOP;
 24     CLOSE cc;
 25  END;
 26  /
PL/SQL procedure successfully completed

This will do bulk select/insert in batches of 100 rows in a single pass. This should be faster than a lock statement followed by an insert statement. Plus you're guaranteed that the rows inserted are exactly the same as the rows locked (whereas the two statements executed one after the other could return inconsistent results if the table has been modified).

Related Topic