Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Question Unanswered: locking problem with update ... where ... in (select...)

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •