Re: [sqlite] Transaction inside transaction

[Clemens Ladisch]

Igor Korot wrote:
> On Sun, Oct 21, 2012 at 11:01 PM, Darren Duncan  
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction inside transaction

[Igor Korot]

Darren,

On Sun, Oct 21, 2012 at 11:01 PM, Darren Duncan  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,  )
table2_slave( slave_id integer primary key, table1_master_id foreign
key,  )
table3_slave( slave_id integer primary key, table1_master_id foreign
key,  )

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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction inside transaction

[Darren Duncan]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction inside transaction

[Igor Korot]

Dan,

On Sun, Oct 21, 2012 at 9:40 PM, Dan Kennedy  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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction inside transaction

[Dan Kennedy]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users