1

Let's say I've got two tables t1 and t2, and I'm inserting data into t1 using columns selected from t2.

create or replace package body for_update_test
as
  procedure test(out_response out varchar2) as
  v_id number;
    begin
      select id into v_id from t2 where id = 1 for update;
      insert into t1
      (
        id
      )
      select
        id
      from 
        t2
      where
        id = 1
      ;
      out_response:= 'success';
  end test;
end for_update_test;
/

The above works, but is there a way to combine the insert-select with for update, something like (it obviously does not work, so wondering if it's just the syntax I am not getting right or if it's not supported):

create or replace package body for_update_test
as
  procedure test(out_response out varchar2) as
  v_id number;
    begin
      insert into t1
      (
        id
      )
      select
        id
      from 
        t2
      where
        id = 1
      for update
      ;
      out_response:= 'success';
  end test;
end for_update_test;
/

Here's what I am trying to achieve: select data from a table (t2 here), modify the values, then insert into its gtt twin (t1 here). Then, the gtt data will be applied back into the actual table itself. The for update during the initial selection from t2 ensures the data written back to it eventually through t1 doesn't accidentally overwrite data written by a different procedure that also modifies (different columns) this way.

Way convoluted than it should be this being a legacy routine, but I've got very little leeway right now to change the whole design (like not having to go through GTT), so just locking the original table for updates to do controlled changes.

mystarrocks
  • 4,040
  • 2
  • 36
  • 61
  • 4
    What exactly are you trying to achieve? – Mureinik Sep 09 '22 at 14:02
  • @Mureinik select data from a table, modify the values, then insert into its gtt twin. Then, the gtt data will be applied back into the actual table itself. The `for update` during the initial selection ensures the data written back to it doesn't accidentally overwrite data written by a different procedure that also modifies (different columns) this way. Way convoluted than it should be, but I've got very little leeway right now to change the whole design, so just locking the original table for updates to do controlled changes. – mystarrocks Sep 09 '22 at 14:21
  • You can't do it as you showed because the insert doesn't have a result set you could then operate on, as you would have if you did that in a cursor query say. A more concrete example (and the explanation in the question, not as a comment) would be helpful. Are you going to be operating on a single row each time, or multiple rows - in which case a collection might work instead of a GTT? – Alex Poole Sep 09 '22 at 14:24
  • @AlexPoole the data selected from one table (`t2`) is actually being inserted into a different table (`t1`), so the selection from `t2` is the result set I am trying to lock for updates, while simultaneously inserting into `t1`. I've now added a slightly more detailed explanation of what I'm trying to achieve. It's not necessarily one record, though, so the `select into` will be a collection. – mystarrocks Sep 09 '22 at 14:32
  • 2
    And you can't just do a straight update - you have to use select/modify/update, and go through a GTT? Can you use a collection instead? It's a bit unclear why you are attempting (or having to attempt) this approach, and knowing the constraints might reduce unhelpful suggestions *8-) – Alex Poole Sep 09 '22 at 14:35
  • @AlexPoole Right, I can't do a straight update, because the routine that applies GTT data back on the actual table does some chores. That's the part I was referring to as being convoluted and leaving me with little leeway to redesign and refactor. – mystarrocks Sep 09 '22 at 14:38
  • I'm struggling to follow what you're trying to accomplish. If we assume `id` is the primary key, your initial `select ... for update` locks a row if there is already a row with an `id` of 1 in `t1`. If there is no such row, the `select ... for update` would fail, an error would be thrown, and your code would fail. If a row is found, the subsequent `insert` would either have to insert 0 rows or it would insert 1 or more rows and generate a duplicate primary key exception. Maybe you intended to lock the row in `t2` rather than `t1` in a `select ... for update`? – Justin Cave Sep 09 '22 at 19:33
  • @JustinCave, yes, the data is selected from `t2` and inserted into `t1`, so `t2` is what I am trying to lock, not `t1`, as I've clarified in this comment: https://stackoverflow.com/questions/73663311/oracle-insert-into-select-for-update?noredirect=1#comment130081553_73663311. I've now clarified in the question as well. – mystarrocks Sep 09 '22 at 20:12
  • So in your first example, are you doing a `select ... for update` on `t2` rather than `t1`? Where is the "modification of `t2`" taking place? Is that happening between the `select ... for update` and the `insert`? – Justin Cave Sep 09 '22 at 21:12
  • @JustinCave, sorry, fixed the first example. So, here is the order of operations: 1) read from `t2` (this is what I am trying to lock/synchronize), 2) insert selected data from `t2` into `t1` (which is actually a GTT), and 3) write the `t1` (i.e. GTT data) back into `t2`. – mystarrocks Sep 09 '22 at 21:20
  • Where is `t2` being modified? If you do a simple `insert into t1 select from t2 ...`, Oracle guarantees a consistent read from `t2`. No matter how many transactions are modifying the data, you'll see the data as of the SCN at which the `insert` statement started (assuming read committed isolation level). If your goal is to hold a lock on the `t2` row until you have step 2a where you modify the data in `t1` and then 3 where you write the data back to `t2` then you'd need a separate `select ... for update`. – Justin Cave Sep 09 '22 at 21:26
  • 1
    If there is an update timestamp in `t2`, you could use optimistic locking instead and base your logic on that. But that wouldn't prevent another session from changing the data, it would alert you that the data changed and you'd have to write code to handle it (i.e. retry the process or whatever makes sense for you). – Justin Cave Sep 09 '22 at 21:27
  • @JustinCave "_If your goal is to hold a lock on the t2 row until you have step 2a where you modify the data in t1 and then 3 where you write the data back to t2 then you'd need a separate select ... for update._" - the insert-with-select in step 2 _is_ the modification to `t1`, and yes, I do intend to hold the lock until after I've written the data back from `t1` to `t2`, so I figure I would need a `select for update` for the same selection in the `insert-with-select`. – mystarrocks Sep 09 '22 at 22:51
  • @JustinCave, regarding Optimistic locking, that was one of the considerations, but the retry (all updates MUST go through, but the order of update does not matter because the transactions don't modify the same column in the affected rows) was cumbersome enough, so we're resorting to locking. If the transactions simply updated the columns directly (i.e. no `t1` all), then none of these conflicts would have happened given the transactions don't update the same columns, but the need to go thru the GTT intermediary (hence the insert into GTT) has what's gotten us in this situation. Oh well! – mystarrocks Sep 09 '22 at 22:55
  • @JustinCave if you could add those two suggestions (separate `select... for update` matching the `select` in `insert-with-select` like in my 1st example, and the optimistic locking approach) as an answer, I can accept it. – mystarrocks Sep 09 '22 at 22:57

0 Answers0