Design defect and Solution _php instance of CodeIgniter Framework database transaction processing

Cause:

In one of our lines of business, using the older version of the CodeIgniter framework, which has a design flaw in the DB class, is probably not a flaw. But he has affected our production environment, resulting in a chain reaction. has a greater impact on the business and is not easy to troubleshoot. This question, I in the middle of March this year, once to CodeIgniter China stationmaster Hex Report, after, I also forgot this matter. Until today, our online business once again thought that this problem, the harm I again the investigation. The specific reason, everybody listens to me to finish slowly first. (This problem is also present in the latest version 2.1.0)

Analysis:

Take the CodeIgniter frame version 2.1.0 as an example, there is a $_trans_status attribute in line 58th of the system\database\db_driver.php Ci_db_driver class.

Copy Code code as follows:

system\database\db_driver.php
var $trans _strict = TRUE;
var $_trans_depth = 0;
var $_trans_status = TRUE; Used with transactions to determine if a rollback should occur
var $cache _on = FALSE;

Also, in the query method for this class, there is the code that assigns this attribute, see file 306, line 307

Copy Code code as follows:

This is trigger a rollback if transactions are being used
$this->_trans_status = FALSE;

This also gives a note that tells us that if you use something, this property becomes a decision condition for a rollback.

In the 520 line of things to submit method Trans_complete, the following code:

Copy Code code as follows:

/**
* Complete Transaction
*
* @access Public
* @return BOOL
*/
function Trans_complete ()
{
if (! $this->trans_enabled)
{
return FALSE;
}

When transactions are nested we only begin/commit/rollback the outermost ones
if ($this->_trans_depth > 1)
{
$this->_trans_depth-= 1;
return TRUE;
}

The query () function would set this flag to FALSE in the event that a query failed
if ($this->_trans_status = = FALSE)
{
$this->trans_rollback ();

If we are not running in strict mode, we'll reset
The _TRANS_STATUS flag so subsequent groups of transactions
would be permitted.
if ($this->trans_strict = = FALSE)
{
$this->_trans_status = TRUE;
}

Log_message (' Debug ', ' DB Transaction failure ');
return FALSE;
}

$this->trans_commit ();
return TRUE;
}

In line 535, if the _trans_status property is False, the rollback will occur and false is returned.

In our business code, the programmer inadvertently tells the user that the operation was successful by neglecting to determine whether the Trans_complete () method was properly executed, but in fact, the program has issued a rollback directive to DB, and the DB record has not been successfully updated. When the user performs the next step, the program finds that the record has not been updated and notifies the user that the previous operation has not been completed, notifying the user that it is being executed again. So repeatedly ...

The process of troubleshooting is also very interesting, initially from the PHP code, always can not determine the problem, and did not focus on the Trans_complete () method of return. It was not until later Strace grabbed the analysis that it was known that the rollback was caused by this attribute.

Copy Code code as follows:

22:54:08.380085 Write (9, "_\0\0\0\3update ' cfc4n_user_info ' SET ' cfc4n_user_lock ' = 1\nwhere ' cfc4n_user_id ' = \ ' 6154\ ' \ NAND ' cfc4n_user_lock ' = 0 ", 99) = 99//Execute UPDATE command
22:54:08.380089 Read (9, ": \0\0\1\377\36\4#42s22unknown column \ ' cfc4n_user_lock\ ' in \ ' where clause\ '", 16384) = 62//No There are fields, SQL execution error
22:54:08.381791 Write (9, "\21\0\0\0\3set autocommit=0", 21) = 21//Prohibit automatic submission
22:54:08.381891 Read (9, "\7\0\0\1\0\0\0\0\0\0\0", 16384) = 11
22:54:08.382186 poll ([{fd=9, events=pollin| POLLPRI}], 1, 0) = 0
22:54:08.382258 Write (9, "\v\0\0\0\2jv01_roles", 15) = 15
22:54:08.382343 Read (9, "\7\0\0\1\0\0\0\0\0\0\0", 16384) = 11
22:54:08.382631 poll ([{fd=9, events=pollin| POLLPRI}], 1, 0) = 0
22:54:08.382703 Write (9, "\22\0\0\0\3start TRANSACTION", 22) = 22//Start transaction processing
22:54:08.401954 Write (9, "\v\0\0\0\2database_demo", 15) = 15
22:54:08.402043 Read (9, "\7\0\0\1\0\0\0\1\0\1\0", 16384) = 11
22:54:08.417773 Write (9, "\v\0\0\0\2database_demo", 15) = 15
22:54:08.417872 Read (9, "\7\0\0\1\0\0\0\1\0\0\0", 16384) = 11
22:54:08.418256 Write (9, "[\0\0\0\3update ' Cfc4n_user_info ' SET ' silver ' = CAST (silver + (5) as signed) \nwhere ' Cfc4n_us" er_id ' = \ ' 6154\ ' ", 95) = 95//execute other SQL statements
22:54:08.418363 Read (9, "0\0\0\1\0\1\0\1\0\0\0 (Rows matched:1 changed:1 warnings:0", 16384) = 52///successfully updated, affecting the number of bars 1.
22:54:08.430212 Write (9, "\v\0\0\0\2database_demo", 15) = 15
22:54:08.430314 Read (9, "\7\0\0\1\0\0\0\1\0\0\0", 16384) = 11
22:54:08.430698 Write (9, "B\0\0\0\3update ' cfc4n_user_info ' SET ' exp ' = exp + 26\nwhere ' cfc4n_user_id ' = \ ' 6154\ '", 70) = 70//Execute other SQK statements
22:54:08.430814 Read (9, "0\0\0\1\0\1\0\1\0\0\0 (Rows matched:1 changed:1 warnings:0", 16384) = 52///successfully updated, affecting the number of bars 1.
22:54:08.432130 Write (9, "\v\0\0\0\2database_demo", 15) = 15
22:54:08.432231 Read (9, "\7\0\0\1\0\0\0\1\0\0\0", 16384) = 11
22:54:08.432602 Write (9, "\244\0\0\0\3update ' cfc4n_user_quest ' SET ' rew ' = 1, ' retable ' = retable + 1, ' re_time ' = 133579 7648\nwhere ' cfc4n_user_id ' = \ ' 6154\ ' \nand ' quest_id ' = \ ' 300001\ ' \nand ' ' rew ' = 0 ', 168) = 168//execute other SQK statement
22:54:08.432743 Read (9, "0\0\0\1\0\1\0\1\0\0\0 (Rows matched:1 changed:1 warnings:0", 16384) = 52///successfully updated, affecting the number of bars 1.
22:54:08.433517 Write (9, "\v\0\0\0\2database_demo", 15) = 15
22:54:08.433620 Read (9, "\7\0\0\1\0\0\0\1\0\0\0", 16384) = 11
22:54:08.433954 Write (9, "\t\0\0\0\3rollback", 13) = 13//ROLLBACK TRANSACTION #注意看这里
22:54:08.434041 Read (9, "\7\0\0\1\0\0\0\0\0\0\0", 16384) = 11
22:54:08.434914 Write (9, "\v\0\0\0\2database_demo", 15) = 15
22:54:08.434999 Read (9, "\7\0\0\1\0\0\0\0\0\0\0", 16384) = 11
22:54:08.435342 Write (9, "\21\0\0\0\3set autocommit=1", 21) = 21//Resume Autocommit
22:54:08.435430 Read (9, "\7\0\0\1\0\0\0\2\0\0\0", 16384) = 11
22:54:08.436923 Write (9, "\1\0\0\0\1", 5) = 5

You can see that at the 22:54:08.380085 point in time, send the update SQL statement instruction, read the return result in 22:54:08.380089 time, get SQL execution error, there is no field "Cfc4n_user_lock"; 22 : 54:08.381791 and 22:54:08.382703 two points in time, PHP send Stop "autocommit" and "Start transaction" instruction, send "transaction rollback" instruction in 22:54:08.433954.

With the code analysis as above, you can clearly know that because "UPDATE ' cfc4n_user_info ' SET ' cfc4n_user_lock ' = 1 WHERE ' cfc4n_user_id ' = ' 6154′and ' Cfc4n_user_lo CK ' = 0 ' This SQL execution error, which causes the $_trans_status property to be set to false, is judged by the Trans_complete () method when the code commits the transaction, and the execution of the SQL statement in the previous transaction (carefully analyzed below) is considered to fail. Decide to roll back the transaction and not commit it.

Just mentioned "last transaction", some friends may not understand, we go back to the code, continue to look at the property, also in the Trans_complete method, 542-545 lines:

Copy Code code as follows:

If we are not running in strict mode, we'll reset
The _TRANS_STATUS flag so subsequent groups of transactions
would be permitted.
if ($this->trans_strict = = FALSE)
{
$this->_trans_status = TRUE;
}

It is also easy to see from the comments that the designer of CI, in order to more rigorous processing in the same script, there are multiple transactions, the relationship between the two important, a proud, a loss. The Trans_strict property here is a switch, and when Trans_strict is false, it is not strictly a mode, which means that the relationship between multiple transactions is unimportant and does not affect. The execution of a SQL statement in the current transaction fails and does not affect itself. The _trans_status is set to true.
There is no doubt that this is a very thoughtful consideration. Consider the relationship between multiple transactions to ensure that the business runs in more rigorous code.

However, in our code, the wrong SQL statement is executed outside of the transaction, not within the transaction. According to our knowledge of the transaction, it is clear that the SQL outside of the transaction is more important than the SQL within the transaction, and that there is an error in the transaction, but it must be correct and not disturbed by the outside. But in the framework of CI, because a statement other than one transaction fails, it causes the entire transaction to roll back ... Of course, our programmers are not judging the return of the transaction submission method, which is also a problem.

The question is clear, so the solution must be simple for you.
For example, in the Trans_start method, assign a value to the _trans_status property, set to True, regardless of the problem outside the transaction.

Copy Code code as follows:

function Trans_start ($test _mode = FALSE)
{
if ($this->trans_strict = = FALSE)
{
$this->_trans_status = TRUE; Reset the value of this property to true at the start of transaction processing
}
2012/05/01 18:00 after CI Chinese community netizen http://codeigniter.org.cn/forums/space-uid-5721.html correction, here revision to increase trans_strict attribute judgment, It's good to decide whether to reset the _trans_status.
if (! $this->trans_enabled)
{
return FALSE;
}

When transactions are nested we only begin/commit/rollback the outermost ones
if ($this->_trans_depth > 0)
{
$this->_trans_depth + 1;
Return
}

$this->trans_begin ($test _mode);
}

End:

Without understanding the design intent of the other, can not blindly define the other side of the code evaluation, regardless of the level of the author of the program. Stronger than himself, can not blindly worship, weaker than himself, not to blame; understanding the design intent, learning others excellent design ideas, code style, algorithm efficiency, this is a good habit. Of course the CodeIgniter framework is excellent.