How to release Informix lock from Java

Question

Is there way how to release a lock in Informix from Java?

[Error Code: -107, SQL State: IX000]  ISAM error: record is locked

In our test environment, we have a lot of lock exceptions and the only way how to solve it is restarting database.

PS: I am not looking solution for database expert. I want to solve this from Java or Groovy.

Answer 1

What happens is that another user has locked the record you want; this is normally transient and the use of SET LOCK MODE TO WAIT tends to be enough.

But this error can be avoid by using indexes, if they already exist then the data distributions are out-of-date and you need to UPDATE STATISTICS.

You really should talk to your DBA about this behavior.

To find the sessions that has locks over the table in question you can use the syslocks table:

SELECT  * 
FROM    sysmaster:syslocks
WHERE   dbsname     = '<DATABASE>'
        AND tabname = '<TABLE>';

To kill the session you have to be able to issue an onmode -z.

This is not a good practice, even if it is a test environment. The purpose of a test environment is to debug and this is not debuging. But neither bouncing the IDS is a good practice.

Answer 2

Some possible solutions:

1. Your DBA should check the lock levels for tables and on your test environment and compare it to a production environment. Looks like lock level on your test environment might me too broad. Setting lock level to ROW instead of PAGE or TABLE should help if this is the case.

2. Adjust the isolation level of your session (e.g. you can execute SET ISOLATION TO DIRTY READ when you open the connection). This can help if you get the errors while reading the data, but be careful when using the dirty read as you might encounter ghost records.

3. As mentioned before, adjust the waiting period to give the locker chance to release the lock (execute SET LOCK MODE TO WAIT 5 when opening the connection; this will yield the execution of your thread for 5 seconds at most, and will raise an error if the record is still locked).