Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: DB2 locktime out parameter

    Hi,

    Can anybody tell me if there is any chance of the LOCKTIME OUT parameter in the Database parameters being overriden by some other parameter?


    I have this following problem:

    My database was designed with a LOCK TIME OUT parameter of 25 seconds. But I could see deadlocks occuring within the wait time of 10 seconds. The process which gets rolled back hardly waits for 10 seconds to take locks on the table, involves in deadlock and rollsback.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A deadlock is not the same as a timeout, even though both are caused by lock contention. If DB2 detects a deadlock (deadly embrace) it will cancel one of the threads in order to resolve it. Please do a search on deadlock to find discussions of deadlock in this forum.
    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 2003
    Location
    Austin, TX, USA
    Posts
    278

    DLCHKTTIME

    Hi .

    In case there is a deadlock then the dlchktime parameter overrides the LockTimeout Parameter.

    The deafault is 10 secs.

    HTH

    Nitin.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Ddlchktime is not exactly an "override" of locktimeout. Dlchktime determines the frequency that DB2 checks for deadlocks. If it just so happens that DB2 checked for a deadlock right after an actual deadlock occured, then the wait time is minimal. If the deadlock occurred right after the last check, the wait time will be longer until the next check occurs.
    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
    Nov 2003
    Posts
    4

    WHen does deadlock happen then?

    Hi marcus or anybody,

    Can u pl explain me the process of deadlock?

    If supppose, Process P1 is updating table T1, It takes an exclusive lock at row level. At the same time, if process P2 wants to update T1 and it waits for an exclusive lock on table T1.

    Now the Q is, till what time the second process waits and when will the deadlcok occure and when will the timeout wil happen?

    PL answer to my Qs. Thanks

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here is “one” deadlock scenario. In this case there are two tables, but it can also occur with one table, but there needs to be 2 updates involved.

    Program A updates Table 1 and then tries to update Table 2 before committing both updates.

    At the same time, Program B updates Table 2 and then tries to update Table 1 before committing.

    Both program 1 and 2 are trying to obtain locks that are being held by other processes, but are each holding their own locks on resources that the other program needs. This is a deadly embrace, because both programs will wait forever unless one of them is cancelled.

    If row locking is used, this lessons the likelihood of this happening because they would have to be waiting on the same rows for a deadlock to occur. But if the locks get escalated to table level, then it can be common.
    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
    Nov 2003
    Posts
    4
    Do you say that there can not a be deadlock between an update and a select?

    updation takes an exclusive lock. And Select needs a shared lock, which it can get only after the updation is over. what will happen in such a situation? deadlock or a timeout.

    the deadlock I mentioned above was between an update and a select only. but there are 2 tables involed. There is a join between the 2 tables in both the programs.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There are many different scenarios that can cause a deadlock. A deadlock can occur between more than 2 application connections.

    The difference between a deadlock and timeout is that a deadlock will never be resolved by just waiting, which is why DB2 will cancel one application when a deadlock is detected. A timeout would theoretically be resolved if the timeout parameter is long enough (or set to -1) and the applications wait for the locks to be released.

    It is difficult to determine your situation without seeing the SQL from both programs and also the DDL of tables and indexes involved.
    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
  •