Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unhappy Unanswered: Transactions and lock

    Hi
    I'm using V8.1.7 on windows.
    I can't figure out what is wrong here. In my test suite (CPP program through ODBC) I start a transaction and insert some rows. I read the rows again without comitting the transaction. Success as expected.
    I start another transaction without committing the first transaction. I issue a query against the table that I inserted the rows in before. I would expect that the query returned empty because the first transaction is not yet comittet (using isolation level Repeatable reads). Instead the query statement fails after aprox. 20 sec. and returns "HY008..... Processing was cancelled due to an interrupt". It seams like the table is locked by the first transaction or something ??
    The same test performs as expected when testing against Oracle and Mssql.
    Is there something wron with my sql or can I configure the DB2 somehow to act as I expect it to do ?
    The Sql is a very simple query that joins two tables and uses primary key as search predicate

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There's nothing wrong here. DB2 implements concurrence in a different way. When you insert a row it remains locked in an exclusive mode until committed. Whenever you attempt to read an exclusively locked row you get a lock wait and eventually a timeout. An exception to that would be an uncommitted read (SELECT ... WITH UR), which wouldn't block on a locked row but would instead read an uncommitted version of it.

    I don't think you'll be able to achieve the behaviour you want on DB2.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You will get a locktimeout (-911 reason code 68) if locktimeout db parm is set to something other than -1 (default).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2004
    Posts
    2

    Thumbs up

    So whats the whole story about isolation level in DB2 if the locking mechanism works like that ?
    I guess that for inserts the table will always be locked unless you actually want to read uncomitted data no matter what isolation level you specify. I guess ACID is down the drain or am I missing something?
    Thanks for the reply I got the info that I needed but I not sure I understand DB2

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The "whole story" is given in much detail in DB2 Administration Guide: Performance. It says, in particular, that an insert doesn't generally lock the entire table but only the inserted row.

    By the way, I fail to see how all of the above contradicts the "ACID principle": a transaction is still atomic, consistent, isolated, and durable.

Posting Permissions

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