Key Lock Foeign Key Transaction timeout when opening a second database connection
I am having an issue with a database transaction in one class getting disconnected due to opening a secondary database in a transaction; the problem came when I added a foreign key constraint. And, testing using:
SET foreign_key_checks = 0;
I was able to confirm this.
My database class looks like this (I stopped all 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;
}
}
My model looks like this:
class Model {
function __construct() {
$this->db = new Db(array('host'=>DB_HOST,'dbname'=>DB_NAME,'username'=>DB_USERNAME,'password'=>DB_PASSWORD));
}
}
Then the code below does a little bit of logic, then inserts into the question_orders table: question_orders has a question_id column with a foreign key index that refers to the questions in the parent table; I think the problem is that Assessment_Question_Orders expands the model and creates a new database connection? Any thoughts on how to support both transactional and external 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();
}}}
source to share
One thread should (usually) only have one database connection. Therefore, I recommend one of these patterns:
Plan A: One bit of $ db is passed to 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.
Plan B: single Db class with 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
It is rarely required to have two db connections in the same program. Plan A makes it easy for this. (But see if you can avoid it - now you're in trouble for it.)
source to share