Re: [sqlite] SQLite and nested transactions

[Dennis Cote]

Darren Duncan wrote:

I will clarify that child transactions are just an elegant way of 
partitioning a larger task, and that parent transactions always 
overrule children; even if a child transaction commits successfully, a 
rollback of its parent means there are no lasting changes.
Darren,

Because of this, and the fact that a transaction is basically a 
guarantee that all or none of the enclosed statements are executed, it 
is much simpler to implement nested transactions using a counter and the 
existing transaction API in a set of wrapper functions. There is no need 
to maintain all the intermediate state information. All you need to know 
is the current nesting level, and if any enclosed transaction was rolled 
back.

The following code shows the basic process.

   int transaction_level = 0;
   bool transaction_failed;

   void begin_nested_transaction()
   {
       if (transaction_level++ == 0) {
           transaction_failed = false;
           sqlite3_exec("begin");
       }
   }

   void commit_nested_transaction()
   {
       if (--transaction_level == 0)
           if (transaction_failed)
               sqlite3_exec("rollback");
           else
               sqlite3_exec("commit");
   }

   void rollback_nested_transaction()
   {
       transaction_failed = true;
       commit_transaction();
   }

HTH
Dennis Cote

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------