CODEWITHSUNDEEP
oracle-databasetransactionsisolation
loonis
loonis

Reputation: 1497

Allow multiple update on different transaction Oracle

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.

Upvotes: 1

Views: 1576

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21085

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.

Upvotes: 1

Ajit Kamble
Ajit Kamble

Reputation: 153

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

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

Upvotes: 0

Related Questions