Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: for update on join

    db2 ese on P/linux 9.5 fp8
    we have a simple query with subselect and "for update" clause because of deadlocks and multi threads

    SELECT m.FILE_ID FROM batch.OUTBOUND_MERGE_FILES m
    where m.FILE_ID IN (select f.FILE_ID FROM clsm.file f
    WHERE f.PARTNER_ID=18000 AND f.APPLICATION_TYPE = 'A1'
    AND COALESCE(f.STATUS, '') <> 'GARBAGE' )
    AND m.MERGE_STATUS = 'READY'
    FOR UPDATE

    the developer would like to get the rows, ordered by rows of subselect - so they changed the query to a join

    SELECT f.inbound_file_Id as inbound_id , m.FILE_ID FROM batch.OUTBOUND_MERGE_FILES m
    join clsm.file f on f.file_Id = m.file_Id and f.PARTNER_ID= 18000 AND f.APPLICATION_TYPE = 'A1' AND COALESCE(f.STATUS, '') <> 'GARBAGE'
    where m.MERGE_STATUS = 'READY'
    order by f.INBOUND_FILE_ID

    but in this case the "FOR UPDATE" is not supported :sql0511

    does anybody have an idea how to circumvent this and get the requested result
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    One option would be: Go back to the original query and use a sub-select to the ORDER BY clause.

    If you have performance concerns, I suggest you first run/measure this query to really identify you have a problem.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by przytula_guy View Post
    we have a simple query with subselect and "for update" clause because of deadlocks and multi threads
    I would think this would cause more concurrency problems, not fewer.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, the FOR UPDATE clause uses a different, more restrictive lock. This reduces concurrency but probably works around with the problems in the applications causing the deadlocks.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the FOR UPDATE clause was used to avoid the deadlocks,
    WITH RS(or RR) USE AND KEEP UPDATE(or SHARE, EXCLUSIVE) LOCKS clause might help you.

Posting Permissions

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