Results 1 to 2 of 2

Thread: Table Locks

  1. #1
    Join Date
    Sep 2007

    Unanswered: Table Locks

    Hi and please excuse my ignorance...

    Operating System: AIX
    DB2: 7.1.0 02010105

    I am trying to enhance the performance and concurrency of a legacy application that I support. The tables are very large. I have a complex query that joins six tables. The server connects to the database using isolation level "uncommitted read". When the server executes this query it returns the first 50 rows to the client and then waits for the client to request more or to cancel the request.

    When I execute this query I notice that DB2 locks all six tables in IN mode and that the locks are NOT escalation locks. DB2 also acquires six row locks of type NS on SYSIBM.SYSTABLES. One row for each table.

    The query is really read-only data so I added the FOR READ ONLY clause to the SELECT statement. This eliminated the six row locks on SYSIBM.SYSTABLES. This is good because I know these row locks could be a source of concurrency problems.

    1 - Is there any way to make the table locks go away?
    2 - If the table locks remain, can another application update a row that is in the result set of the "active query"?

    Please forgive the length of this post. Thank you very much...

  2. #2
    Join Date
    Jul 2004
    IN locks are Intent None locks, this means the lock holder can read any of the locked rows but not update them... all other concurrent applications can read or update these rows

    In short you don't have a locking problem.

Posting Permissions

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