Thread: A transaction in transaction? Possible?
Is it possible to have another transatction in a transaction??? In the following example the last ROLLBACK is totally ignored(transaction1).
//connect to database$database = dbConnect($dbhost, $dbuser, $dbpass, $dbname);
dbExec($database, "BEGIN"); //transaction1
//*
dbExec($database, "BEGIN");//transaction2
$sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143";
dbExec($database, $sql);
dbExec($database, "COMMIT");//transaction2
/**/
$sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143";
dbExec($database, $sql);
dbExec($database, "ROLLBACK");//transaction1
dbClose($database);
On Tue, Nov 09, 2004 at 10:47:06AM +0200, Andrei Bintintan wrote: > Is it possible to have another transatction in a transaction??? PostgreSQL 8.0 (currently in beta) has savepoints, so you'll be able to do this: BEGIN; UPDATE orders SET technikernotiz='51' WHERE id=16143; SAVEPOINT foo; UPDATE orders SET reklamationsdetail='51' WHERE id=16143; ROLLBACK TO foo; -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I thought nested transactions are available in the new
release (8) coming up.
Ted
--- Andrei Bintintan <klodoma@ar-sd.net> wrote:
> Is it possible to have another transatction in a
> transaction??? In the following example the last
> ROLLBACK is totally ignored(transaction1).
>
> //connect to database
> $database = dbConnect($dbhost, $dbuser, $dbpass,
> $dbname);
> dbExec($database, "BEGIN"); //transaction1
>
> //*
> dbExec($database, "BEGIN");//transaction2
> $sql = "UPDATE orders SET
> technikernotiz='51' WHERE id=16143";
> dbExec($database, $sql);
> dbExec($database, "COMMIT");//transaction2
> /**/
>
> $sql = "UPDATE orders SET
> reklamationsdetail='51' WHERE id=16143";
> dbExec($database, $sql);
> dbExec($database, "ROLLBACK");//transaction1
>
> dbClose($database);
>
>
>
__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com
On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote: > I thought nested transactions are available in the new > release (8) coming up. how to commit/rollback them ?
On Wed, Nov 10, 2004 at 09:23:02AM +0300, sad wrote:
> On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote:
> > I thought nested transactions are available in the new
> > release (8) coming up.
>
> how to commit/rollback them ?
CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
BEGIN;
INSERT INTO person (name) VALUES ('Tom');
SAVEPOINT foo;
INSERT INTO person (name) VALUES ('Dick');
ROLLBACK TO foo;
INSERT INTO person (name) VALUES ('Harry');
COMMIT;
SELECT * FROM person;id | name
----+------- 1 | Tom 3 | Harry
(2 rows)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Sorry, but I understand that your example is not really about nested
transactions, but about sequential transactions.
so, the primary question remains:
how to commit/rollback them ?
--- Michael Fuhr <__> wrote:
> On Wed, Nov 10, 2004 at 09:23:02AM +0300, sad wrote:
> > On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote:
> > > I thought nested transactions are available in the new
> > > release (8) coming up.
> >
> > how to commit/rollback them ?
>
> CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>
> BEGIN;
> INSERT INTO person (name) VALUES ('Tom');
> SAVEPOINT foo;
> INSERT INTO person (name) VALUES ('Dick');
> ROLLBACK TO foo;
> INSERT INTO person (name) VALUES ('Harry');
> COMMIT;
>
> SELECT * FROM person;
> id | name
> ----+-------
> 1 | Tom
> 3 | Harry
> (2 rows)
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
> Sorry, but I understand that your example is not really about nested
> transactions, but about sequential transactions.
Here's a more elaborate example. If this doesn't demonstrate the
capability you're looking for, then please provide an example of
what you'd like to do and describe the desired behavior.
CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
BEGIN; INSERT INTO person (name) VALUES ('Alice');
SAVEPOINT s1; INSERT INTO person (name) VALUES ('Bob');
SAVEPOINT s2; INSERT INTO person (name) VALUES ('Charles');
SAVEPOINT s3; INSERT INTO person (name) VALUES ('David'); ROLLBACK TO s3;
INSERT INTO person (name) VALUES ('Edward');ROLLBACK TO s2;
INSERT INTO person (name) VALUES ('Frank'); RELEASE s1;
INSERT INTO person (name) VALUES ('George');
COMMIT;
SELECT * FROM person;id | name
----+-------- 1 | Alice 2 | Bob 6 | Frank 7 | George
If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
id | name
----+--------- 1 | Alice 2 | Bob 3 | Charles 5 | Edward 6 | Frank 7 | George
If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
id | name
----+-------- 1 | Alice 7 | George
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
O Michael Fuhr έγραψε στις Nov 10, 2004 :
> On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
>
> > Sorry, but I understand that your example is not really about nested
> > transactions, but about sequential transactions.
>
> Here's a more elaborate example. If this doesn't demonstrate the
> capability you're looking for, then please provide an example of
> what you'd like to do and describe the desired behavior.
>
> CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>
> BEGIN;
> INSERT INTO person (name) VALUES ('Alice');
>
> SAVEPOINT s1;
> INSERT INTO person (name) VALUES ('Bob');
>
> SAVEPOINT s2;
> INSERT INTO person (name) VALUES ('Charles');
>
> SAVEPOINT s3;
> INSERT INTO person (name) VALUES ('David');
> ROLLBACK TO s3;
>
> INSERT INTO person (name) VALUES ('Edward');
> ROLLBACK TO s2;
>
> INSERT INTO person (name) VALUES ('Frank');
> RELEASE s1;
>
> INSERT INTO person (name) VALUES ('George');
> COMMIT;
Just a very naive thought....
Wouldn't make more sense to allow nested begin/commit/rollback blocks?
>
> SELECT * FROM person;
> id | name
> ----+--------
> 1 | Alice
> 2 | Bob
> 6 | Frank
> 7 | George
>
> If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
>
> id | name
> ----+---------
> 1 | Alice
> 2 | Bob
> 3 | Charles
> 5 | Edward
> 6 | Frank
> 7 | George
>
> If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
>
> id | name
> ----+--------
> 1 | Alice
> 7 | George
>
>
--
-Achilleus
Okay, I see you're speaking about pgsql 8.0
What about 7.4?
Andy.
----- Original Message -----
From: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
To: "Michael Fuhr" <mike@fuhr.org>
Cc: "Riccardo G. Facchini" <abief_ag_-postgresql@yahoo.com>;
<pgsql-sql@postgresql.org>; "Theodore Petrosky" <tedpet5@yahoo.com>; "Andrei
Bintintan" <klodoma@ar-sd.net>; "sad" <sad@bankir.ru>
Sent: Wednesday, November 10, 2004 12:58 PM
Subject: Re: [SQL] A transaction in transaction? Possible?
> O Michael Fuhr έγραψε στις Nov 10, 2004 :
>
> > On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
> >
> > > Sorry, but I understand that your example is not really about nested
> > > transactions, but about sequential transactions.
> >
> > Here's a more elaborate example. If this doesn't demonstrate the
> > capability you're looking for, then please provide an example of
> > what you'd like to do and describe the desired behavior.
> >
> > CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
> >
> > BEGIN;
> > INSERT INTO person (name) VALUES ('Alice');
> >
> > SAVEPOINT s1;
> > INSERT INTO person (name) VALUES ('Bob');
> >
> > SAVEPOINT s2;
> > INSERT INTO person (name) VALUES ('Charles');
> >
> > SAVEPOINT s3;
> > INSERT INTO person (name) VALUES ('David');
> > ROLLBACK TO s3;
> >
> > INSERT INTO person (name) VALUES ('Edward');
> > ROLLBACK TO s2;
> >
> > INSERT INTO person (name) VALUES ('Frank');
> > RELEASE s1;
> >
> > INSERT INTO person (name) VALUES ('George');
> > COMMIT;
>
> Just a very naive thought....
> Wouldn't make more sense to allow nested begin/commit/rollback blocks?
>
> >
> > SELECT * FROM person;
> > id | name
> > ----+--------
> > 1 | Alice
> > 2 | Bob
> > 6 | Frank
> > 7 | George
> >
> > If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
> >
> > id | name
> > ----+---------
> > 1 | Alice
> > 2 | Bob
> > 3 | Charles
> > 5 | Edward
> > 6 | Frank
> > 7 | George
> >
> > If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
> >
> > id | name
> > ----+--------
> > 1 | Alice
> > 7 | George
> >
> >
>
> --
> -Achilleus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
Achilleus Mantzios wrote: > Wouldn't make more sense to allow nested begin/commit/rollback > blocks? Possibly. But that consideration would have been more relevant about 6 years ago when they wrote the SAVEPOINT syntax into the SQL standard. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/
O Peter Eisentraut έγραψε στις Nov 10, 2004 : > Achilleus Mantzios wrote: > > Wouldn't make more sense to allow nested begin/commit/rollback > > blocks? > > Possibly. But that consideration would have been more relevant about 6 > years ago when they wrote the SAVEPOINT syntax into the SQL standard. > :) In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; can be replaced with SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. If only transactions weren't a requirement for SAVEPOINTs, what would we then need BEGIN; COMMIT; ROLLBACK; for? > > -- -Achilleus
Achilleus Mantzios wrote: > In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; > can be replaced with > SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. > > If only transactions weren't a requirement for SAVEPOINTs, > what would we then need BEGIN; COMMIT; ROLLBACK; for? Note that under the current arrangement, it doesn't make much sense to "commit" a subtransaction. It will be committed anyway when the main transactions commits, and you cannot commit it earlier because the main transaction could still roll back. So savepoint blocks are not really transactions, but more like semi-transactions. In other nested transaction models, things can be different. If you have so-called open nested transactions, which expose their results to other transactions already before the parent transaction commits, then a subtransaction commit is useful. But that behavior violates the isolation criterion of transactions and therefore needs additional facilities to behave tolerably. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> Just a very naive thought....
> Wouldn't make more sense to allow nested begin/commit/rollback blocks?
We actually had it working that way initially, but changed to the
spec-defined behavior, because (a) it wasn't standard, and (b) it
was confusing. See the pghackers archives.
regards, tom lane
On Wednesday, 10 November 2004 18:28, Tom Lane wrote: > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > Just a very naive thought.... > > Wouldn't make more sense to allow nested begin/commit/rollback blocks? > > We actually had it working that way initially, but changed to the > spec-defined behavior, because (a) it wasn't standard, and (b) it > was confusing. See the pghackers archives. We used to run into problems with nested transactions in scenarios like this: Imagine a database where you have a table for customers, and each customer can have (in a seperate table) several contacts; a contact can have one or more addresses, phone numbers, etc. These tables are connected by foreign keys, but without "on delete" triggers. The frontend application has a function for deleting a contact, which works something like this: * begin transaction * delete the contact's addresses, phone numbers, etc * ... * delete the contact record itself * commit Then there is a function for deleting a customer: * begin transaction * for all contacts, call the "delete contact" function * ... * delete the customer record itself * commit At the moment the application is "simulating" support for nested transactions: We use a wrapper for the BEGIN and COMMIT calls, and an internal counter, which is incremented for each BEGIN. Only the first BEGIN gets sent to the backend. When COMMIT has been called as many times as BEGIN, we send a real commit (errors and ROLLBACK are handled too, of course). It's not perfect, but it does what we need. Savepoints are a nice feature, but I don't think they could help us here. cheers, stefan
Stefan Weiss wrote:> On Wednesday, 10 November 2004 18:28, Tom Lane wrote:>>>Achilleus Mantzios <achill@matrix.gatewaynet.com>writes:>>>>>Just a very naive thought....>>>Wouldn't make more sense to allow nested begin/commit/rollbackblocks?>>>>We actually had it working that way initially, but changed to the>>spec-defined behavior,because (a) it wasn't standard, and (b) it>>was confusing. See the pghackers archives.>>> We used to run into problemswith nested transactions in scenarios> like this:>> Imagine a database where you have a table for customers, and>each customer can have (in a seperate table) several contacts; a> contact can have one or more addresses, phone numbers,etc. These> tables are connected by foreign keys, but without "on delete"> triggers. Why "without" ? Are you looking to solve a problem introduced by yourself ? > The frontend application has a function for deleting a contact,> which works something like this:>> * begin transaction> * delete the contact's addresses, phone numbers, etc> * ...> * delete the contact record itself> * commit>>Then there is a function for deleting a customer:>> * begin transaction> * for all contacts, call the "deletecontact" function> * ...> * delete the customer record itself> * commit>> At the moment the application is "simulating"support for nested> transactions: We use a wrapper for the BEGIN and COMMIT calls,> and an internal counter,which is incremented for each BEGIN.> Only the first BEGIN gets sent to the backend. When COMMIT has> been calledas many times as BEGIN, we send a real commit (errors> and ROLLBACK are handled too, of course).>> It's not perfect,but it does what we need. Savepoints are a nice> feature, but I don't think they could help us here. You can handle this task using the new functionality introduced with savepoint: the exception. For more information look at: http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Your delete customer can do: * BEGIN * for all contacts call delete contact * ... * EXCEPTION * handle your exception * END; * * delete the customer record itself Regards Gaetano Mendola
Achilleus Mantzios wrote:
> O Michael Fuhr έγραψε στις Nov 10, 2004 :
>
>
>>On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
>>
>>
>>>Sorry, but I understand that your example is not really about nested
>>>transactions, but about sequential transactions.
>>
>>Here's a more elaborate example. If this doesn't demonstrate the
>>capability you're looking for, then please provide an example of
>>what you'd like to do and describe the desired behavior.
>>
>>CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>>
>>BEGIN;
>> INSERT INTO person (name) VALUES ('Alice');
>>
>> SAVEPOINT s1;
>> INSERT INTO person (name) VALUES ('Bob');
>>
>> SAVEPOINT s2;
>> INSERT INTO person (name) VALUES ('Charles');
>>
>> SAVEPOINT s3;
>> INSERT INTO person (name) VALUES ('David');
>> ROLLBACK TO s3;
>>
>> INSERT INTO person (name) VALUES ('Edward');
>> ROLLBACK TO s2;
>>
>> INSERT INTO person (name) VALUES ('Frank');
>> RELEASE s1;
>>
>> INSERT INTO person (name) VALUES ('George');
>>COMMIT;
>
>
> Just a very naive thought....
> Wouldn't make more sense to allow nested begin/commit/rollback blocks?
Is not naive because in this way you can do what you could do with
nested begin/commit/rollback blocks, in this way you can do more.
Think for example in the example above to convert the "ROLLBACK TO s3"
in a "ROLLBACK TO s1", that is impossible to do with begin/commit/rollback blocks.
Regards
Gaetano Mendola
On Thursday, 11 November 2004 09:23, Gaetano Mendola wrote: > Stefan Weiss wrote: > > These tables are connected by foreign keys, but without "on delete" > > triggers. > > Why "without" ? Are you looking to solve a problem introduced by > yourself ? There are numerous checks involved before a customer (or a contact) can be deleted, and not all of these checks can be done by querying the database. Sometimes triggers aren't enough. > You can handle this task using the new functionality introduced with > savepoint: the exception. For more information look at: > http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures >.html#PLPGSQL-ERROR-TRAPPING The "delete contact" and "delete customer" functions are not stored procedures, so I'm not sure how this can help. thanks, stefan
Andrei Bintintan wrote: > //Is it possible to have another transatction in a transaction??? In the > following example the last ROLLBACK is totally ignored(transaction1). > > ///connect to database/ > $database = dbConnect($dbhost, $dbuser, $dbpass, $dbname); > dbExec($database, "BEGIN"); //transaction1 > > ///*/ > / / dbExec($database, "BEGIN");//transaction2 > $sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "COMMIT");//transaction2 > //**/ > / > $sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "ROLLBACK");//transaction1 > > dbClose($database); > > This appears to be the same as Oracle's "autonomous transactions", fwiw.