Re: ODBC, NT and Workgroup problem

On Thu, 22 Oct 1998, Jun Nolasco wrote: > But, why is it that even with row level locking, these errors still > occur eventhough no one else is currently using the record in question > (but with others holding locks on other records)? And from the error > messages, it looks like it is the index record that is locked, not the > data record. What gives? > The statement is probably doing a sequential scan of the index. A select statement doing a sequential scan of data pages or index pages cannot read past a lock in the default isolation mode - committed read. BTW - SET LOCK MODE TO WAIT - only works for and update statement or a select statement inside a begin work - commit work. A select statement not inside a transaction will not wait. Therefore proper handling of the 243... error is important. Also an update statement that does not use an index can have many locks on the table as it sequentially scans looking for rows to update. The larger the table the longer it takes to seq scan and the longer the duration of the locks. Steve Berg > Jun Nolasco > nolasco@inx.net > > > Art S. Kagel wrote: > > > > Jun Nolasco wrote: > > [Reply SNIPPED] > > > asevillano@gesfor.es wrote: > > > > > > > > Hello all, > > > > > > I have a problem in a NT 4.0 with INFORMIX Workgroup 7.23 and ODBC (Informix- > > > > cli). When I insert a lot of records with a unique program there is no > > > > problem, but when I execute several program at the same time begin to appear > > > > those errors: > > > > > > -244: [INTERSOLV][ODBC Informix driver][Informix]Could not do a physical-order> > > > read to fetch nect row. (S1000) > > > > -243: [INTERSOLV][ODBC Informix driver][Informix]Could not position within a > > > > table (%s). (S1000) and > > > > -271: [INTERSOLV][ODBC Informix driver][Informix]Could not insert new row into > > > > the table. (S1000)> > > > > > > > Is it INFORMIX threadsafe? > > > > Yes it is, but that is not your problem. > > > > You have concurrency issues. You either need to tell the database to > > wait for locks to clear instead of returning lock errors or code your > > program to spin or sleep for a bit and then retry the operation. You > > can set the locking wait mode with: SET LOCK MODE TO WAIT; which will > > wait forever for the lock to clear or: SET LOCK MODE TO WAIT n; where > > n is a number of seconds to wait. In that latter case if the lock has > > not been released before the time expires you will still get the errors > > 243, 244, 271 and others and so should code to handle them reasonably. > > You can scan the Informix Error Codes manual to determine the few other > > codes that indicate a lockout condition. Normally locks are very short > > lived in a system with properly written clients and so the "WAIT n" > > version with 'n' = 5 is usually more than sufficient to solve > > concurrency problems without hanging the client if someone locks a row > > and goes on vacation to Accapulco for a week. > > > > Jun's suggestion to change to row level locking will of course reduce > > the number of concurrency caused lockouts and so I support that > > suggestion as well. It just does not go far enough. > > > > Art S. Kagel >