Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Locking/Isolation Level

    Hey all,

    I have a java application which accessses a DB2 7.2 FP9 DB. One of the operations the application performs takes out hundreds of locks (often over 1000). Now most of these are row level locks, and it performs a lot of updates and inserts, so many of them are exclusive locks, the problem is that it takes out table level exclusive locks almost as soon as it starts executing, effectively locking all other users out of the system until it completes (which can take up to 5 mins, this is another issue that needs looking at).

    The isolation level of the DB is CS, all the tables are set to row level locking.
    There are no lock escalations and the locking memory is easily large enough o maintain the many locks it obtains.

    Is there any suggestions you can make where I can go from here?
    Do I need to look at the DB2 settings on the Webserver (The Java ap has a web front end, so all DB connections come from a websphere server)? If so, which settings?

    Any help appreciated.

    Cheers.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Dramtically increase locklist size, which will force escalation to table level when it fills up.

    You should probably also increase maxlocks. Use something over 50 (%) if you really want to curtail lock escalation.
    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
    Jul 2004
    Posts
    306
    Marcus,

    Thanks for the reply, there are no lock escalations taking place at all. The locklist and maxlocks are already larger then they strictly need to be.
    The problem is that exclusinve table locks are taken almost immediately, then the row locks begin to mount up as the application goes through what I assume are loop iterations, but it's the instant table locks that are causing me the problems I think.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Which applications are taking the exclusive table locks? Are they utilities or regular applcations? I would take a snapshot for locks.
    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
    Jul 2004
    Posts
    306
    It's just the one Java App (a web based client served by websphere application server 4).
    The application is performing a task where is adds/updates several records for a customer, it's one of the larger customer operations, but its still a very small operation in the scheme of things and its not acceptable to shut all other users out (as is effectively going to be the case if I can't figure out how to stop the IX locks)

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You don't necessarily want to eliminate IX locks. And don't confuse "exlusive locks" with table locks (they are not the same).

    Any time you "select for update", DB2 takes an IX lock (intent to escalate to exclusive on that row). This prevent others from taking an X lock or an IX lock, but does not prevent S (share) locks. The IX lock is needed to make sure that no one else can change the data between the time the "select for update" is issued and when the update is issued. Doing a commit after the update (or as soon as possible in any transaction) will release locks (in almost all cases).

    The IX lock should occur at the row level if escalation to table level has not occured, which is a completely different issue from escalation from S to IX, IX to X, etc (a completely different kind of escalation). However, if you are using version 7, next key locking of indexes may be locking more rows than the one needed (this has been fixed in version 8 with type 2 indexes).

    Unfortuneately the term escalation has two completely different meanings in DB2 and this causes as lot of confusion.
    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
    Jul 2004
    Posts
    306
    Marcus,

    I probably haven't been completely clear. I get IX table level locks immediately when the application starts to perform this particular task.
    Here is what I mean:

    Lock Object Name = 258
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = PKGACM_TBSP
    Table Schema = DBAUSR
    Table Name = PKGACM
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 2
    Node number lock is held at = 0
    Object Type = Table
    Tablespace Name = PKGACM_TBSP
    Table Schema = DBAUSR
    Table Name = PKGACM
    Mode = IX
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 2307
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = BENDTL_TBSP
    Table Schema = DBAUSR
    Table Name = BENDTL
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 2
    Node number lock is held at = 0
    Object Type = Table
    Tablespace Name = BENDTL_TBSP
    Table Schema = DBAUSR
    Table Name = BENDTL
    Mode = IX
    Status = Granted
    Lock Escalation = NO

    When I look at the snapshot for database afterward, I see no deadlocks or escalations.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Does this application open a cursor (updateable)? If this requires a tablespace scan, it may be locking the entire table. Try looking at the Explain for the SQL statements in question.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jul 2004
    Posts
    306
    I don't think it uses cursors. The developers say it doesn't (the guy who wrote the code is gone) so I'm not 100% I can take that claim at face value.
    I can't get a copy of the SQL from the developers as they say they can't extract it.
    I've tried to obtain it from the Dynamic SQL snap, but thats a bit of a nightmare.

    Is there any other way to tell if cursors at involved?
    Should I have setup anything on the web server, which is effectively the client machine (CLI settings or something)?

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sorry, I don't know of anything off-hand that would cause the table locks, other than what has already been discussed. But maybe someone else will come up with a suggestion.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jul 2004
    Posts
    306
    Okie, cheers for the above in any case.

  12. #12
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    If you want to find out which SQL statements are executed, you can define an event monitor to catch them.

Posting Permissions

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