About ISAM -113 error

I use informix dynamic server 2000 9.21. My program use JDBC. When my program is under a stress test, it sometimes throws ISAM -113 error. I don't know the reason. I checked the explanation for the error. it says: 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. So, I did a simple test to prove how this error is generated. The test like this: 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 use wait lock mode and both session use repeatable read(ansi TRANSACTION_SERIALIZABLE) I found seesion 2 waited there. (That's what I expect, but what the explanation talking about?) I have a little confused about the result. I think this test is similar to the scenario described in the explanation. But why I don't get the error message? What is the exact meaning of ISAM -113. How can I make a simple test to prove it? Thanks in advance.

JACK wrote: > I use informix dynamic server 2000 9.21. > My program use JDBC. When my program is under a stress test, it sometimes throws ISAM -113 error. I don't know the reason. I checked the explanation for the error. it says: > > 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. > > So, I did a simple test to prove how this error is generated. > The test like this: > > 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 use wait lock mode and both session use repeatable read(ansi TRANSACTION_SERIALIZABLE) > > I found seesion 2 waited there. (That's what I expect, but what the explanation talking about?) > I have a little confused about the result. I think this test is similar to the scenario described in the explanation. But why I don't get the error message? > > What is the exact meaning of ISAM -113. How can I make a simple test to prove it? Mmmm, I'll have a go. I think the short answer is, swap your session 1 & 2 round and the second one should then produce the same error. It sounds like you are getting this error on your session 1, before you get to session 2. Because you have no WHERE clause, you are attempting to update the entire table. The fastest way to read a whole table is to perform a sequential scan. Because of your isolation level, you are also going to lock every row in the table. The fastest way to lock every row is simply to place a lock on the table. However, someone else already has a lock on that table, hence the attempt will fail. I think... Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

Actually, I expect session 2 get the error. But it doesn't. It blocks there. So, I want to know how I can get a ISAM -113 error.