Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Unanswered: How to avoid DEADLOCKS

    Hi,

    We have designed an application to pull some profit and loss reports which help business analysts in analysing company's business across various countries on different industries (like Shop industry, restaurant industry etc.)

    We have provided seperate login ID to each and every user.
    When user pulls report from .NET front end, it accesses DB2 version 9.1 databases.
    Now the situation is, when 1 user tries inserting some data into the database it is not possible for other user to delete data from database.
    I am not able to understand why it is throwing SQLCODE -913 (i.e. deadlock) in this case.
    In database, we have maintained a USER_ID column to distinguish data for one user from another. So, one user's data is completely isolated from other user's data and each user has access to only their data not to other user's data.
    In fact the purpose of maintaining USER_ID was to avoid deadlocks in the application.
    As per our requirement:
    1) We do not need to lock rows
    2) It should be possible to perform simultaneous operations on data base for many users, i.e. user1 should be able to insert/update/delete/select when user2 is performing insert/delete/update/select operation.

    We use DB2 version 9.1

    Regards,
    Upendra

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    do you include the USERID in the WHERE-Clause?
    Do you have an Index on the USERID?

    Cheers
    nvk

  3. #3
    Join Date
    Dec 2010
    Posts
    10
    Hi nvk@vhv,

    We have index on USERID column and USERID column is also present in the WHERE clause. On further investigation, we found that the PAGE level locks are defined on the table. Have asked DBA to change it to ROW level lock. I will check if the issue gets resolved after changing the level of lock from PAGE to ROW and keep you posted with the updates.

    Thanks,
    Upendra

  4. #4
    Join Date
    Oct 2010
    Posts
    6
    Hi,
    I don't know if you have already checked this, but what is the LOCKSIZE of the table?
    For your task, changing the LOCKSIZE to ROW will probably help. keep in mind that you need to verify that the values of locklist and maxlocks are enough

    Hope this helps
    Alex

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since it was mentioned above that they were using Page Locks, we may presume that this is DB2 for z/OS and not DB2 LUW.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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