Skip site navigation (1) Skip section navigation (2)

Re: Nested transactions

From: Barry Lind <blind(at)xythos(dot)com>
To: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>,Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Nested transactions
Date: 2004-06-18 02:13:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbcpgsql-patches
The other thing that I have been meaning to say in this thread is that I 
don't like using COMMIT to mean subtransaction commit (vs. introducing a 
new command for it) because of the following situation.

Lets say that I have a java method that takes a jdbc connection and this 
  code starts a transaction and does some work then issues a commit to 
ensure the changes have been committed to the database, and then does 
some other work outside the database based on the fact that the commit 
was sucessfull and it therefore knows the data is saved to disk (i.e. 
send out an email notification, or any number of other non-database tasks).

Now lets suppose that someone calls this method with a database 
connection that already has a transaction started, so that this method 
really is beginning and working with a sub-transaction.  Now when it 
commits it doesn't know if the changes will ever get to disk since its 
commit could be rolled back later.  So this code gets broken.

I like the functionality of nested transactions, I just think that there 
needs to be different commands other than BEGIN/COMMIT to work with 
them.  So that there is no possiblity for misunderstanding what COMMIT 
really means.


Alvaro Herrera wrote:

> On Wed, Jun 16, 2004 at 11:45:36PM +0100, Simon Riggs wrote:
>>The patch looks impressively technical, but overall I'm not exactly sure
>>what it does...I guess I'm just not clear why I would want it, except as
>>the main technical pre-work to later syntax changes. I'm sure some short
>>explanations would clear that up for me very quickly... :)
> Right.  I have never intended to be implementing a known SQL standard
> feature.  What I'm doing is allowing the whole backend to go back to a
> know state after an error is encountered.
> With this in place, implementing SAVEPOINTs the way SQL expects them to
> work appears to be a very trivial exercise.
>>Perhaps what I've just asked about is trivial icing on the cake you've
>>just baked,
> I think this phrase very precisely describes it.  At least, that's what
> I expect.
> You may not see it, but a savepoint is just the start of a nested
> transaction in disguise.  Consider:
> begin;
> 	insert into foo values (1);
> 	savepoint dammit;
> 	insert into foo values (2);
> 	select foo;		-- fails
> 	rollback to dammit;
> 	insert into foo values (3);
> commit;
> You expect the transaction to finish with tuples 1 and 3 in table foo,
> right?  Well, this is exactly the same as
> begin;
> 	insert into foo values (1);
> 	begin;		-- dammit
> 		insert into foo values (2);
> 		select foo;	-- fails, goes to aborted state
> 	rollback;
> 	insert into foo values (3);
> commit;
> So all that's needed for the former to work is to be able to define a
> "name" for a transaction (using a cute syntax) and being able to
> rollback to it.  Definitely trivial, after all the work I have put into
> making the latter work.
> In extant releases you can only do this:
> begin;
> 	insert into foo values (1);
> 	insert into foo values (2);
> 	select foo;		-- oops, can't go back!
> rollback;
> begin;
> 	insert into foo values (1);
> 	insert into foo values (3);
> commit;
> You are forced to send all the commands before the aborting one to the
> server again.  And there's no way to "undo" a command in the
> transaction, short of aborting it completely.
> I don't know what Oracle or other DBMSs expect in this area.  Anyone
> care to give me a few pointers?  If I'm missing something, I want to
> know as soon as possible.

In response to


pgsql-patches by date

Next:From: Bruce MomjianDate: 2004-06-18 04:56:14
Subject: Re: Tablespace patch review
Previous:From: Alvaro HerreraDate: 2004-06-18 01:33:28
Subject: nested xacts and phantom Xids

pgsql-jdbc by date

Next:From: Jie LiangDate: 2004-06-18 03:09:16
Subject: Re: Prepare Statement
Previous:From: Roedy GreenDate: 2004-06-18 00:10:37
Subject: Re: Portable Java Data Layer based on JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group