Thread: Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi,
I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1, and I have a question:
How do you implement an optimistic locking strategy with these tools? With an Access front-end, and the ODBC driver,
thisis completely transparent. A test showed me that the Delphi client writes to the database without worrying about
anotheruser doing that meanwhile...
I saw it's possible to manipulate the isolation level (read commited or serializable only) in the ZEOS controls, but it
doesnot help at all here. An optimistic lock is a kind of "long transaction" for me.
Thanks for your time!
Philippe
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Arthur Hoogervorst
Envoyé : lundi, 9. mai 2005 12:46
À : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Adventures in Quest for GUI RAD
Hi,
The company I work for actually uses the Zeos lib/Postgres extensively to track the shipping and sales side for almost
3years.
We're still running on a 7.2/7.4 Postgres database, because I haven't been convinced yet to either update or upgrade to
8.x.x.I'm curious if others have successfully moved their (production) database successfully to Postgres 8.0.
Regards,
Arthur
On 5/9/05, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
> Hi,
>
> I'm testing Delphi 2005 at the moment, with ZEOS Lib (libpq), and I have to say it work fine, as Tony mentioned. I
havea few questions:
>
> 1) I'm curious: are there a lot of big projects using ZEOS with PG or is that technology still relatively new? I
wouldlike to use it a replacement for ODBC, but I have no experience regarding its stability.
>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Hi Philippe,
It still works the same way as the ODBC driver, because of Postgresql's multi version concurrency.
Zeos uses libpq exactly like the ODBC driver does, except it talks directly to libpq without the overhead of ODBC, and
all
you have to deploy with your app is the super small libpq.dll.
You really never have to worry about locks.
However if you want to do a bunch of commands in the context of a long transaction you need to pick one of the
isolationlevels
like read commited and then in your code do something like this:
with myconnection do
begin
Myconnection.connection.StartTransaction;
try
sql.add('insert into sometable (field1) VALUES ('bla')');
execsql;
//do some more operations in the same transaction
sql.clear;
sql.add('select * from sometable');
open;
Myconnection.commit;
except
//if a error occurs rollback everything we did in the transaction
Myconnection.connection.Rollback;
end;
end;
When ever I use Zeos I always set the isolation level to tiNone and let the server handle the transactions.
When you use tiNone you simply do all your statements in one operation, just do a bunch of adds and seperate each
statement with a semi colon, then do the execsql. All the statements will be executed in a single transaction by the
server and if a error occurs they all get rolled back.
Hope this helps you out.
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
Philippe Lang wrote:
> Hi,
>
> I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1, and I have a question:
>
> How do you implement an optimistic locking strategy with these tools? With an Access front-end, and the ODBC driver,
thisis completely transparent. A test showed me that the Delphi client writes to the database without worrying about
anotheruser doing that meanwhile...
>
> I saw it's possible to manipulate the isolation level (read commited or serializable only) in the ZEOS controls, but
itdoes not help at all here. An optimistic lock is a kind of "long transaction" for me.
>
> Thanks for your time!
>
> Philippe
>
Hi,
Thanks for your answer, but I'm not sure we are talking about the same thing. What I was used to with MS
Access/ODBC/Postgresql,in a multiuser network scenario, is the following:
- User A loads data in a form
- User B loads the same data in a form.
- Before user A makes changes, B makes his changes, and commits them.
- User A makes changes, and tries to commit them, but... the software shouts, because meanwhile, someone else made
changesto the data loaded in the form.
That's what I call optimistic locking.
Between each step, you can easily imagine a duration of several minutes, so from my point of view, this is not a
featurethat has to be implemented with things like isolation level & database locking. These transactions have to be as
shorta possible, right?
Until now, I was not able to reproduce optimistic locking with Delphi/ZEOS.
With Dephi/BDE/ODBC, this is different: as soon as you try updating a field that has been modified by someone else
meanwhile,the field is automatically updated for you before you start making your own changes, and of course before you
tryto commit them. That's fine too. I would have preferred an error personnally.
Is there a way to do the same with ZEOS? Or maybe is there another mecanism that could be used to do optimistic
locking?Some kind of "long transactions", in the database server?
Philippe Lang
-----Message d'origine-----
De : Tony Caduto [mailto:tony_caduto@amsoftwaredesign.com]
Envoyé : jeudi, 12. mai 2005 18:43
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi Philippe,
It still works the same way as the ODBC driver, because of Postgresql's multi version concurrency.
Zeos uses libpq exactly like the ODBC driver does, except it talks directly to libpq without the overhead of ODBC, and
allyou have to deploy with your app is the super small libpq.dll.
You really never have to worry about locks.
However if you want to do a bunch of commands in the context of a long transaction you need to pick one of the
isolationlevels like read commited and then in your code do something like this:
with myconnection do
begin
Myconnection.connection.StartTransaction;
try
sql.add('insert into sometable (field1) VALUES ('bla')');
execsql;
//do some more operations in the same transaction
sql.clear;
sql.add('select * from sometable');
open;
Myconnection.commit;
except
//if a error occurs rollback everything we did in the transaction
Myconnection.connection.Rollback;
end;
end;
When ever I use Zeos I always set the isolation level to tiNone and let the server handle the transactions.
When you use tiNone you simply do all your statements in one operation, just do a bunch of adds and seperate each
statementwith a semi colon, then do the execsql. All the statements will be executed in a single transaction by the
serverand if a error occurs they all get rolled back.
Hope this helps you out.
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x http://www.amsoftwaredesign.com
Philippe Lang wrote:
> Hi,
>
> I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1, and I have a question:
>
> How do you implement an optimistic locking strategy with these tools? With an Access front-end, and the ODBC driver,
thisis completely transparent. A test showed me that the Delphi client writes to the database without worrying about
anotheruser doing that meanwhile...
>
> I saw it's possible to manipulate the isolation level (read commited or serializable only) in the ZEOS controls, but
itdoes not help at all here. An optimistic lock is a kind of "long transaction" for me.
>
> Thanks for your time!
>
> Philippe
>
Why not just add a onenter event handler to the forms field in question and just check the data before they edit it. If it's different update the field with the most current data. Access probably just does something similar under the hood for you. I don't think that's a feature of the ODBC driver or is it? If it is you could always use the ODBC driver from Delphi as well. > > With Dephi/BDE/ODBC, this is different: as soon as you try updating a field that has been modified by someone else meanwhile,the field is automatically updated for you before you start making your own changes, and of course before you tryto commit them. That's fine too. I would have preferred an error personnally. > > Is there a way to do the same with ZEOS? Or maybe is there another mecanism that could be used to do optimistic locking?Some kind of "long transactions", in the database server? > > Philippe Lang >
Hi!
On Thu, 12 May 2005 19:19:10 +0200
"Philippe Lang" <philippe.lang@attiksystem.ch> wrote:
> Thanks for your answer, but I'm not sure we are talking about the
> same thing. What I was used to with MS Access/ODBC/Postgresql, in a
multiuser network scenario, is the following:
>
> - User A loads data in a form
> - User B loads the same data in a form.
> - Before user A makes changes, B makes his changes, and commits them.
> - User A makes changes, and tries to commit them, but... the software
> shouts, because meanwhile, someone else made changes to the data
> loaded in the form.
>
> That's what I call optimistic locking.
[...]
> Is there a way to do the same with ZEOS? Or maybe is there another
> mecanism that could be used to do optimistic locking? Some kind of
> "long transactions", in the database server?
You could include the old values of columns which could change in the
where clause of your update statement. For example:
UPDATE mytable SET col1 = 'newValue'
WHERE col1 = 'oldValue1' AND col2 = 'oldValue2';
This way, whenever a row has changed (col1 or col2 have different
values), this update will fail (i.e. no row gets updated).
Ralf Schuchardt
> Hi Philippe,
>
> It still works the same way as the ODBC driver, because of
> Postgresql's multi version concurrency. Zeos uses libpq exactly like
> the ODBC driver does, except it talks directly to libpq without the
> overhead of ODBC, and all you have to deploy with your app is the
> super small libpq.dll.
>
> You really never have to worry about locks.
> However if you want to do a bunch of commands in the context of a
> long transaction you need to pick one of the isolation levels like
> read commited and then in your code do something like this:
>
> with myconnection do
> begin
> Myconnection.connection.StartTransaction;
> try
> sql.add('insert into
> sometable (field1) VALUES ('bla')'); execsql;
> //do some more operations in the
> same transaction sql.clear;
> sql.add('select * from sometable');
> open;
>
> Myconnection.commit;
> except
> //if a error occurs rollback
> everything we did in the transaction Myconnection.connection.Rollback;
> end;
> end;
>
>
> When ever I use Zeos I always set the isolation level to tiNone and
> let the server handle the transactions. When you use tiNone you
> simply do all your statements in one operation, just do a bunch of
> adds and seperate each statement with a semi colon, then do the
> execsql. All the statements will be executed in a single transaction
> by the server and if a error occurs they all get rolled back.
>
> Hope this helps you out.
>
> --
> Tony Caduto
> AM Software Design
> Home of PG Lightning Admin for Postgresql 8.x
> http://www.amsoftwaredesign.com
> Philippe Lang wrote:
> > Hi,
> >
> > I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS
> > Lib 6.5.1, and I have a question:
> >
> > How do you implement an optimistic locking strategy with these
> > tools? With an Access front-end, and the ODBC driver, this is
> > completely transparent. A test showed me that the Delphi client
> > writes to the database without worrying about another user doing
> > that meanwhile...
> >
> > I saw it's possible to manipulate the isolation level (read
> > commited or serializable only) in the ZEOS controls, but it does
> > not help at all here. An optimistic lock is a kind of "long
> > transaction" for me.
> >
> > Thanks for your time!
> >
> > Philippe
Hi, Yes, I'm sure this kind of feature is application-based. There is nothing in the driver itself. If I'm not wrong, MS Accessuses a timestamp column to check if the record was updated meanwhile, or the column values if not timestamp is available.This might be true only with a MS SQL Database, though. I'll try doing something similar in a Delphi event-handler. Thanks, bye Philippe -----Message d'origine----- De : Tony Caduto [mailto:tony_caduto@amsoftwaredesign.com] Envoyé : jeudi, 12. mai 2005 21:07 À : Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking Why not just add a onenter event handler to the forms field in question and just check the data before they edit it. If it's different update the field with the most current data. Access probably just does something similar under the hood for you. I don't think that's a feature of the ODBC driver or is it? If it is you could always use the ODBC driver from Delphi as well. > > With Dephi/BDE/ODBC, this is different: as soon as you try updating a field that has been modified by someone else meanwhile,the field is automatically updated for you before you start making your own changes, and of course before you tryto commit them. That's fine too. I would have preferred an error personnally. > > Is there a way to do the same with ZEOS? Or maybe is there another mecanism that could be used to do optimistic locking?Some kind of "long transactions", in the database server? > > Philippe Lang >
You could also open a transaction and SELECT FOR UPDATE. So a second transaction can't Select the same data for update. That works fine. (You can try in with pgsql too). Daniel Philippe Lang schrieb: >Hi, > >I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1, and I have a question: > >How do you implement an optimistic locking strategy with these tools? With an Access front-end, and the ODBC driver, thisis completely transparent. A test showed me that the Delphi client writes to the database without worrying about anotheruser doing that meanwhile... > >I saw it's possible to manipulate the isolation level (read commited or serializable only) in the ZEOS controls, but itdoes not help at all here. An optimistic lock is a kind of "long transaction" for me. > >Thanks for your time! > >Philippe > > >-----Message d'origine----- >De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Arthur Hoogervorst >Envoyé : lundi, 9. mai 2005 12:46 >À : pgsql-general@postgresql.org >Objet : Re: [GENERAL] Adventures in Quest for GUI RAD > >Hi, > >The company I work for actually uses the Zeos lib/Postgres extensively to track the shipping and sales side for almost 3years. > >We're still running on a 7.2/7.4 Postgres database, because I haven't been convinced yet to either update or upgrade to8.x.x. I'm curious if others have successfully moved their (production) database successfully to Postgres 8.0. > > >Regards, > > >Arthur > >On 5/9/05, Philippe Lang <philippe.lang@attiksystem.ch> wrote: > > >>Hi, >> >>I'm testing Delphi 2005 at the moment, with ZEOS Lib (libpq), and I have to say it work fine, as Tony mentioned. I havea few questions: >> >>1) I'm curious: are there a lot of big projects using ZEOS with PG or is that technology still relatively new? I wouldlike to use it a replacement for ODBC, but I have no experience regarding its stability. >> >> >> > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > >
""Philippe Lang"" <philippe.lang@attiksystem.ch> wrote in message news:6C0CF58A187DA5479245E0830AF84F42143306@poweredge.attiksystem.ch... > Hi, > > Yes, I'm sure this kind of feature is application-based. There is nothing > in the driver itself. If I'm not wrong, MS Access uses a timestamp column > to check if the record was updated meanwhile, or the column values if not > timestamp is available. This might be true only with a MS SQL Database, > though. My understanding is that MS Access uses the row's ctid value to check uniqueness, on the basis that an updated row (even if all the data remains the same) will have a new ctid. This value is provided via the driver, where the "row versioning" option is set to true. Otherwise, Access will check the value of every data field, which can produce painful results. So, the short answer is, you could just check the ctid value for the row you are about to update. > > I'll try doing something similar in a Delphi event-handler. > > Thanks, bye > > Philippe > > -----Message d'origine----- > De : Tony Caduto [mailto:tony_caduto@amsoftwaredesign.com] > Envoy� : jeudi, 12. mai 2005 21:07 > � : Philippe Lang > Cc : pgsql-general@postgresql.org > Objet : Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking > > Why not just add a onenter event handler to the forms field in question > and just check the data before they edit it. > If it's different update the field with the most current data. > > > Access probably just does something similar under the hood for you. > I don't think that's a feature of the ODBC driver or is it? > > If it is you could always use the ODBC driver from Delphi as well. > >> >> With Dephi/BDE/ODBC, this is different: as soon as you try updating a >> field that has been modified by someone else meanwhile, the field is >> automatically updated for you before you start making your own changes, >> and of course before you try to commit them. That's fine too. I would >> have preferred an error personnally. >> >> Is there a way to do the same with ZEOS? Or maybe is there another >> mecanism that could be used to do optimistic locking? Some kind of "long >> transactions", in the database server? >> >> Philippe Lang >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >