SUMMARY(Belated) -143 Error - Deadlock detected.

This is a summary of a problem I posted in April Sorry for delay. Original posting is at the bottom. It turned out to be nothing sinister, just a common or garden clash between Cobol programmers thinking one way, and Informix Programmers thinking another way. It was resolved by disabled lock wait on one side (the Informix side) and using some good old fashioned lock handling techniques. We also found that one program was navigating without an index. Putting one in place solved a lot of the contention. A fragment of my case noted is as under; rgds and thanks very much to those who responded. Peter Tashkoff <tashkop@zespri.co.nz> Zespri International Limited This posting may not be used by any party to vilify another. Standard disclaimers apply. ----------------case note fragment----------------------- 2. The Deadlock error. Can be struck in two circumstances. A. Say Session A has a row lock in table Alpha, and session B has a row lock in table Beta. Session A then places a lock request on the row that Session B has locked, and goes into a wait loop for whatever period it is set to wait. Say Session B then requests a lock on the table that Session A has locked; Session B will receive an IMMEDIATE -143 error. Informix will determine that a deadlock situation exists and return the error immediately. This situation can arise when programs address the same types of processes in reverse order. If they flowed through in the same order, then such problems will not occur. In their own coding, Informix ensure that they only flow one way through processes in order to avoid such problems. B. The same problem can be triggered when sessions are locking rows but (at least one of them ) are not navigating using indexes. In the same case as above, Say one (or both) tables is holding a record, not wanted by the other, but in the same table as that requested by the other. If one or both processes is navigating without using indexing, then it will need to test every record to see if it is the one it wants (sequential scan). If a record is locked it will wait for the prescribed time. In the meantime the other process could be doing the same thing in the other table. Bingo; same effect as if they were actually after the same record row. A deadlock situation exists. Each is waiting on the other. A -143 error is given to the latest party to request a lock. In this situation the problem can be allieviated by means of applying navigational indexing to the database. ================orignial post ============= Hi Folks I have a situation where we are receiving a -143 error, potential deadlock detected in stored procedures called from both Cobol and Informix programs. This is a nasty that I personally have not seen in the past. Lock mode is set to wait. If set to not wait, then we receive a simple 107, record is locked. The stored procedures are layered, one calling another etc. Version is 7.13UC3X1. The benefit of anyone's experience on this would be most appreciated. I will summarise to the list.