SELECT FOR UPDATE problem
A Delphi/BDE developer found that SELECT ... FOR UPDATE against Informix Dynamic Server (7.2/7.3, non-ANSI database, Committed Read) did not reliably hold locks: a second application could UPDATE the "locked" rows before the first transaction committed, unlike Oracle. Replies suggested that rows are only locked when actually fetched, that locks are released as the cursor moves on, and that Repeatable Read isolation would be needed; the poster objected that changing isolation levels globally would lock rows for ordinary reporting selects and be error-prone in Delphi. He mentioned falling back on a "dummy update" to hold locks, but the thread ends without a confirmed resolution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We are experiencing some problems while using SELECT FOR UPDATE through Delphi programming language to assure exclusive access to some information on an informix table (7.3, non ANSI database). NOTE: This whole thing works alright on an Oracle database (PO 7) so I guess the problem isn't caused by Delphi access. - We are using Delphi 4 and acessing data via the native drivers provided (SQL Links) - We start a transaction - We execute the SELECT FOR UPDATE (we open an TQuery that executes this) - We do not make a COMMIT before the next step. - If we start another application and try to execute an UPDATE on the previously "locked" data there are certain times (still to understand) that the UPDATE actually succeeds BEFORE any commit on the first application. We tested this with PAGE lock mode tables and ROW lock mode tables. Can someone explain this? What's the use of SELECT FOR UPDATE in Informix? Do we have to run a dummy update to keep the information locked (I know this works even without a SELECT FOR UPDATE)? Thanks a lot for any help... P.Silva Critical Software
You have to use an isolation level (repeatable read) In article <87vack$kqj$1@duke.telepac.pt>, "P.S." <psilva69 @ hotmail.com> wrote: > We are experiencing some problems while using SELECT FOR UPDATE through > Delphi programming language to assure exclusive access to some information > on an informix table (7.3, non ANSI database). > > NOTE: This whole thing works alright on an Oracle database (PO 7) so I guess > the problem isn't caused by Delphi access. > > - We are using Delphi 4 and acessing data via the native drivers provided > (SQL Links) > - We start a transaction > - We execute the SELECT FOR UPDATE (we open an TQuery that executes this) > - We do not make a COMMIT before the next step. > > - If we start another application and try to execute an UPDATE on the > previously "locked" data there are certain times (still to understand) that > the UPDATE actually succeeds BEFORE any commit on the first application. We > tested this with PAGE lock mode tables and ROW lock mode tables. > > Can someone explain this? > What's the use of SELECT FOR UPDATE in Informix? > Do we have to run a dummy update to keep the information locked (I know this > works even without a SELECT FOR UPDATE)? > > Thanks a lot for any help... > > P.Silva > Critical Software > > Sent via Deja.com http://www.deja.com/ Before you buy.
Hi, did you fetch the entry befor the second update ? Informix locks it not befor you fetch it. Dirk "P.S." schrieb: > We are experiencing some problems while using SELECT FOR UPDATE through > Delphi programming language to assure exclusive access to some information > on an informix table (7.3, non ANSI database). > > NOTE: This whole thing works alright on an Oracle database (PO 7) so I guess > the problem isn't caused by Delphi access. > > - We are using Delphi 4 and acessing data via the native drivers provided > (SQL Links) > - We start a transaction > - We execute the SELECT FOR UPDATE (we open an TQuery that executes this) > - We do not make a COMMIT before the next step. > > - If we start another application and try to execute an UPDATE on the > previously "locked" data there are certain times (still to understand) that > the UPDATE actually succeeds BEFORE any commit on the first application. We > tested this with PAGE lock mode tables and ROW lock mode tables. > > Can someone explain this? > What's the use of SELECT FOR UPDATE in Informix? > Do we have to run a dummy update to keep the information locked (I know this > works even without a SELECT FOR UPDATE)? > > Thanks a lot for any help... > > P.Silva > Critical Software
But if I use a repeatable read isolation level every select I execute (even those that aren't meant to be for update) will lock the selected rows to every user (even those trying to make a simple report to printer. Isn't it so? BTW we are using the COMMITED READ mode. Thanks for your help. P.Silva Critical Software PORTUGAL <yap123@my-deja.com> wrote in message news:87vor1$8s6$1@nnrp1.deja.com... > You have to use an isolation level (repeatable read) >
Dirk: We didn't fetch any data from the result set because we had this strange behaviour when we did fetches: If we scroll to the end of the result set on app1 (causing all possible fetches to occur) and then on the second application we tried to make an UPDATE statement on one of the previous records then it succeeded! We got the ideia that the locks were being released as the cursor position changed on app1. Is the lock only kept after the fetch if an update to the data is being made before scrolling? If so, then there's no need to use a SELECT FOR UPDATE because without it, if in the context of a transaction one app updates some data then a second app will not even be allowed to make a SELECT on that data (Oracle on READ COMMITED would return the values before the beginning of the transaction). I heard some people make "dummy updates" to the data they want to keep locked (eg: update <table1> set key = key where <my_lock_record_criteria>). This whole behaviour it's pretty confusing so I guess we'll take this "dummy update" approach (to small results sets). Thanks a lot. Paulo Silva Critical Software Dirk Niemeier <dirk.niemeier@stueken.de> wrote in message news:38A3C3AC.4D1FDE07@stueken.de... > Hi, > did you fetch the entry befor the second update ? > Informix locks it not befor you fetch it. > > Dirk > > "P.S." schrieb:
Paulo, first you should tell me what database-type you are using. If you are using Informix Dynamic Server (IDS, IUS, etc) or above you should set the isolation level to dirty read. But if you are using Informix Standard Engine (SE) (I think you do) you should make clear if your database uses locking or not.(start database command). Dirk "P.S." schrieb: > Dirk: > > We didn't fetch any data from the result set because we had this strange > behaviour when we did fetches: > If we scroll to the end of the result set on app1 (causing all possible > fetches to occur) and then on the second application we tried to make an > UPDATE statement on one of the previous records then it succeeded! We got > the ideia that the locks were being released as the cursor position changed > on app1. > > Is the lock only kept after the fetch if an update to the data is being made > before scrolling? If so, then there's no need to use a SELECT FOR UPDATE > because without it, if in the context of a transaction one app updates some > data then a second app will not even be allowed to make a SELECT on that > data (Oracle on READ COMMITED would return the values before the beginning > of the transaction). > > I heard some people make "dummy updates" to the data they want to keep > locked (eg: update <table1> set key = key where <my_lock_record_criteria>). > This whole behaviour it's pretty confusing so I guess we'll take this "dummy > update" approach (to small results sets). > > Thanks a lot. > Paulo Silva > Critical Software > > Dirk Niemeier <dirk.niemeier@stueken.de> wrote in message > news:38A3C3AC.4D1FDE07@stueken.de... > > Hi, > > did you fetch the entry befor the second update ? > > Informix locks it not befor you fetch it. > > > > Dirk > > > > "P.S." schrieb:
Dirk: Thanks a lot for your attention. Dirk Niemeier <dirk.niemeier@stueken.de> wrote in message news:38A7C03A.E1BE79FE@stueken.de... > Paulo, > first you should tell me what database-type you are using. The version we're using is Informix Dynamic Server (on UNIX) 7.20... > If you are using Informix Dynamic Server (IDS, IUS, etc) or above you should set > the isolation level to dirty read. But won't this lock the rows for other even for a select statement (executed for instance for presenting data on a paper report) executed by other users? Also, it would force us to change "by hand" the transaction isolation mode wich we are trying to avoid. On Delphi, each data-aware component used to fetch or send data to the server will need a TDatabase component wich sets (among other things) the "transaction isolation level". Of course that we can sen a "SET ISOLATION TO..." through that Database (using Passthrough SQL) but we are trying to avoid this to make things easier to the programming team (it's not difficult that someone forgets to put it back to COMMITED READ, ...). > But if you are using Informix Standard Engine (SE) (I think you do) you should > make clear if your database uses > locking or not.(start database command). I can only Add that we are not using ANSI databases on IDS... Thanks for your time, Paulo Silva Critical Software PORTUGAL