Database transactions - developer instructions

Database transactions - developer instructions

by Augusto C -
Number of replies: 2
I'm working on a Moodle installation on Centos, using MYSQL with a mysqli connection, supporting  transaction
I've tried to set up a database transaction block like this: (see "Delegated transactions",  https://docs.moodle.org/dev/Data_manipulation_API )

global $DB;
try {
     $transaction = $DB->start_delegated_transaction();
     // Insert a record
     $DB->insert_record('foo', $object);
     $DB->insert_record('bar', $otherobject);
 
     // Assuming the both inserts work, we get to the following line.
     $transaction->allow_commit();
} catch(\Exception $e) {
     $transaction->rollback($e);
}
After having enabled  databse debug mode ( $DB->set_debug(true); ), i tried to verify rollback system, generating an exception doin' a wrong insert on a table that does not exist!

An exception was effectively thrown, BUT I noticed that the followin' method was not reached at all:

1791      protected function rollback_transaction() {
1792          if (!$this->transactions_supported()) {
1793              return;
1794          }
1795  
1796          $sql = "ROLLBACK";
1797          $this->query_start($sql, NULL, SQL_QUERY_AUX);
1798          $result = $this->mysqli->query($sql);
1799          $this->query_end($result);
1800  
1801          return true;
1802      }
1803

Doin' some tests,  I've found that editing the "catch" block like this, all seems to work in the right way:

 catch(\Exception $e) {
     try{
            $transaction->rollback($e);
     }
     catch (Exception $e) {}
}
Should the manual be updated according to "this solution", or am I doin' something wrong?

I've made some searchs, few minutes ago, and it seems that even in "/lib/tests/messagelib_test.php" a try/catch block is used to call "$transaction->rollback($e);" method:
 692          try {
 693              $transaction->rollback(new Exception('ignore'));
 694          } catch (Exception $e) {
 695              $this->assertSame('ignore', $e->getMessage());
 696          }
Hope this could be useful for someone else needing to use "Database transactions"
Thanks
Average of ratings: -
In reply to Augusto C

Re: Database transactions - developer instructions

by Darko Miletić -

That was already discussed in this thread:

https://moodle.org/mod/forum/discuss.php?d=241661

Average of ratings: -
In reply to Darko Miletić

Re: Database transactions - developer instructions

by Augusto C -

Thanks Darko, i found the key in your quote:

"What is not obvious is that rollback throws another exception which is rather annoying."

That's the point, lookin' at the API, i found it not so clear. After some tests I came up, more or less, to your solution, but imho  it would be useful to add your example (that i'm reporting below) to the API docs, I think other people would appreciate it wink 

 
function testdb() {
    global $DB;

    try {
        try {
            $transaction = $DB->start_delegated_transaction();
            // Do something here.
            $transaction->allow_commit();
        } catch (Exception $e) {
            // Make sure transaction is valid.
            if (!empty($transaction) && !$transaction->is_disposed()) {
                $transaction->rollback($e);
            }
        }
    } catch (Exception $e) {
        // Silence the rollback exception or do something else.
    }

}
Average of ratings: -