Duration/scope of SET ISOLATION and SET LOCK MODE TO WAIT

I'm trying to solve a problem in which I'm getting error -107 (failed to perform physical-order read) when updating a single row in a table. I have several applications updating the same table, although each application only updates its own specific rows, so no more than one application can update a particular row. What I need to know is the scope or duration of the following SQL statements: SET ISOLATION TO DIRTY READ and SET LOCK MODE TO WAIT n Do these statements apply for the duration of an open connection, within a single transaction, or some other scope of which I am unaware? thanks, - chris chrisw@ignitemedia.com Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

<chrisw@ignitemedia.com> schrieb in im Newsbeitrag: 7km964$lks$1@nnrp1.deja.com... > I'm trying to solve a problem in which I'm getting error -107 (failed > to perform physical-order read) when updating a single row in a table. > I have several applications updating the same table, although each > application only updates its own specific rows, so no more than one > application can update a particular row. > > What I need to know is the scope or duration of the following SQL > statements: > > SET ISOLATION TO DIRTY READ> > and > > SET LOCK MODE TO WAIT n> > Do these statements apply for the duration of an open connection, > within a single transaction, or some other scope of which I am unaware? > > thanks, > > - chris > > chrisw@ignitemedia.com > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. The scope is the duration of Your connection. Wolfgang

Scpe is the duration of the connection. You problem is probably that the table in question has LOCK MODE page set. Change that to row level locking (ALTER TABLE SET LOCK MODE (row);) and also SET LOCK MODE TO WAIT n. Art S. Kagel chrisw@ignitemedia.com wrote: > > I'm trying to solve a problem in which I'm getting error -107 (failed > to perform physical-order read) when updating a single row in a table. > I have several applications updating the same table, although each > application only updates its own specific rows, so no more than one > application can update a particular row. > > What I need to know is the scope or duration of the following SQL > statements: > > SET ISOLATION TO DIRTY READ> > and > > SET LOCK MODE TO WAIT n> > Do these statements apply for the duration of an open connection, > within a single transaction, or some other scope of which I am unaware? > > thanks, > > - chris > > chrisw@ignitemedia.com > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.

Possibly, although I'd say more likely the applications are not using an index to get at their "specific rows". Thus the "physical-order read" error, which implies sequential scan. One application thus blocks another application, even though they are not going after the same rows. This is covered in more detail on my unofficial Informix FAQ at http://ww.geocities.com/SiliconValley/Bridge/4578 . 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. Art S. Kagel wrote: > Scpe is the duration of the connection. You problem is probably that > the table in question has LOCK MODE page set. Change that to row level > locking (ALTER TABLE SET LOCK MODE (row);) and also SET LOCK MODE TO > WAIT n. > > Art S. Kagel > > chrisw@ignitemedia.com wrote: > > > > I'm trying to solve a problem in which I'm getting error -107 (failed > > to perform physical-order read) when updating a single row in a table. > > I have several applications updating the same table, although each > > application only updates its own specific rows, so no more than one > > application can update a particular row. > > > > What I need to know is the scope or duration of the following SQL > > statements: > > > > SET ISOLATION TO DIRTY READ> > > > and > > > > SET LOCK MODE TO WAIT n> > > > Do these statements apply for the duration of an open connection, > > within a single transaction, or some other scope of which I am unaware? > > > > thanks, > > > > - chris > > > > chrisw@ignitemedia.com > > > > Sent via Deja.com http://www.deja.com/ > > Share what you know. Learn what you don't.