Re: Delphi 2005, Postgresql, ZEOS & optimistic locking

[Tony Caduto <tony_caduto@xxxxxxxxxxxxxxxxxxxx>]

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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org