Hi,

I have a locking problem with the following scenario:

session 0:
create table test1 (col1 bigint, col2 char(10));
insert into test1 values (1, '1');
insert into test1 values (2, '2');
commit
* col1 is the primary key

session 1:
update test1 set col2 = '9' where col1 in (select col1 from test1 where col1 = 1)

session 2:
update test1 set col2 = '9' where col1 in (select col1 from test1 where col1 = 2)

session 2 has to wait session 1 to commit. However, session 1 and session 2 are updating different record. How can I prevent session 1 from locking records that it is not actually updating?

Can anyone help me? Thanks in advance.

One thing: you dont seem to have no indexes. So those update sessions are making a table space scan. And doing so, both sessions will lock whole table.

Cherrs, Bill

You should also rewrite your update statements to:

session 1:
update test1 set col2 = '9' where col1 = 1

session 2:
update test1 set col2 = '9' where col1 = 2


Andy