concurrent transactions inside the same connection
Hello hackers,
I have included below a fragment of a discussion from the freepascal mailing
list. Apparently, some folks expect to be able to start multiple transactions
inside the *same* database connection. Since this is not possible in postgres,
they would be forced to start more connections from the same application.
Is such a feature possible to be implemented someday ?
My feeling is that this is a false problem, caused by some interbase/firebird
or delphi habits . But ... the poster claims that Oracle and SqlServer
have such a feature ( Oracle has 'named' transactions - but these
names seem to be just labels ) - is this true ?
Best wishes,
Adrian Maier
---------- Forwarded message ----------
From: Michael Van Canneyt <michael@freepascal.org>
Subject: Re: [lazarus] FPC question
To: lazarus@miraclec.com
On Wed, 19 Apr 2006, Adrian Maier wrote:
On 4/19/06, Joost van der Sluis <joost@cnoc.nl> wrote:
Is this a problem with PostgreSQL itself or the component in lazarus
wrapping it?It's a problem of postgres. A transaction 'block' is started with the
sql-command 'begin', from that comand on, all queries are executed
within that transaction. Using another connection is not possible.
Unless, offcourse, you start a new connection. Or closes the transaction
(commit, rollback etc)Please pardon me for jumping in the middle of the thread, but your
phrase made me really curious. The behaviour you described seems
to be the normal one, once you execute "begin" (transaction) . What
other behaviour would someone expect postgres to have ?
To allow several concurrent transactions.
Interbase/Firebird allows to start several concurrent transactions in
1 connection. AFAIK Oracle and MS-SQL too (named transactions).
This is extremely handy.
Michael.
I have included below a fragment of a discussion from the freepascal
mailing
list. Apparently, some folks expect to be able to start multiple
transactions
[...]
inside the *same* database connection.
My feeling is that this is a false problem, caused by some
interbase/firebird
or delphi habits . But ... the poster claims that Oracle and
SqlServer
have such a feature ( Oracle has 'named' transactions - but these
names seem to be just labels ) - is this true ?
I can only speak for Oracle:
Transaction names are just labels, that is correct.
There is, however, something called an 'autonomous transaction' in
PL/SQL
stored procedures and triggers. A stored procedure can start a new
transaction,
independent from the current context.
Once you return from the stored procedure, the old transaction is
resumed.
Yours,
Laurenz Albe
Import Notes
Resolved by subject fallback
On Wed, Apr 19, 2006 at 12:31:23PM +0300, Adrian Maier wrote:
Hello hackers,
I have included below a fragment of a discussion from the freepascal mailing
list. Apparently, some folks expect to be able to start multiple transactions
inside the *same* database connection. Since this is not possible in postgres,
they would be forced to start more connections from the same application.Is such a feature possible to be implemented someday ?
It's not clear what the use case is. Given the original problem was
excised from the email it's not clear what they're trying to solve.
Transactions can "nest", sort of.
My feeling is that this is a false problem, caused by some interbase/firebird
or delphi habits . But ... the poster claims that Oracle and SqlServer
have such a feature ( Oracle has 'named' transactions - but these
names seem to be just labels ) - is this true ?
I beleive oracle can have independant subtransactions. That's something
different from several concurrent transaction though. I'm not oracle
expert though.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.