Concurrency in Oracle

[coolpacific]

Hi all,
I just found out that if i issue two update statements on the same column of a table in 2 different sessions then one session hangs till i issue a rollback or commit in the other session. For eg i issued 2 statements as follows :

1 .update emp1 set sal = 2566 where deptno = 20 ;
2 .update emp1 set sal = 1500 where deptno = 20 ;

So here command one completes successfully but the second session hangs until i issue a commit or rollback in session 1.

This must be due to the locking techniques of Oracle and isolation levels etc. but what i want to know is whether this is the default behaviour in Oracle or can we change it. Also can somebody suggest me some good links on this topic for further reading.

Thanks a lot in advance

[ales]

Hi,
you're right, this is a locking issue and the default behaviour. You cannot change it.
Why would you permit two users to change the same record at the same time?
How would Oracle decide which user made "the right change"? Perhaps the one who entered the bigger salary ;).

See Oracle Concepts manual: http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c01intro.htm#3932

Regards,

[Raminder]

The default behaviour which you noticed is:

Set transaction isolation level read committed;

The other isolation level which you can set in oracle is:

Set transaction isolation level seializable;

This command follows the principle that if a SQL command alters a resource that may have been uncommitted at the start of the transaction, then that SQL statement will fail instead of waiting for the locks to be freed. This isolation mode is as defined in ANSI standard SQL92.