I got an error which error number is ISAM -113.
I have checked the explain about error ISAM -113.
I have some questions on the explain.

1. One seesion use exclusive lock on a table. Another session try to lock the same table exclusively. Because I use wait lock mode, so I expected another seesion will wait for this exclusive lock. Why it throws the exception?

2. How can I avoid this exception. (I don't want to use index. Is it possible? If not, it means without index, informix almost can't work correctly, is it strange?)

I tried to reproduce the problem under the scenario described by the ISAM -113. But I can't.
my test case is:
1. do select name from test for update (session 1)
2. select name from test (session2).

session 1 and session 2 are from different connection.

I found seesion 2 waited there.

I have a little cofused what is the exact meaning of the ISAM -113 explanation.

Hi,

I think that the error refer to one locked file and not one locked table.
What version of Informix are you using ?.
As is the sentence SQL ?.


Gustavo.

I use informix dynamic server 2000 9.21.
I don't think it refer to a file. Because the explanation is :

If you get this error (-113) when using a transaction isolation mode of REPEATABLE READ or SERIALIZABLE, and if your query did not use an index (and therefore had to use a sequential scan of the entire table), the cause of the error might be that another user had either an exclusive lock or a promotable lock on at least one row in the table. If you are in REPEATABLE READ or SERIALIZABLE mode and your query requires a scan (search) of every single record in the table to find all the records that meet the conditions in the WHERE clause, then the engine will need to lock every record in the table to maintain the repeatability of the read. In practice, rather than locking every single record, the engine will try to lock the entire table. But if there are any exclusive locks, or even promotable locks, on any row in the table, then the engine will not be able to get a shared lock on the entire table, and the query will fail.

Hi,
Ok.
Also the error -113 include:
-113 ISAM error: the file is locked.

Another user request has opened the file (table) that was requested in
exclusive mode. In systems that use files for locking, a tablename.lok file
exists. Possibly such a file was left behind when another program terminated
abnormally. If you are sure that is the case, you can release the lock by
emptying that file. Lock files are not used in many systems, and they are
never used with Informix Dynamic Server or INFORMIX-OnLine Dynamic Server.

This error occurs with Informix Dynamic Server or INFORMIX-OnLine Dynamic
Server when a user attempts to access a table that has been locked.

For C-ISAM programs, rerun the program after the file is unlocked. For
SQL products, tables are locked explicitly using the LOCK TABLE statement
and implicitly during the execution of the CREATE INDEX or ALTER TABLE
statement. Rerun the program or query when the table is unlocked.
.

Please send me the sintax SQL.

Gustavo.

Hi,

Please, excuse me, which is the error of SQL ?

Gustavo.

my programme always get ISAM -113 error, but I don't know the reason. So I want to have a simple test to reproduce this problem. So, I did the test above. one seesion lock the table, another session try to read it. but the result is it will wait there. According to the explanation of ISAM -113. It should fail.

So, I want to know under which situation, this error can be reproduced.
Is there any simple test can generate this error?

Thanks.

Hi Swordfish ,

Even if you are using Lock mode wait the the exception will occur after the wait ends and the lock is still not acquired.

for e.g. -
If you have set lock mode to wait 5,
the other session will wait till 5 mins and if it still does not get hold of a lock on the tabel, it'll throw and exception.

Also even if you say-
set lock mode to wait;
the wait time will be taken as the value specified for the Onconfig parameter DEADLOCK_TIMEOUT.