代码，事务中的错误跟踪

问题描述

我在CodeIgniter中运行一个小方法在数据库中插入一些行（同一个表）。我想看看哪个插入在事务内失败（通过返回一个标题数组）。我的代码是：

I'm running a little method in CodeIgniter to insert some lines in the database (same table). I would like to see which insertion have failed inside a transaction (by returning an array of titles). My code is :

$failure = array(); //the array where we store what failed
$this->db->trans_start();
foreach ($data as $ressourceCsv){ //data is an array of arrays to feed the database
    $this->ajout_ressource($ressourceCsv); //method to insert (basically, just an insert with active record)
    if (($this->db->_error_message())!=null) { 
          $failure[] = $ressourceCsv['title']; 
    }
}
$this->db->trans_complete();
return $failure;

事实是，如果我没有做一个事务（没有$ this-> db- > trans _...），它工作完美，我有一个包含几个标题的数组。但是使用事务，数组包含自第一个错误以来的每个标题。有没有办法只从插入的标题，导致事务回滚？

The fact is that if I don't make it a transaction (no $this->db->trans_...), it works perfectly and I have an array containing a few titles. But with the transaction, the array contains every titles since the first error. Is there a way to just get the title from the insertion which caused the transaction to rollback?

我也尝试过：

$failure = array(); //the array where we store what failed
$this->db->trans_start();
foreach ($data as $ressourceCsv){ //data is an array of arrays to feed the database

    if (!$this->ajout_ressource($ressourceCsv)) { //active record insertion return true
          $failure[] = $ressourceCsv['title'];   // if successful
    }
}
$this->db->trans_complete();
return $failure;

推荐答案

我相信一旦事务内部发生错误，您可以必须回滚之前，任何更多的数据库mods可以做。这将解释你看到的行为。在第一个错误之后，事务被中止，并且继续循环，导致每个后续SQL命令也失败。这可以说明如下：

I believe that once an error occurs inside a transaction, you must rollback before any more DB mods can be made. That would explain the behavior you are seeing. After the first error, the transaction is "aborted" and you continue your loop, causing every subsequent SQL command to fail as well. This can be illustrated as follows:

db=# select * from test1;
 id | foo | bar
----+-----+-----
(0 rows)

db=# begin;
BEGIN
db=# insert into test1 (foo, bar) values (1, 'One');
INSERT 0 1
db=# insert into test1 (foo, bar) values (Oops);
ERROR:  column "oops" does not exist
LINE 1: insert into test1 (foo, bar) values (Oops);
                                             ^
db=# insert into test1 (foo, bar) values (2, 'Two');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
db=# select * from test1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
db=# commit;
ROLLBACK
ace_db=# select * from test1;
 id | foo | bar
----+-----+-----
(0 rows)

db=#

请注意，如果出现错误（这不是打字错误），commit会执行回滚。

Note it seems that "commit" does a "rollback" if there was an error (it was not a typo.)

还有BTW：使用 $ this-> db-> trans_status（）=== FALSE 在事务期间检查错误。

Also BTW: use $this->db->trans_status() === FALSE to check for an error during the transaction.

更新：这里有一些（未测试的）代码在事务中做，以便插入是别人看不到，直到你准备好：

Update: Here's some (untested) code to do it in a transaction so that the inserts are not seen by others until you are ready:

$failure = array(); //the array where we store what failed
$done = false;
do {
    $this->db->trans_begin();
    foreach ($data as $key => $ressourceCsv){ //data is an array of arrays to feed the database
        $this->ajout_ressource($ressourceCsv); //method to insert (basically, just an insert with active record)
        if ($this->db->trans_status() === false) { // an insert failed
            $failure[] = $ressourceCsv['title'];   // save the failed title
            unset($data[$key]);                    // remove failed insert from data set
            $this->db->trans_rollback();           // rollback the transaction
            break;                                 // retry the insertion
        }
    }
    $done = true;                                  // completed without failure
} while (count($data) and ! $done);                // keep going until no data or success

/*
 * Two options (uncomment one):
 * 1. Commit the successful inserts even if there were failures.

$this->db->trans_commit();

 * 2. Commit the successful inserts only if no failures.

if (count($failure)) {
    $this->db->trans_rollback();
} else {
    $this->db->trans_commit();
}
*/

return $failure;

这篇关于代码，事务中的错误跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！