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

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