Design defects of DB transaction processing in CodeIgniter 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. This problem occurs in middle March of this year,

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. This problem occurs in middle March of this year,

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 hex, the webmaster of http://codeigniter.org.cn/, in middle March this year. Then 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.

//system\database\DB_driver.phpvar $trans_strict= TRUE;var $_trans_depth= 0;var $_trans_status= TRUE; // Used with transactions to determine if a rollback should occurvar $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.

// 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, the following code is as follows:

/** * Complete Transaction * * @accesspublic * @returnbool */function trans_complete(){if ( ! $this->trans_enabled){return FALSE;}// When transactions are nested we only begin/commit/rollback the outermost onesif ($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 failedif ($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 programmer negligence,The trans_complete () method is not correctly executed, and the operation is successful.But in fact, the program has issued a rollback command to the DB, And the DB records are 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...

Design defects of CodeIgniter framework

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.

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 // No field exists. SQL Execution error 22:54:08. 381791 write (9, "\ 21 \ 0 \ 0 \ 0 \ 3 set autocommit = 0", 21) = 21 // disable automatic submission at 22:54:08. 381891 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0", 16384) =. 382186 poll ([{fd = 9, events = POLLIN | POLLPRI}], 1, 0) =. 382258 write (9, "\ v \ 0 \ 0 \ 0 \ 2jv01_roles", 15) = 1522: 54: 08.382343 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0", 16384) =. 382631 poll ([{fd = 9, events = POLLIN | POLLPRI}], 1, 0) =. 382703 write (9, "\ 22 \ 0 \ 0 \ 3 start transaction", 22) = 22 // start transaction processing 22:54:08. 401954 write (9, "\ v \ 0 \ 0 \ 0 \ 2database_demo", 15) = 1522: 54: 08.402043 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 1 \ 0 \ 1 \ 0", 16384) =. 417773 write (9, "\ v \ 0 \ 0 \ 0 \ 2database_demo", 15) = 1522: 54: 08.417872 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0", 16384) =. 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 at 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 // successful update, affected items: 1.22: 54: 08.430212 write (9, "\ v \ 0 \ 0 \ 0 \ 2database_demo", 15) = 1522: 54: 08.430314 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0", 16384) =. 430698 write (9, "B \ 0 \ 0 \ 0 \ 3 UPDATE 'cfc4n _ user_info 'SET 'exp' = exp + 26 \ nWHERE 'cfc4n _ user_id' = \ '2017 \'", 70) = 70 // execute another SQK statement 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 // successful update, affected items: 1.22: 54: 08.432130 write (9, "\ v \ 0 \ 0 \ 0 \ 2database_demo", 15) = 1522: 54: 08.432231 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0", 16384) =. 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 another 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 // successful update, affected items: 1.22: 54: 08.433517 write (9, "\ v \ 0 \ 0 \ 0 \ 2database_demo", 15) = 1522: 54: 08.433620 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0", 16384) =. 433954 write (9, "\ t \ 0 \ 0 \ 3 ROLLBACK", 13) = 13 // roll back the transaction # note that here is 22:54:08. 434041 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0", 16384) =. 434914 write (9, "\ v \ 0 \ 0 \ 0 \ 2database_demo", 15) = 1522: 54: 08.434999 read (9, "\ 7 \ 0 \ 0 \ 1 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0", 16384) =. 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) =. 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"Previous Transaction Processing"(Which will be analyzed in detail below) If an SQL statement fails to be executed, it is decided to roll back the transaction without committing it.

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:

// 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.

Function trans_start ($ test_mode = FALSE) {if ($ this-> trans_strict = FALSE) {$ this-> _ trans_status = TRUE; // when starting transaction processing, reset the value of this attribute to TRUE} // 2012/05/01. After judging by the CI 文 attribute, it is better to decide whether to reset _ trans_status. If (! $ This-> trans_enabled) {return FALSE;} // When transactions are nested we only begin/commit/rollback the outermost onesif ($ 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.

Original article address: I would like to thank the original author for sharing the design defects of DB transaction processing in the CodeIgniter framework.