Re: Delphi 2005, Postgresql, ZEOS & optimistic locking - Mailing list pgsql-general

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
>