Singleton class and mysqli transaction

Question

I have a singleton class DataBase for sharing database connection:

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() {} //no cloning in singleton
}

I use this singleton object in many other classes and retrieve the database connection from singleton within each class constructor function, e.g.:

class Customer
{
  public $db;

  function __construct()
  {
    $this->db=DataBase::getInstance();
  }

  public function create()
  {
    $this->db->query('INSERT INTO customers SET ... WHERE id=100'); //e.g.
  }

}

and

class Product
{
  public $db;

  function __construct()
  {
    $this->db=DataBase::getInstance();
  }

  public function create()
  {
    $this->db->query('INSERT INTO products SET ... WHERE id=200'); //e.g.
  }
}

Since all my classes actually share the same connection given by singleton class, my question is can I start database transaction from one class and rollback/commit it from another class. What actually I mean by that, is the code below a correctly made db transaction for creating Customer and Product (classes modeling db records behind) within one database transaction:

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

How this code would work in multiuser environment where all users share the same connection from sigleton class?

Answer 1

As @Marc B mentioned, transactions are connection-based, so generally you are able to start and commit a transaction in two different objects.

However, you should make sure you're not using persistent connections (read more about connections handling by PHP and mysqli http://php.net/manual/en/mysqli.quickstart.connections.php. Persistent connections are pooled between scripts and potentially expose your app to risk of transactions interference.

Also, bear in mind that some statements cause an implicit commit (as explained http://php.net/manual/en/mysqli.quickstart.transactions.php). You should also verify, that your connection to DB doesn't time out during script execution (in some cases it can happen). I'd start with checking MySQL's wait_timeout http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout setting.

IMO you should also think hard about the design of your application. I strongly believe, that DB objects should be only available in Model objects and called only from inside of them, not explicitly anywhere in code.

And if you decide to think about transactions - remember to put aproppriate methods in your DataBase class (like adding autocommit(TRUE) and rollback() on object destruction, if there was a transaction started). It's better to be safe than sorry.

I strongly encourage you to choose (or change your db engine) to InnoDB (great support for table locking and transactions) - read more about it in The InnoDB Transaction Model and Locking. Also a greaty deal of useful info about transactions is in documentation here: Mysql transactions and locking.

Answer 2

Yes, your code does work properly in a multi-user enviorment if users access your phps from a webserver apache, nginx etc. php will create a new connection resource (you can see them in mysql's proccess list). Your singleton DB class just guarantees that in a single request what ever included php or class needs a connection, all of them will use that connection so, if you are not useing some pctnl fork flow, logicaly all of your code will run in an order this will guarantee that all of your commits and rollbacks will be work properly.

Of course your tables should be innoDB if you want to do some transactional queries myisam does not support transactions.