Singleton class and mysqli transaction

Question

I have a singleton DataBase class to connect to a database:

class DataBase { private static $mysqli; final private function __construct() {} public static function getInstance() { if (!is_object(self::$mysqli)) self::$mysqli = new mysqli($H,$U,$P,$B); return self::$mysqli; } private function __destruct() { if (self::$mysqli) self::$mysqli->close(); } private function __clone() {} } 

I use this singleton from many other classes and extract the database connection from the constructor function, for example:

 class Customer { public $db; function __construct() { $this->db=DataBase::getInstance(); } public function create() { $this->db->query('INSERT INTO customers SET ... WHERE id=100'); //eg } } 

and

 class Product { public $db; function __construct() { $this->db=DataBase::getInstance(); } public function create() { $this->db->query('INSERT INTO products SET ... WHERE id=200'); //eg } } 

Since all my classes actually use the same connection defined by the singleton class, my question is: can I start a database transaction from one class and roll back / copy it from another class. What I really mean by this is the code below a correctly made db transaction to create a Customer and Product (classes modeling db records behind) within a single database transaction:

 $Customer=new Customer(); $Customer->db->begin_transaction(); ... $Customer->create(); ... $Product=new Product(); $Product->create(); ... $Product->db->commit(); 

How will this code work in a multi-user environment where all users use the same connection from the sigleton class?

Answer 1

Yes, your code really works in a multi-user environment if users access your php from the apache, nginx web server, etc. php will create a new connection resource (you can see them in the mysql proxy list). Your singleton DB class simply ensures that in one query that ever included php or class, a connection is required, they will all use this connection, if you do not use any pctnl fork thread, it is logical that all your code will work OK, this ensures that all your commits and rollbacks work correctly.

Of course, your tables must be innoDB if you want to execute some transactional queries. myisam does not support transactions.

Answer 2

As mentioned in @Marc B, transactions are connection-based, so you can usually start a transaction and make transactions in two different objects.

However , you must make sure that you do not use persistent connections (more about handling connections using PHP and mysqli http://php.net/manual/en/mysqli.quickstart.connections.php Persistent connections are combined between scripts and potentially expose your application risk of interference in the transaction.

Also note that some statements invoke an implicit commit (as explained by http://php.net/manual/en/mysqli.quickstart.transactions.php ). You should also make sure that your database connection is not disconnected during script execution (in some cases this can happen). I would start by checking the MySQL installation wait_timeout http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout .

IMO, you should also seriously consider the design of your application. I strongly believe that database objects should be accessible only in model objects and called only from the inside, not explicitly anywhere in the code.

And if you decide to think about transactions - do not forget to put aprop the appropriate methods in your DataBase class (for example, adding autocommit (TRUE) and rollback () when destroying an object if a transaction was started). Better to be safe than sorry.

I highly recommend that you select (or change your db engine) in InnoDB (excellent support for locking tables and transactions) - read more about this in the InnoDB Transaction Model and Lock . In addition, a large amount of useful information about operations is contained here in the documentation: Mysql transactions and locking .