DB Transactions description for Strict Mode #6916

On looking further the issue is bigger than I thought. The documentation has the following description for "strict mode";
"When strict mode is enabled, if you are running multiple groups of transactions, if one group fails all groups will be rolled back."
That is not true as transaction will be committed when level 0 of the transaction is reached. As the code stands now the a query failure in or out of a transaction will cause all future transaction to fail. A query failure will have no effect on and transaction that has been committed.

Query faulure only effect previous queries is the transactions have been nested. Take a look at the following;
$this->db->trans_start();
//first queries
$this->db->trans_complete();
$this->db->trans_start();
//second queries
$this->db->trans_complete();
$this->db->trans_start();
//third queries
$this->db->trans_complete();
If the first queries complete it will be committed at the first trans_complete(). If the second queries do not complete the first transaction can not be rolled back as it has already been committed. The third queries will roll back only in "strict mode".

Look at a nested query;
$this->db->trans_start();
//some transaction
$this->db->trans_start();
//inside transaction
$this->db->trans_complete();
//last queries
$this->db->trans_complete();
In this case the commit only happens on the outside trans_complete(). Therefore all previous queries, after the first trans_start(), are rolled back. The trans_strict flag is meaningless as it only effects resetting on the outside trans_complete().

The issue comes in choosing where to reset the _trans_status flag. Currently it is set in trans_complete(). The problem with it being there is that queries before the transaction can set that flag to false and the transaction will fail.

I think it should be done in trans begin and the following line should be changed;
$this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
Should be ;
if ($this->trans_strict === FALSE)
{
$this->_trans_status = ($test_mode === TRUE) ? FALSE : TRUE ;
}
This resets the _trans_status flag on the outermost transaction with strict mode off and makes test_mode work. Currently test mode does not work as _trans_failure in not accessed anywhere. In the event that "strict mode" is on subsequent transactions will fail if any query fails. Right now any transaction fails if any previous query fails reguardless of "strict mode".

Originally posted by @jklovanc in bcit-ci/CodeIgniter#4026 (comment)