Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: Update... (Select ) concurrency problem

    Hi everyone,

    I am currently encountering concurrency issues with the following statement. I am using JDBC with autocommit on.

    Update table1 t1
    Set t1.column1 = 'something'
    Where t1.column2 = (Select t2.column1 from table2 t2, table1 ttt1 where
    ttt1.column1 is null and t2.column1 = ttt1.column2 fetch first 1 rows only) WITH CS;

    Let me try to explain the logic:
    1. The select statement retrieves 1 row that satisfies some criteria (in this case, just joining the 2 tables) and the table1.column1 is empty.
    2. The update statement updates the table1.column1 to 'something'.

    This statement works fine when there is only 1 user but the problem occurs when I test it with multiple threads. The problem is that I get back a whole lot of DeadLock exceptions. I don't quite understand this part. Doesn't the transaction lock up the rows until it completely finishes?

    If I change WITH CS to WITH UR, then I don't get any DeadLocks, but some rows dont get updated because of the dirty reads.

    Does anyone have a solution this ? ie. No DeadLock and no dirty reads...

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You could try using RR or RS isolation level. Also, make sure you have the necessary indexes for your join, otherwise it could be doing a tablescan, whcih is more likely cause contention problems. Do a runststats on the tables and all indexes.
    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
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that the join in subquery is not neccesary.

    Why not use simpler statement, like this...
    Code:
    UPDATE table1 t1
       SET t1.column1 = 'something'
     WHERE t1.column1 is null
       AND EXISTS
           = (SELECT 0
                FROM table2 t2
               WHERE t2.column1 = t1.column2
             )
     WITH CS
    ;

  4. #4
    Join Date
    Sep 2011
    Posts
    3
    Quote Originally Posted by Marcus_A View Post
    You could try using RR or RS isolation level. Also, make sure you have the necessary indexes for your join, otherwise it could be doing a tablescan, whcih is more likely cause contention problems. Do a runststats on the tables and all indexes.
    Wouldn't RR or RS lead to more deadlocks? I have actually tested it and it resulted in more deadlocks then CS.

  5. #5
    Join Date
    Sep 2011
    Posts
    3
    Quote Originally Posted by tonkuma View Post
    I think that the join in subquery is not neccesary.

    Why not use simpler statement, like this...
    Code:
    UPDATE table1 t1
       SET t1.column1 = 'something'
     WHERE t1.column1 is null
       AND EXISTS
           = (SELECT 0
                FROM table2 t2
               WHERE t2.column1 = t1.column2
             )
     WITH CS
    ;
    The sql I posted was just a sample to clarify the point.

    I guess what I want to achieve is more of a QUEUE, hence its not what a relational database is best for....

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by world2160 View Post
    Wouldn't RR or RS lead to more deadlocks? I have actually tested it and it resulted in more deadlocks then CS.
    It can lead to more deadlocks on some SQL, and less deadlocks on others. Using RR and RS would tend to single thread things more, but as you noted it could itself cause deadlocks, especially with different kinds of SQL running at the same time.

    One of the most important things to improve concurrency is to make sure the SQL is not doing a table scan. If table is too small, you may have to set it to volatile to encourage index usuage.
    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
    Quote Originally Posted by world2160 View Post
    The sql I posted was just a sample to clarify the point.
    That was my suspicion. If you have some real SQL that had a concurency problem, it would be easier to offer some advice once it became clear what you are trying to do. Usually, poorly designed databases and applications have more concurency problems that well designed ones.

    Also, I am not sure about your statement:

    "If I change WITH CS to WITH UR, then I don't get any DeadLocks, but some rows dont get updated because of the dirty reads."

    Not sure how that could adversely affect the subselect unless you are deleting a lot of rows while the update is taking place.
    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
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Originally Posted by tonkuma
    I think that the join in subquery is not neccesary.

    Why not use simpler statement, like this...

    Code:
    UPDATE table1 t1
       SET t1.column1 = 'something'
     WHERE t1.column1 is null
       AND EXISTS
           = (SELECT 0
                FROM table2 t2
               WHERE t2.column1 = t1.column2
             )
     WITH CS
    ;
    The sql I posted was just a sample to clarify the point.

    I guess what I want to achieve is more of a QUEUE, hence its not what a relational database is best for....
    Even if you used another database or keyed(or indexed) files,
    you can design and write inefficient, and/or error prone programs.
    You should make properly designed programs with understanding of the characteristics of the system you are using,
    before complining the isuues/problems in your poorly designed program.

    Back to your original SQL statement,
    if there were some records of ttt1 satisfying the conditions, one of them must be t1.
    It is not necessary to access table1 twice.
    Last edited by tonkuma; 09-19-11 at 22:58. Reason: Replace "ill designed" to "poorly designed"

Posting Permissions

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