Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009

    Unanswered: Deadlock with online transaction


    I am having a question based on the conflict between a batch job and online transaction.

    I have an application from where online transactions came to access the DB2 database. During one point of time a batch job runs and creates a deadlock situation for the online transaction to be processed. This increases the response time for the online transactions. The batch job makes insert and update to one DB2 table which is also used by the online transaction. Is there any way to place a logic in the program (my point is have a kind of commit logic after every 100 row updates or something like that) to avoid this kind of scenario ?

  2. #2
    Join Date
    May 2007
    somewhere in dbforums
    check the LOCKTIMEOUT parameter in db cfg and make sure it is not anything less than 30 secs....

    secondly you may need to reduce the commit count.....

    Also you will have to check the plan for insert and update statements.... for large tables with large amount of data this will take time so you may need to fine tune your query.... you can also use db2 advisor for that matter and accordingly you'll have to change your LOCKTIMEOUT parameter and commit count... and / or maybe add new indexes....

    Another alternative is to run the batch during non-peak hours.... any acceptable time before or after the maintenance window....
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Apr 2006
    Provided Answers: 11
    are you using insert or import ?
    commitcount is for import only
    try to use a commit as soon as possible or allowed
    or if always on the same table - try lock table in share mode
    no other updates can occur and others will be in lock wait$always try to be as specific as possible while describing the scenario and how this is accomplished
    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

  4. #4
    Join Date
    Apr 2009
    hi sorry for the delay response...
    the program associated with the batch job performs insert, delete and update operations. Currently the program has commit logic in place but not for after any count. I checked with our DBA about the LOCKTIMEOUT parameter and as per their info it has been set correctly.

    The batch job time cannot be changed due to some critical processing.

    Please let me know if you have any other suggestions.


Posting Permissions

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