Deadlock Handling
To minimize deadlock between application servers, design your application with the following points in mind:
• ESQL application servers accessing Ingres databases do not share transaction context. Two servers (possibly in the same global transaction) modifying the same page in a table will compete for locks.
• The resource manager (Ingres DBMS) may abort your transaction if it encounters a locking contention problem (for example, deadlock or lock-wait timeout).
The following strategies can help minimize locking problems:
• Place services that access the same table into the same application server process.
• Set the read locking off where appropriate by including the following line in your ENVFILE:
ING_SET=SET LOCKMODE SESSION WHERE READLOCK = NOLOCK
• Set a loc- wait timeout value in the resource manager to avoid waiting indefinitely for a page or table lock to be freed. Add the following to your ENVFILE:
ING_SET=SET LOCKMODE SESSION WHERE TIMEOUT=value
where value is TPBLOCKTIME * SCANUNIT * 0.5 or less
• Use a hash structure on your table to avoid locking contention on inserts using a sequential key.
• Clients (or services who explicitly initiate transactions) must test the tpcall return status for deadlock or time-out. If either problem is detected, the client should abort the transaction.
For more information on locking strategies, see the Database Administrator Guide.
Last modified date: 11/09/2022