[Q] What does Error -244 mean
A user got Informix error -244 ("could not do physical order read to fetch next row") when running a DELETE via Intersolv ODBC. The thread explains it is a locking conflict: the rows/pages being scanned (a sequential, non-indexed read) are locked by another session. Suggested fixes were setting the table to row-level locking (ALTER TABLE ... SET LOCK MODE (ROW)) and using SET LOCK MODE TO WAIT n so transient locks don't abort the statement, plus trapping/retrying the error in the application. One poster's claim that inserts lock the whole table, and the SET ISOLATION TO DIRTY READ suggestion, were corrected: dirty read only affects SELECTs, not deletes. The original poster traced his case to a crashed session leaving an open transaction holding locks, and asked how to clear such locks, but no answer to that is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm tyring to perform a delete query via ODBC to Informix using the Intersolv drivers. I get the following error: Could not do physical order read to fetch next row. (Error -244). Does anyone know what it means and how to correct it? Thanks Steve
Thanks for the tip Vijaya,
I can't beleive the whole table gets locked on an Insert!!!
PS but what does "SET ISOLATION TO DIRTY READ" do?
Thanks for your feedback,
Steve
vijaya reddy <vijaya_reddy@providian.com> wrote in message
news:378a4801@discussions...
>
> "Steven Wong"
> <steven.wong@tequilasoftware.com> wrote:
> >I'm tyring to perform a delete query via
> ODBC to Informix using the>Intersolv drivers.
> I get the following error: Could not do
> physical order
> >read to fetch next row. (Error -244). Does
> anyone know what it means and
> >how to correct it?
> >
> >
> >Thanks
> >Steve
> >
> >
>
> Before you read do
>
> SET ISOLATION TO DIRTY READ
One last thing, is there a way of flushing the locks that a user has? I found the error -244 was infact caused by a user tyring to insert into the db, but then crashed leaving the transaction incomplete. Thanks Steve vijaya reddy <vijaya_reddy@providian.com> wrote in message news:378a4316@discussions... > > "Steven Wong" > <steven.wong@tequilasoftware.com> wrote: > >I'm tyring to perform a delete query via > ODBC to Informix using the>Intersolv drivers. > I get the following error: Could not do > physical order > >read to fetch next row. (Error -244). Does > anyone know what it means and > >how to correct it? > > > > > >Thanks > >Steve > > > > > > Solution: > ___________ > > Hi > You will get this error if more than one > application tries to access the table > simultaneously. > For example, application A tries to insert > record into table and application B tries to > update one of the records in same table,then > most likely you will get this error in > application B because insertion in informix > will lock the entire table.Until insertion of > record is successful, you cannot update the > table from application B. > You can write following piece of code in > error handler of both the applications: > > 'Check that table is locked. If so, give > message box to retry and cancel. > Dim rdoErr As rdoError > > For Each rdoErr In rdoErrors > > If (rdoErr.Number = -244 Or > rdoErr.Number = -113 Or _ > rdoErr.Number = -107 Or > rdoErr.Number = -154) Then > > If (MsgBox("Table is being used by > another user. Do you wish to,", _ > vbQuestion + vbRetryCancel, > g_strTitle) = vbRetry) Then > > Resume > > End If > > Endif > > Next >
"Steven Wong" <steven.wong@tequilasoftware.com> wrote: >I'm tyring to perform a delete query via ODBC to Informix using the>Intersolv drivers. I get the following error: Could not do physical order >read to fetch next row. (Error -244). Does anyone know what it means and >how to correct it? > > >Thanks >Steve > > Solution: ___________ Hi You will get this error if more than one application tries to access the table simultaneously. For example, application A tries to insert record into table and application B tries to update one of the records in same table,then most likely you will get this error in application B because insertion in informix will lock the entire table.Until insertion of record is successful, you cannot update the table from application B. You can write following piece of code in error handler of both the applications: 'Check that table is locked. If so, give message box to retry and cancel. Dim rdoErr As rdoError For Each rdoErr In rdoErrors If (rdoErr.Number = -244 Or rdoErr.Number = -113 Or _ rdoErr.Number = -107 Or rdoErr.Number = -154) Then If (MsgBox("Table is being used by another user. Do you wish to,", _ vbQuestion + vbRetryCancel, g_strTitle) = vbRetry) Then Resume End If Endif Next
I means that either the table had lock mode row and the row you want to delete is locked by someone else or the table had lock mode page and another user has the page containing the data locked. Change the table to row level locking (ALTER TABLE <tabname> SET LOCK MODE (row);) and in you application execute: SET LOCK MODE TO WAIT <nseconds>; which will prevent transitory locks from stopping your application. Art S. Kagel Steven Wong wrote: > > I'm tyring to perform a delete query via ODBC to Informix using the > Intersolv drivers. I get the following error: Could not do physical order > read to fetch next row. (Error -244). Does anyone know what it means and > how to correct it? > > Thanks > Steve
"Steven Wong"
<steven.wong@tequilasoftware.com> wrote:
>I'm tyring to perform a delete query via
ODBC to Informix using the>Intersolv drivers.
I get the following error: Could not do
physical order
>read to fetch next row. (Error -244). Does
anyone know what it means and
>how to correct it?
>
>
>Thanks
>Steve
>
>
Before you read do
SET ISOLATION TO DIRTY READ
I've also seen this error occur eventhough locking is at the row level, no one is using the row, but someone else in inserting data into the same table. I am beginning to suspect that it has something to do with the way Informix handles indices, but not even the ex-Informix colleague of mine can seem to pinpoint the real problem. Any ideas? Jun Nolasco "Art S. Kagel" wrote: > > I means that either the table had lock mode row and the row you want to > delete is locked by someone else or the table had lock mode page and > another user has the page containing the data locked. Change the table > to row level locking (ALTER TABLE <tabname> SET LOCK MODE (row);) and > in you application execute: SET LOCK MODE TO WAIT <nseconds>; which > will prevent transitory locks from stopping your application. > > Art S. Kagel > > Steven Wong wrote: > > > > I'm tyring to perform a delete query via ODBC to Informix using the > > Intersolv drivers. I get the following error: Could not do physical order > > read to fetch next row. (Error -244). Does anyone know what it means and > > how to correct it? > > > > Thanks > > Steve
It does have to do with the way Informix handles indices -- in this case, the fact that it is not using an index at all, hence the "physical order read" error. Read my unofficial Informix FAQ at http://www.geocities.com/SiliconValley/Bridge/4578/faq.html. In the OnLine section, it covers these locking errors. Feel free to email me with any feedback you have, e.g. if it isn't clear to you after you've read it. June -- june_t@hotmail.com Still alive -- just when you thought it was safe to come out with the chocolate... Please do not send Informix questions to this account. I would add 'Please do not send spam to this account' but I suppose I would be wasting my bits. Jun Nolasco wrote: > I've also seen this error occur eventhough locking is at the row level, > no one is using the row, but someone else in inserting data into the > same table. I am beginning to suspect that it has something to do with > the way Informix handles indices, but not even the ex-Informix colleague > of mine can seem to pinpoint the real problem. > > Any ideas? > > Jun Nolasco > > "Art S. Kagel" wrote: > > > > I means that either the table had lock mode row and the row you want to > > delete is locked by someone else or the table had lock mode page and > > another user has the page containing the data locked. Change the table > > to row level locking (ALTER TABLE <tabname> SET LOCK MODE (row);) and > > in you application execute: SET LOCK MODE TO WAIT <nseconds>; which > > will prevent transitory locks from stopping your application. > > > > Art S. Kagel > > > > Steven Wong wrote: > > > > > > I'm tyring to perform a delete query via ODBC to Informix using the > > > Intersolv drivers. I get the following error: Could not do physical order > > > read to fetch next row. (Error -244). Does anyone know what it means and > > > how to correct it? > > > > > > Thanks > > > Steve
Steven Wong wrote:
> Thanks for the tip Vijaya,
>
> I can't beleive the whole table gets locked on an Insert!!!
Good, because it isn't true. Inserts do not lock the whole table.
> PS but what does "SET ISOLATION TO DIRTY READ" do?
It tells the engine to ignore locks when you attempt to read a row.
However, this only works on selects, not on deletes, as I believe you
said you were attempting. At the risk of repeating myself, read my
unofficial Informix FAQ at
http://www.geocities.com/SiliconValley/Bridge/4578/faq.html.
June
--
june_t@hotmail.com
Still alive -- just when you thought it was safe to come out with the
chocolate...
Please do not send Informix questions to this account.
I would add 'Please do not send spam to this account'
but I suppose I would be wasting my bits.
> Thanks for your feedback,
> Steve
>
> vijaya reddy <vijaya_reddy@providian.com> wrote in message
> news:378a4801@discussions...
> >
> > "Steven Wong"
> > <steven.wong@tequilasoftware.com> wrote:
> > >I'm tyring to perform a delete query via
> > ODBC to Informix using the>Intersolv drivers.
> > I get the following error: Could not do
> > physical order
> > >read to fetch next row. (Error -244). Does
> > anyone know what it means and
> > >how to correct it?
> > >
> > >
> > >Thanks
> > >Steve
> > >
> > >
> >
> > Before you read do
> >
> > SET ISOLATION TO DIRTY READ