Re: Delphi 2005, Postgresql, ZEOS & optimistic locking

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 changes to 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 feature that has to be implemented with things like isolation level & database locking. These transactions have to be as short a 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 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

-----Message d'origine-----
De : Tony Caduto [mailto:tony_caduto(at)amsoftwaredesign(dot)com]
Envoyé : jeudi, 12. mai 2005 18:43
À : Philippe Lang
Cc : pgsql-general(at)postgresql(dot)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 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
>