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.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster