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.

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.

Hi .

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

The deafault is 10 secs.

HTH

Nitin.

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.

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

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.

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.

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.