当第二个数据库连接打开时，Feoign Key Locking Transaction超时

本文介绍了当第二个数据库连接打开时，Feoign Key Locking Transaction超时的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我遇到一类涉及数据库事务的问题，该类事务由于在事务中打开辅助数据库连接而超时；当我添加外键约束时，该问题开始出现.并且，使用以下方法进行测试:

I'm having a problem involving a database transaction in one class that is timing out due to a secondary database connection being opened within the transaction; the problem started occurring when I added a foreign key constraint. And, testing using:

SET foreign_key_checks = 0;

我已经确认了这一点.

我的数据库类看起来像这样(我放弃了所有方法):

My database class looks like this (I've left off all of the methods):

class Db { function __construct($config) { $this->config = $config; } private function connect($config) {$dsn = 'mysql:host=' . $config['host'] . ';dbname=' . $config['dbname'] . ';charset=utf8'; $options = array( // PDO::ATTR_PERSISTENT => true, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ); $dbh = new PDO($dsn, $config['username'], $config['password'], $options); $dbh->exec("SET NAMES utf8;"); return $dbh; } }

我的模型如下:

class Model { function __construct() { $this->db = new Db(array('host'=>DB_HOST,'dbname'=>DB_NAME,'username'=>DB_USERNAME,'password'=>DB_PASSWORD)); } }

然后，下面的代码执行一点逻辑，然后插入到question_orders表中:question_orders具有一列question_id，该列具有外键索引，该索引引用父表的问题；我认为问题在于Assessment_Question_Orders扩展了模型并创建了新的数据库连接?对于如何同时维护交易和外键方面的任何想法，将不胜感激.

The code below then performs a little bit of logic, then an insert into the question_orders table: question_orders has a column question_id, with a foreign key index, which references the parent table questions; I think that the problem is that Assessment_Question_Orders extends the Model and creates a new database connection? Any thoughts on how to maintain both the transaction and foreign key aspects would be appreciated.

class This_Is_A_Problem extends Model() { public function __construct() { parent::construct(); } public function problemFunction() { /*variable init code left out*/ $this->db->beginTransaction(); $db_result = false; try { $db_result = $this->db->insert('questions', $questions_data); $new_insert_id = $this->db->lastInsertId(); $assessment_question_orders = new Assessment_Question_Orders(); $question_number = $assessment_question_orders->insertSingleQuestionOrder($module_id, $new_insert_id); $db_result = $this->db->commit(); } } catch (PDOException $e) { $this->db->rollBack(); }}}

推荐答案

(通常)一个线程应仅与数据库建立一个连接.因此，我建议使用以下一种模式:

One thread should (usually) have only one connection to the database. So I recommend one of these patterns:

方案A:将一个$ db传递给所有类:

Plan A: a single $db passed into all classes:

$db = new PDO(...); $my_obj = new My_Class($db); -- $db is saved in $this->db for use within the methods of My_Class.

计划B:具有getter方法的单例Db类:

Plan B: a singleton Db class with a getter method:

// Singleton (of sorts) class Db { private static $db; function __construct() { self::$db = new PDO(...); // A variant would include "lazy" instantiation of self::$Db. } function Get_Db() { return self::$db; } // All calls get the same `db` } class My_class { function My_Method() { $db = Db::Get_Db(); $db->... } } new Db(); // one time call at start of program

在一个程序中几乎不需要两个数据库连接.计划A很容易做到这一点. (但是看看是否能避免它-因为它，您现在遇到了麻烦.)

There is only rarely a need to have two db connections in a single program. Plan A easily allows for such. (But see if you can avoid it -- you are in trouble now because of it.)