Design defects and solutions for CodeIgniter framework database transaction processing

This article mainly introduces the design defects and solutions for CodeIgniter framework database transaction processing. It can also be said that it is a small BUG. friends who use CodeIgniter transactions will encounter this problem, for more information, see the CI framework.

Cause:

In an online business, we use the older CodeIgniter framework. in the DB class, there is a design defect in the processing of DB transactions, maybe it's not a defect. However, it affects our production environment and leads to a chain reaction. It has a great impact on the business and is not easy to troubleshoot. I reported this issue to codeigniter's Chinese webmaster Hex in middle March, and I forgot about it. Today, our online businesses once again think this problem has caused me to troubleshoot it again. For specific reasons, please let me know. (This problem also exists in the latest Version 2.1.0)

Analysis:

Take CodeIgniter framework Version 2.1.0 as an example. in the CI_DB_driver class of system \ database \ DB_driver.php, row 58th has a $ _ trans_status attribute.

The code is 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 shocould occur
Var $ cache_on = FALSE;

At the same time, the query method of this class contains code that assigns this attribute. for details, see lines 306 and 307 of the file.

The code is as follows:

// This will trigger a rollback if transactions are being used
$ This-> _ trans_status = FALSE;

A comment is also given here, telling us that if transaction processing is used, this attribute will become a final condition for rollback.

In the transaction commit method trans_complete of row 3, run the following code:

The code is 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 will 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 will reset
// The _ trans_status flag so that subsequent groups of transactions
// Will 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 row 535, if the _ trans_status attribute is false, rollback will occur and false is returned.

In our business code, due to the programmer's negligence, the program did not judge whether the trans_complete () method was correctly executed and directly told the user that the operation was successful. However, in fact, the program has issued a rollback command to the database, the DB record is not updated successfully. When the user executes the next operation, the program finds that the corresponding record is not updated, and reminds the user that the previous operation is not completed and notifies the user to re-execute the operation. So repeatedly...

The troubleshooting process is also very interesting. at first, from the PHP code, the problem is always uncertain and the focus is not placed on the return of the trans_complete () method. It was not until the strace captured packet analysis that the rollback was caused by this attribute.

The code is as follows:

22:54:08. 380085 write (9, "_ \ 0 \ 0 \ 0 \ 3 UPDATE 'cfc4n _ user_info 'SET 'cfc4n _ user_lock' = 1 \ nWHERE 'cfc4n _ user_id '= \ '2017 \' \ nAND' cfc4n _ user_lock '= 0 ", 99) = 99 // execute the update command
22:54:08. 380089 read (9, ": \ 0 \ 0 \ 1 \ 377 \ 36 \ 4 # 42S22Unknown column \ 'cfc4n _ user_lock \ 'in \ 'Where clause \'", 16384) = 62 // SQL execution error because no field exists
22:54:08. 381791 write (9, "\ 21 \ 0 \ 0 \ 0 \ 3 set autocommit = 0", 21) = 21 // disable 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 \ 3 start 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 \ 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 \ 3 UPDATE 'cfc4n _ user_info 'SET 'sil' = CAST (silver + (5) as signed) \ nWHERE 'cfc4n _ user_id '= \ '000000' ", 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 // Updated successfully, affecting the number of items 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 \ 3 UPDATE 'cfc4n _ user_info 'SET 'exp' = exp + 26 \ nWHERE 'cfc4n _ user_id' = \ '2017 \'", 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 // Updated successfully, affecting the number of items 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 \ 3 UPDATE 'cfc4n _ user_quest 'SET 'rew' = 1, 'retable' = retable + 1,'re _ time' = 1335797648 \ nWHERE 'cfc4n _ user_id '= \ '2017 \' \ nAND 'Quest _ id' = \ '2017 \ '\ nAND 'rew' = 0 ", 168) = 168 // execute other SQK statements
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 // Updated successfully, affecting the number of items 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 \ 3 ROLLBACK", 13) = 13 // roll back the transaction # note here
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 \ 3 set autocommit = 1", 21) = 21 // restore automatic submission
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 \ 1", 5) = 5

We can see that at 22:54:08. 380085 send the SQL statement update command at 22:54:08. 380089 read the returned results and get an SQL execution error. The field "cfc4n_user_lock" does not exist; 22:54:08. 381791 and 22:54:08. 382703 at two time points, PHP sent and stopped the "automatic commit" and "start transaction processing" commands at 22:54:08. 433954 send the "transaction rollback" command.

With the above code analysis, you can clearly understand, because the SQL execution error "UPDATE 'cfc4n _ user_info 'SET 'cfc4n _ user_lock' = 1 WHERE 'cfc4n _ user_id' = '000000' AND 'cfc4n _ user_lock' = 0" is returned, as a result, the $ _ trans_status attribute is set to FALSE. when the code commits a transaction, it is determined by the trans_complete () method and considered as "processing of the previous transaction" (which will be analyzed carefully below) if SQL statement execution fails, the transaction is rolled back and not committed.

Some friends may not understand the previous transaction processing. let's go back to the code and continue to look at this attribute. Similarly, in the trans_complete method, there are lines 542-545:

The code is as follows:

// If we are NOT running in strict mode, we will reset
// The _ trans_status flag so that subsequent groups of transactions
// Will be permitted.
If ($ this-> trans_strict === FALSE)
{
$ This-> _ trans_status = TRUE;
}

It is also easy to understand from the annotations that when the CI designer is set up to process multiple transactions in the same script more rigorously, the relationship between the transactions is important, loss. The trans_strict attribute is a toggle. when trans_strict is set to false, the non-strict mode means that the relationship between multiple transactions is not important and does not affect each other. The execution of SQL statements in the current transaction fails and cannot affect itself. Set _ trans_status to TRUE.
There is no doubt that this is a very comprehensive consideration. The relationship between multiple transactions is considered to ensure that the business runs on more rigorous code.

However, in our code, the wrong SQL statement is executed outside the transaction processing, not within the transaction. According to our understanding of transactions, we can clearly understand that SQL beyond transactions is more important than SQL within transactions, and errors can be allowed outside of transactions, however, in the event of a transaction, it must be correct without external interference. However, in the CI framework, the execution of statements other than a transaction fails, but the entire transaction is rolled back... Of course, our programmers did not judge the return of the transaction commit method, which is also a problem.

The problem is clear, so the solution must be simple for you.
For example, in the trans_start method, assign a value to the _ trans_status attribute and set it to TRUE, ignoring non-transactional issues.

The code is as follows:

Function trans_start ($ test_mode = FALSE)
{
If ($ this-> trans_strict === FALSE)
{
$ This-> _ trans_status = TRUE; // reset the value of this attribute to TRUE when starting transaction processing.
}
// After the CI Chinese community netizen http://codeigniter.org.cn/forums/space-uid-5721.htmlcorrection, modify to add the trans_strict attribute to determine whether to reset _ 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 party, you cannot blindly define the code evaluation of the other party, regardless of the level of the program author. They are better than themselves, and cannot blindly worship themselves. they are weaker than themselves, and cannot be blamed. they can understand design intent and learn others' excellent design ideas, code styles, and algorithm efficiency, this is a good habit. Of course, the codeigniter framework is excellent.