Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jun 2006
    Posts
    21

    Unanswered: How to lock record when SELECT the record?

    Hello!

    I am facing concurrency issue now. My system would have concurrent update on same record and SELECT statement is executed before update in order to get the updated data for calculation. Therefore, I need to lock the record, including READ from other transaction, after executing the SELECT statement. I am applying Isolation level - Read Stability now but it imposes SHARE lock only when SELECT. Could anyone advise me how to solve this issue? Our system cannot impose LOCK TABLE since it will affect the performance....

    Thanks!

    (DB2 verion - 8.1, OS - AIX)
    Last edited by miyuki; 06-20-06 at 07:46.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Use: SELECT .... FROM table-name FOR UPDATE;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2006
    Posts
    21
    I have tried this sql before. It can only avoid UPDATE by other transaction but cannot prevent READ by other transaction. My system need to block all access on the same record so that concurrent transaction would not reading same data but do different calculation on it. If transaction A has READ record 1, transaction B cannot READ & UPDATE record 1 until transaction A release the lock. However, Read Stablity cannot fulfill this requirement....

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you need to prevent SELECT by another transaction, then update the row first. That will hold an exclusive lock until you COMMIT.

    You could update a value in the row to the same value that already exists, or update a timestamp that is not critical to your application logic.

    If you using this table for a "next available number" for use as a primary key on inserting rows into another table, here is a solution:

    UPDATE table-name
    set LAST_ASSIGNED_NUMBER = LAST_ASSIGNED_NUMBER + 1
    where TYPE = 'xxxx'; [this would be the primary key]

    SELECT LAST_ASSIGNED_NUMBER
    from table-name
    where TYPE = 'xxxx'; [this would be the primary key]

    COMMIT;

    Once the udpate is accepted, no other appliction will be able to do a an update or select on this row until the commit;
    Last edited by Marcus_A; 06-18-06 at 12:12.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2006
    Posts
    21
    Thank you very much! I will try this!

    However, any solution from DB side (e.g. setting similar to isolation level)instead of programming side? Is there any method to impose EXCLUSIVE lock with SELECT statement directly?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by miyuki
    Thank you very much! I will try this!

    However, any solution from DB side (e.g. setting similar to isolation level)instead of programming side? Is there any method to impose EXCLUSIVE lock with SELECT statement directly?
    No, by defintion, a SHARE LOCK that is the result of a SELECT allows other applications to do SELECTS.

    BTW, the isolation level only determines how long a SHARE lock (as a result of SELECT) is held. It does not determine what kind of lock is held on a row.

    CS - Share lock is released as the cursor moves to next row.

    RS - Share locks are released when the current SQL statement is finished.

    RR - Share locks are released at COMMIT.

    Note that all other row locks (as a result of INSERT, UPDATE, DELETE, or SELECT FOR UPDATE) are released at COMMIT.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One other point to remember:

    If an application does a SELECT FOR UPDATE on certain rows, then other applications could SELECT the same rows, but no other applicaiton can do a SELECT FOR UPDATE on the same rows.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    If you need to prevent SELECT by another transaction, then update the row first. That will hold an exclusive lock until you COMMIT.
    Note that even this will not avoid reads from an other application, if that application decides to use isolation level UR ("uncommitted read"). UR access can never be blocked with locking.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Peter,
    I agree, but "with UR" should not be used when data are updating this can lead to inconsistent data.
    UR should only be used when consistency is not 100% required.
    Grofaty

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by Marcus_A
    One other point to remember:
    If an application does a SELECT FOR UPDATE on certain rows, then other applications could SELECT the same rows, but no other applicaiton can do a SELECT FOR UPDATE on the same rows.
    Marcus_A, are you sure about this on db2 linux/unix/windows v8.2?

    1.I created table and inserted one record:
    create table db2admin.test (col1 integer not null primary key, col2 integer)
    insert into db2admin.test values (1,1)

    2. Then I opened DB2 Command Window and executed the command:
    db2 +c select * from db2admin.test where col1=1 for update of col1

    3. Opened new DB2 Command Window and executed the same command:
    db2 +c select * from db2admin.test where col1=1 for update of col1

    Note: The step 3 is working!

    Grofaty

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by grofaty
    Marcus_A, are you sure about this on db2 linux/unix/windows v8.2?

    1.I created table and inserted one record:
    create table db2admin.test (col1 integer not null primary key, col2 integer)
    insert into db2admin.test values (1,1)

    2. Then I opened DB2 Command Window and executed the command:
    db2 +c select * from db2admin.test where col1=1 for update of col1

    3. Opened new DB2 Command Window and executed the same command:
    db2 +c select * from db2admin.test where col1=1 for update of col1

    Note: The step 3 is working!

    Grofaty
    The SELECT FOR UPDATE will hold the U lock on the row and an IX lock on the table (and prevent another SELECT FOR UPDATE) if you use RS or RR isolation level (but not CS). So the following should work (you don't need to name the column):

    db2 +c select * from db2admin.test where col1=1 for update with RS

    I was not aware of this, so thank you for pointing it out.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    However:

    If you use a cursor, the lock is held with CS isolation level:

    1. db2 +c "declare c1 cursor for select * from db2admin.test where col1=1 for update with CS"

    2. db2 +c open c1

    3. db2 +c fetch c1

    4. db2 get snapshot on locks for sample (you will see the U lock on the row and the IX lock on the table).

    I wonder if the lack of locking without the cursor (using CS isolation level) is peculiar to the CLP?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Jun 2006
    Posts
    21
    Quote Originally Posted by Marcus_A
    The SELECT FOR UPDATE will hold the U lock on the row and an IX lock on the table (and prevent another SELECT FOR UPDATE) if you use RS or RR isolation level (but not CS). So the following should work (you don't need to name the column):

    db2 +c select * from db2admin.test where col1=1 for update with RS

    I was not aware of this, so thank you for pointing it out.
    I also tried similar sql using two command editor and it works fine. Then, I tried to update my program to add "FOR UPDATE" in the sql and run this with different connection, it also works fine in my local environment (RAD on Window XP). However, when I tried in it AIX server, it failed I have printed out the isolation level and sql so I sure the programs on server are correct. What is the problem actually?

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by miyuki
    I also tried similar sql using two command editor and it works fine. Then, I tried to update my program to add "FOR UPDATE" in the sql and run this with different connection, it also works fine in my local environment (RAD on Window XP). However, when I tried in it AIX server, it failed I have printed out the isolation level and sql so I sure the programs on server are correct. What is the problem actually?
    Are your sure that you had auto-commit off?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    Jun 2006
    Posts
    21
    Quote Originally Posted by Marcus_A
    Are your sure that you had auto-commit off?
    Yes!
    "conn.setAutoCommit(false);"

Posting Permissions

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