1

Is it possible to allow on Oracle 11g multiple UPDATE on the same line on 2 different transaction ?

For example in the first transaction

UPDATE MYTABLE SET COLUMN1 = 'value1'

And in the second transaction, the update is locked by the first one.

UPDATE MYTABLE SET COLUMN1 = 'value2'

I need to allow both update that will be visible on their own transaction.
In my context, both transaction will be rollbacked at the end.
It's like a level 1 cache associated to each transaction where all modifications will be applied and read until commited.

Erik A
  • 31,639
  • 12
  • 42
  • 67
loonis
  • 1,317
  • 16
  • 19
  • I don't think this is possible. What is it you are trying to achieve by updating this table? – Boneist Mar 08 '18 at 13:47
  • I have hundreds of Java integrations test using jdbc. Each test is encapsulated in a oracle transaction. At the beginning of the test I update the db according to specific test condition. I run the test and then I rollback the transaction to leave the db as initial state. The limitation of this method is that I can only run the test sequentially and not in parallel. I'm looking for a way to isolate the modification of a transaction without blocking the other transactions like a level 1 cache. – loonis Mar 08 '18 at 15:09
  • You won't be able to update the same row in two different transactions. You could, of course, update different rows in your tests, which would allow you to run them in parallel. – Boneist Mar 08 '18 at 15:14

2 Answers2

1

You may do it, but you will observe the expected behavior - you may UPDATE the records that are dirty, i.e. subject of oper transaction from other session, but you will have to wait until the first transaction is commited (or rollbacked).

So your scenario will be serialized:

UPDATE  from session 1

UPDATE from session 2  is waiting

ROLLBACK from session 1

UPDATE from session 2 is completed

You may test it out using this setup:

Session 1

create table MYTABLE(column1 varchar2(10));

insert into MYTABLE(column1) values(null);
commit;

UPDATE MYTABLE SET COLUMN1 = 'value1';
select * from MYTABLE;
COLUMN1  
----------
value1

Session 2

UPDATE MYTABLE SET COLUMN1 = 'value2';

is waiting until session 1 makes commit or rollback.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • Indeed that's the behavior I am observing. I was wondering if I can avoid this waiting by playing with Oracle isolation level. – loonis Mar 08 '18 at 15:35
  • @loonis not with an isolation level; may you are looking for a **[global temporary table](https://oracle-base.com/articles/misc/temporary-tables)**, it can be updated (and inserted) independently by any session... – Marmite Bomber Mar 08 '18 at 15:47
  • I have an existing db with thousands of tables that are updated at the beginning of each test. I can't see a way to use the global temporary tables in my case. – loonis Mar 08 '18 at 16:14
0

you can update multiple column in single sql.refer my sql.

UPDATE MYTABLE SET COLUMN1 = 'value1',COLUMN1 = 'value2'

Ajit Kamble
  • 153
  • 1
  • 12