Re: [Gnumed-devel] what about commit?

[Horst Herb]

On Tue, 23 Sep 2003 11:55, syan tan wrote:
> What does that mean " real concurrent atomic transactions inside of the
> scope of a single client connection" ?
> With one connection, I didn't think you could have more than one
> transaction ( that's why the commit() operation is
> on the connection object).  If  one wants more than one connection
> running concurrently, don't you just get a new
> connection object?

As I mentioned before, establishing a new connection is expensive in terms of 
server ressources. It slows the client down. You don't want any client having 
more than 2-3 open connections at any time.

This is why we pool connections in gmPG. gmPG has the potential of 
transparently keeping a few connections open and dishing them out to the 
process that requests one on a "as needed" basis.

Maybe 90% of database access is read-only. Read-only queries can't crash any 
concurrent transactions within the same connection, hence gmPG will open only 
one single read-only connection per client and share it - this resulted in a 
major speedup (we are talking several seconds!)

Now, if you do the same with writeable connections, let's say one process 
requests a cursor, and does something:
cur1 = con. cursor()
cur1.execute....
Without knowing this, another subroutine requests another cursor from the same 
connection:
cur2 = con.cursor()
cur2.execute ....
You will get no error message - the API allows to do this for a number of good 
and not-so-good reasons.

As you already found out, cur1 and cur2 can rollback or commit each other, 
which is not what we want. Hence, if you want a writeable connection, gmPG 
takes care of it and gives you a dedicated one if you request one.

Just in order to enforce transactional safety (in the case some smartass 
accesses a connection directly instead of going via gmPG) we have built in 
the "safety lock" of the pseudo user name for this. Of course, you still can 
do stupid things, but you have to be absolutely deliberate about this and you 
will know that you are about to do something stupid.