Hi All,
I am working with db2V7.2 aix,
I am trying to insert rows to a table from three different processes, and I get deadlock error, how can I manage to avoiding deadlock error

I would apreciate help me with this issue.
Thanks

First, just a refresher of an example of what constitues a deadlock:

ProcessA wants resource1, locks it
ProcessB wants resource2, locks it
ProcessA wants resource2, waits for lock to finish
ProcessB wants resource1, waits for lock --> deadlock

Suggestions:

1) have your processes access (insert,update, delete) tables in the same order. e.g. Always access TableA, then TableB.
2) have shorter Units of Work (UOW) when possible. This prevents locks being held that are no longer needed.
3) specify a small lock wait timeout. Better to timeout on obtaining a lock, then deadlocking.

HTH

Andy

Thank you ARWinner, I should say
1- all three processes are trying to insert rows in ONE table, so you think even "small lock wait timeout" works?
2- I suggested to developers to create three temptables for inserting rows in them and then import from those temptable to original table. Is it work (from performance point of view) ?
thanks

You can still get deadlock while inserting into a single table (at least with V7.x). DB2 is locking more than just the rows being inserted. It also has index locks that are probably causing your situation.

My suggestion would be to have very small UOWs so that the locks are not held for any length of time. This means that your developers need to commit more often if possible. If this is not possible, maybe they should explictly put a table lock before inserting rows
(LOCK TABLE mytable IN EXCLUSIVE MODE). THis will prevent anyone else from accessing (reading or writing) the table while the UOW is active.

Andy

Hi,

I am not sure for AIX, but on /390 and Windows there are no index locks anymore. And, smaller UOWs can degrade performance dramatically, because while commiting DB2 has to write the log data synchronous.

Well, I think DB2 on AIX works different, since INSERT contention does not exists on /390 for many years now (1996 or so...). So, when you are just INSERTING, then DB2 on AIX works different. In this case, ignore my last post, please.

That is because on OS390 you have type 2 indexes. These are not available on UDB for LUW V7.x. (they are in V8).

Andy

The index locking situation has been improved with type 2 indexes, but old type 1 indexes must be explicitly converted on indexes previously created before version 8.

Frequently, people mix the concept of deadlock and timeout, when they are actually not the same. A deadlock (as described by ARWinner) would theoretically NEVER be resolved except when the database manager detects the probable deadlock, and then cancels one of the processes.

An ordinary timeout due contention can occur when a process is waiting for a lock to be released by another process, but the defined wait period has been exceeded, and the process is then cancelled. In theory, if there was no wait period defined (or it was long enough), the process eventually would be able to continue when the other process finished.

As discussed in recent threads on this forum, lock escalation can cause row locks to be escalated to tables level locks, which causes contention with other processes. See the other recent threads (about 1-2 weeks ago) for a discussion on how to deal with this.

Also, you can use cursor stability (CS) isolation level (defined in the program or the bind option) to increase concurrency. Usually the default isolation level is repeatable read (RR), but the default can be changed. You might consider changing the default isolation level to read stability (RS) and have certain programs override it by using CS. Frequent commits also help.

However, increasing concurrency and preventing lock escalation to table level, may increase the amount of work DB2 does in maintaining row level locks. Obviously, in many cases, the extra workload is necessary to have adequate concurrency.