Re: locking and transaction isolation level

"Art S. Kagel" <kagel@bloomberg.net> offerred: +>Christian Lang wrote: +> 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. Gee, Art is usually spot on, but I'm afraid this time he is a bit off the mark. First, index locking is done with whatever lock mode the table uses. If the table uses row locking, then only the index entry is locked; if page locking is in effect, only then would an entire index node be locked by the update of a single key. +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. Nope. CR does not OBTAIN any locks; it simply tests to see if it COULD acquire a lock. In essence, this is how the server checks to see if someone else currently holds an exclusive lock on the resource. The source of Christine's problem is that Informix will not skip over a locked row. That is, if you access a table sequentially with an isolation level of committed read or better and a row that is to be read has an exclusive lock on it, Informix will not read past that row. Until the lock is released, it acts as a sort of roadblock to reading the table past that point. If you were to do an indexed read, it should be able to get the row you are after. However if an index key is locked, and Informix needs to read that key to see if it matches the select condition or not, then the same effect will take place. If the key you are after is physically placed earlier in the index node, then there will be no problem. Once you hit the locked key, you can go no further. Since a dirty read ignores locks of any sort, it will never encounter this problem. Dave ** Dave Kosenko <davek@summitdata.com> ** Director of Training Services (732) 469-4070 ** Summit Data Group (an Informix Authorized Education Center) ** Find my advice useful? Let me teach you everything I know about ** Informix. Sign up for OFFICIAL Informix training at SDG. ** For details, see http://www.summitdata.com/training