Thread: 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
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/ > From each according to his ability. To each according to his ability to litigate.