Re: locking and transaction isolation level

Christian Lang wrote: > > Hi, > my company has an DB-bases product which lives of many and huge > transactions (transactions which have an duration of 1 minute). We have My first comment is that there is probably something wrong with ANY application design that creates transactions that take a full minute. But read on, I have the answer for your direct question. Ultimately you need to redesign your application so that you are not holding locks, ie eliminate SELECT...FOR UPDATE and replace the logic with an optimistic locking scheme. > an ONLINE DB 7.23 UC1, customers of us use INFORMIX ONLINE 7.22 - 7.30. > All versions of the ONLINE DB have the same problem(?) which can be > explained by the following example: > one task (transaction) inserts a row into a table with some kind of an > index. > Another task wants to delete another row in that table having another id > (->no row conflict). On some DB versions it works on some not (depends > on the query-optimizer if the delete's statements WHERE clause uses an > index!?). If the delete statment uses an index, the operation works, if > not we are getting error -244 (...yes we have row locking...) (you can > easily produce that error on a table without index). To this point > informix might be strange but probably one must have indexes for all > cases which could happen!?!? My guess is that if you check the ISAM error (sqlca.sqlerrd[1]) it is probably -144 or one of a few other values that indicate that an index or key value is locked. When you update a row in a table with indexes on non-primary key fields the index node which contains the rows seconary key, in each index, must be locked until the transaction is committed. Your delete is trying to delete rows with keys on the same page. You need to SET LOCK MODE TO WAIT 60; so that the delete does not timeout before the other transaction is committed. > The second problem is the ISOLATION level COMMITED READ which should > only return rows which where commited. With the example above, just try > an select from another task using no where clause or an where clause > which could receive the uncommited insert of the other task -> ERROR > -244 on all informix versions we could test! I think informix should > write in the documentation: In COMMITED READ only commited rows are > gotten and if a row is not commited jet, no row is gotton and a error > occurs... (The real funny thing is: on a dirty read standard engine > everything is working allot better (no problems here)). Committed read needs to obtain a shared lock on all rows selected as they are fetched. This is the same problem as the delete. ISOLATION DIRTY READ works because that isolation level does not even check for the presence of locks. Again SET LOCK MODE TO WAIT 60; to wait for the index locks to be released. > Are we the only people using transactions under online or does everybody > use SET LOCK MODE TO WAIT (works fine but the performance of an muli > user system is the performance of an serial single user system...); or > are all our (and our customers) online installations crap??? No, it is just that your application was not written taking STANDARD ANSI ISOLATION behavior into account. Again, recode those long transactions to FETCH the data without locking or a transaction and close those cursors, then after obtaining the user's edits, and before updating the row, BEGIN WORK, refetch with lock (ie FOR UPDATE); perhaps by rowid for speed; and check that the data has not been altered by another user. If not changed update and COMMIT WORK, if changed rollback and tell the user about the conflict, perhaps redisplaying the modified record. This is called optimistic transaction locking because it behaves as if it is optimistic that noone will change the row during the user's activity. Typically this is true as is evident from your description of the problem, ie you do not complain about other users not being able to edit or delete the same row but different rows sharing an index node. You can speed the checking for modifications by adding a timestamp column updated by triggers that you can check quickly. If you make this change in program logic then the deletes, and updates and selects only need to SET LOCK MODE TO WAIT 3; since the actual update transaction will be quite short (< 1 sec). > If someone could help us please mail to cl@daidalos.com (or this > address) (informix support is also welcome). If no one could help us we > have to go to ORACLE where the needed features above are working very > well(but others?) I won't even dignify this comment. There's no need to get vulgar and mention the "O" word is there? Art S. Kagel