Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Many asynch accesses to the same table

    Hi to all,
    I have a table accessed by several asynchronous processes, some for R and some other for both R/W.
    How can I avoid deadlock situations?
    How can I avoid to read dirty datas?
    Does exist a common way to solve this problem?
    Thanks a lot,
    Marcello

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 automatically performs locking based on:

    1. Isolation level (RR, RS, CS, UR) - Read the “Administration Guide: Performance” for an understanding of the isolation levels so you can determine which is appropriate for you application. Isolation level can be set in a number of different ways depending on the interface (CLI, embedded SQL, etc.). Try the manuals or the search facility of this forum for a discussion of how to set the isolation level for your application.

    2. Commit frequency - determines the duration of locks. Locks are generally released when a commit is issued.

    3. Certain SQL clauses such as FOR UPDATE OF can affect the locking used by DB2.

    4. Locking level of the Table definition (Row or Table). Row level locking is the default, but Table level locking can be specified in an ALTER TABLE.

    5. Lock escalation from row to table level at runtime. This is influenced by the locklist and maxlocks parameters. Escalation may occur when the locklist memory is full, or when one applciaiton exceeds the percent of the locks in the locklist as determined by the maxlocks parm. Increasing the locklist memory and maxlocks percent will help prevent lock escalation.

    6. An escalation to table level locks can also be explicitly obtained by a "LOCK TABLE" SQL statement.

    7. The following command to update the registry can be used to set RS as the default isolation level:
    db2set DB2_RR_TO_RS=YES
    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
    Mar 2004
    Posts
    3
    Thank you Markus for your reply.
    Though I already know (at least theorically ;-)) the things that you have just indicated, my problem is that I would like to know if exists a common approach, a beautiful way or a simple practical example that explains how to avoid critical situations.
    Where can I find a list of steps to be executed in order to evaluate the best approach in terms of isolation levels, locking level and so on?
    Thanks a lot,
    Marcello

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First, read the manual and determine which isolation level is needed for each program or SQL statement, and then figure out how to implement that depending on the DB2 interface that you are using.
    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
    Mar 2004
    Posts
    3
    Ok Marcus, thanks a lot for the time dedicated to me.
    I'm going to make an evaluation about the needed isolation level.
    However, if you have a good book to read containing some useful example....
    Thanks,
    Marcello

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Follow these steps to maximize concurrency:

    1. Read the “Administration Guide: Performance” for an understanding of the isolation levels so you can determine which is appropriate for you application. Pick the isolation level with the most concurrency that still meets your application integrity requirements. For maximum concurrency, CS is best, followed by RS, then RR is worst. Most applications can function just fine with CS.

    2. Read the “Administration Guide: Performance” to understand how to set the isolation level for each program or DB2 interface (CLI, embedded SQL, etc.) . In most cases you may want to alter the default isolation level, and then change it for a specific program if a higher degree of integrity (RS, or RR) is needed.

    3. The more often you commit (inserts, updates, deletes) the more concurrency you will have. This needs to be balanced against UOW requirements and performance considerations.

    4. Read my suggestions 4-6 and follow the advice to prevent or discourage lock escalation to table level. Obviously, row level locking has better concurrency.

    5. If you can always live with RS instead of RR (most can) implement the registry update in number 7.

    6. In unusual cases where more concurrency is needed, you may want to omit FOR UPDATE OF in a cursor (where you will update or delete a row) to prevent an IX lock being taken when the cursor is positioned at a row (before the actual update or delete takes place). This should only be omitted in extreme cases of lock contention and done with caution (especially for an update).
    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
  •