Transaction inside transaction

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Transaction inside transaction

Igor Korot
Hi, ALL,
Is it possible to have transaction inside transaction? Will it be
handled correctly?

What I mean is: crate transaction 1 by issuing "BEGIN", create
transaction 2 by issuing "BEGIN".
Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
If "COMMIT" is issued, continue transaction 1.

Thank you.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Transaction inside transaction

Dan Kennedy-4
On 10/22/2012 11:34 AM, Igor Korot wrote:
> Hi, ALL,
> Is it possible to have transaction inside transaction? Will it be
> handled correctly?
>
> What I mean is: crate transaction 1 by issuing "BEGIN", create
> transaction 2 by issuing "BEGIN".
> Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
> If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
> If "COMMIT" is issued, continue transaction 1.

See here:

   http://www.sqlite.org/lang_savepoint.html

They don't work exactly as you describe above, but might be
close enough.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Transaction inside transaction

Igor Korot
Dan,

On Sun, Oct 21, 2012 at 9:40 PM, Dan Kennedy <[hidden email]> wrote:

> On 10/22/2012 11:34 AM, Igor Korot wrote:
>>
>> Hi, ALL,
>> Is it possible to have transaction inside transaction? Will it be
>> handled correctly?
>>
>> What I mean is: crate transaction 1 by issuing "BEGIN", create
>> transaction 2 by issuing "BEGIN".
>> Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
>> If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
>> If "COMMIT" is issued, continue transaction 1.
>
>
> See here:
>
>   http://www.sqlite.org/lang_savepoint.html
>
> They don't work exactly as you describe above, but might be
> close enough.

Looks like this is what I need.
However I am not sure why you said "They don't work exactly as described..."

Thank you.

>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Transaction inside transaction

Darren Duncan
In reply to this post by Igor Korot
Igor Korot wrote:

> Hi, ALL,
> Is it possible to have transaction inside transaction? Will it be
> handled correctly?
>
> What I mean is: crate transaction 1 by issuing "BEGIN", create
> transaction 2 by issuing "BEGIN".
> Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
> If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
> If "COMMIT" is issued, continue transaction 1.
>
> Thank you.

Didn't you mean to say, "regardless of how (inner) transaction 2 ends, continue
transaction 1"?

Being able to rollback part of a main transaction without rolling back the whole
thing is the whole point of nested transactions.  (Likewise for "savepoints",
which are different syntax but the same functionality.)

If you want a rollback on the inner to rollback the outer unconditionally, you
never needed a nested transaction in the first place, but rather just the one
outer transaction 1, or you might have wanted an autonomous transaction, which
is different, depending on what you want.

-- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Transaction inside transaction

Igor Korot
Darren,

On Sun, Oct 21, 2012 at 11:01 PM, Darren Duncan <[hidden email]> wrote:

> Igor Korot wrote:
>>
>> Hi, ALL,
>> Is it possible to have transaction inside transaction? Will it be
>> handled correctly?
>>
>> What I mean is: crate transaction 1 by issuing "BEGIN", create
>> transaction 2 by issuing "BEGIN".
>> Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
>> If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
>> If "COMMIT" is issued, continue transaction 1.
>>
>> Thank you.
>
>
> Didn't you mean to say, "regardless of how (inner) transaction 2 ends,
> continue transaction 1"?
>
> Being able to rollback part of a main transaction without rolling back the
> whole thing is the whole point of nested transactions.  (Likewise for
> "savepoints", which are different syntax but the same functionality.)
>
> If you want a rollback on the inner to rollback the outer unconditionally,
> you never needed a nested transaction in the first place, but rather just
> the one outer transaction 1, or you might have wanted an autonomous
> transaction, which is different, depending on what you want.

OK, consider the following scenario:

table1_master( id integer primary key, <more fields follows> )
table2_slave( slave_id integer primary key, table1_master_id foreign
key, <more fields follows> )
table3_slave( slave_id integer primary key, table1_master_id foreign
key, <more fields follows> )

With an insert everything is OK. I have one transaction starting from
bottom up to satisfy foreign key constraints.
But if I want to edit the data my algorithm is:

1. Query all tables to grab the data according to table1_master.id
2. Present the data on screen and let the user edit the data. Delete
the data retrieved in step 1.
3. Grab all the data in the appropriate class.
4. Start transaction 1 to insert the data.
5. Start transaction 2 to delete the data from tableN_slave
6. If 5 fails, rollback transaction 2, then transaction 1 (i.e. don't
go with tr.1)
7. If 5 succeeds continue with transaction 1.

What would be the best approach from sqlite point of view?

Thank you.

>
> -- Darren Duncan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Transaction inside transaction

Clemens Ladisch
Igor Korot wrote:

> On Sun, Oct 21, 2012 at 11:01 PM, Darren Duncan <[hidden email]> wrote:
>> If you want a rollback on the inner to rollback the outer unconditionally,
>> you never needed a nested transaction in the first place, but rather just
>> the one outer transaction 1,
>
> 1. Query all tables to grab the data according to table1_master.id
> 2. Present the data on screen and let the user edit the data. Delete
> the data retrieved in step 1.
> 3. Grab all the data in the appropriate class.
> 4. Start transaction 1 to insert the data.
> 5. Start transaction 2 to delete the data from tableN_slave
> 6. If 5 fails, rollback transaction 2, then transaction 1 (i.e. don't
> go with tr.1)
> 7. If 5 succeeds continue with transaction 1.
>
> What would be the best approach from sqlite point of view?

As Darren said, just do nothing for the inner transaction:
1. Query all tables to grab the data according to table1_master.id
2. Present the data on screen and let the user edit the data. Delete
   the data retrieved in step 1.
3. Grab all the data in the appropriate class.
4. Start transaction 1 to insert the data.
5. delete the data from tableN_slave
6. If 5 fails, rollback transaction 1 (i.e. don't go with tr.1)
7. If 5 succeeds continue with transaction 1.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users