How to make database transaction in PHP OOP

Question

In my obsolate procedural code (which I'd like now to translate into OOP) I have simple database transaction code like this:

mysql_query("BEGIN");
mysql_query("INSERT INTO customers SET cid=$cid,cname='$cname'");
mysql_query("INSERT INTO departments SET did=$did,dname='$dname'");
mysql_query("COMMIT");

If I build OOP classes Customer and Department for mapping customers and departments database tables I can insert table records like:

$customer=new Customer();
$customer->setId($cid);
$customer->setName($cname);
$customer->save();

$department=new Department();
$department->setId($did);
$department->setName($dname);
$department->save();

My Customer and Department classes internally use other DB class for querying database.

But how to make $customer.save() and $department.save() parts of a database transaction?

Should I have one outer class starting/ending transaction with Customer and Department classes instantiated in it or transaction should be started somehow in Customer (like Customer.startTransaction()) and ended in Department (like Department.endTransaction())? Or...

The `mysql_*` functions are **no longer maintained** and shouldn't be used in any new codebase. It is being phased out in favor of newer APIs. Instead you should use [**prepared statements**](https://www.youtube.com/watch?v=nLinqtCfhKY) with either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). — tereško, Dec 01 '13 at 13:44
On other note, you should not have domain logic and persistence logic in same class. Try instead implement [data mapper](http://martinfowler.com/eaaCatalog/dataMapper.html) pattern (no, it is **not** a name of ORM). As for cases when you need to store multiple entries in a single transaction, this is the point when you should be starting to use [unit of work](http://martinfowler.com/eaaCatalog/unitOfWork.html) for managing your mappers. — tereško, Dec 01 '13 at 13:47
Thanks tereško, of course that I will use mysqli but the main question is here how to start database transaction on one class and commit it on another class!? — sbrbot, Dec 02 '13 at 20:17
Did you actually research what *Unit of Work* is and does? There is a whole chapter on it in PoEAA. — tereško, Dec 02 '13 at 20:27
Dear tereško do you intend to answer my question and help me or you just want to advertise the book PoEAA? No doubt that there is some book that can solve my problem. But the purpose of sites like this is to help people with quick and prompt answers. — sbrbot, Dec 03 '13 at 20:23

score 2 · Answer 1 · edited May 23 '17 at 12:02

How to make $customer.save() and $department.save() parts of a database transaction?

You don't have to do anything besides start the transaction.

In most DBMS interfaces, the transaction is "global" to the database connection. If you start a transaction, then all subsequent work is automatically done within the scope of that transaction. If you commit, you have committed all changes since the last transaction BEGIN. If you rollback, you discard all changes since the last BEGIN (there's also an option to rollback to the last transaction savepoint).

I've only used one database API that allowed multiple independent transactions to be active per database connection simultaneously (that was InterBase / Firebird). But this is so uncommon, that standard database interfaces like ODBC, JDBC, PDO, Perl DBI just assume that you only get one active transaction per db connection, and all changes happen within the scope of the one active transaction.

Should I have one outer class starting/ending transaction with Customer and Department classes instantiated in it or transaction should be started somehow in Customer (like Customer.startTransaction()) and ended in Department (like Department.endTransaction())? Or...

You should start a transaction, then invoke domain model classes like Customer and Department, then afterwards, either commit or rollback the transaction in the calling code.

The reason for this is that domain model methods can call other domain model methods. You never know how many levels deep these calls go, so it's really difficult for the domain model to know when it's time to commit or rollback.

For some pitfalls of doing this, see How do detect that transaction has already been started?

But they don't have to know that. Customer and Department should just do their work, inserting and deleting and updating as needed. Once they are done, the calling code decides if it wants to commit or rollback the whole set of work.

In a typical PHP application, a transaction is usually the same amount of work as one PHP request. It's possible, though uncommon, to do more than one transaction during a given PHP request, and it's not possible for a transaction to span across multiple PHP requests.

So the simple answer is that your PHP script should start a transaction near the beginning of the script, before invoking any domain model classes, then commit or rollback at the end of the script, or once the domain model classes have finished their work.

score 2 · Accepted Answer · answered Dec 06 '13 at 11:43

Additional object is the way to go. Something like this:

$customer=new Customer();
$customer->setId($cid);
$customer->setName($cname);

$department=new Department();
$department->setId($did);
$department->setName($dname);

$transaction = new Transaction();
$transaction->add($customer);
$transaction->add($department);
$transaction->commit();

You can see that there is no call to save() method on $customer and $department anymore. $transaction object takes care of that.

Implementation can be as simple as this:

class Transaction
{
    private $stack;

    public function __construct()
    {
        $this->stack = array();
    }

    public function add($entity)
    {
        $this->stack[] = $entity;
    }

    public function commit()
    {
        mysql_query("BEGIN");
        foreach ($this->stack as $entity) {
            $entity->save();
        }
        mysql_query("COMMIT");
    }
}

but you really should consider switching to PDO or MySQLi, as both of them have explicit support for transactions — JimiDini, Dec 06 '13 at 11:45

Carlos Robles · Answer 3 · 2013-12-12T21:00:59.997

You are migrating to OOP, and thats great, but soon you will find yourself migrating to an arquitecture with a well diferenciated Data Access Layer, including a more complex way of separating data from control. Now, i guess you are using some kind of Data access object, that is a great first approach pattern, but for sure you can go further. Some of the answer here already lead you in that direction. You shouldent think in your objects as the basis of your arquitecture, and use some helper objects to query database. Instead, you should think about a fully featured layer, with all required generic classes that takes care of the comunication with the database, that you will use in all your projects, and then have the business-level-objects, like customer or department, than know as litle as possible about database implementations.

For this, for sure you will have an outer class handling transactions, but probably also other taking care of security, other for building queries providing a unique api regardless or the database engine, and even more, a class that reads objects in order to put them in the database, so the object itself doesn't even know that it is meant to end in a database.

Achieve this, would be a hard and long work, but after that, you could have a custom and widely reusable layer that will make your projects more escalable, more stable, and more trustable. And that will be great and you will learn a lot and after that you would fill quite good. You will have some kind of DBAL or ORM. But that wouldnt also be the best solution, since there are people that already have been years doing that, and it will be hard to achieve what the already have.

So, what i recommend, for any medium size project, is that you take data base abstraction as serious as you can, and any opensource ORM, that happens to be easy to use, and finally you will save time and get a system much better.

for example, doctrine has a very nice way of handling transactions and concurrency, in two ways: implicit, taking automatically care of the normal operations, or implicit, when you need to take over and control transaction demarcation yourself. check it out here. Also, there are some other complex posibilities like transaction nesting, and others.

The most famous and reliable ORM are

Doctrine, and
Propel

I use doctrine mostly, since it has a module to integrate with Zend Framework 2 that i like, but propel has some aspects that i like a lot.

Probably you would have to refactor somethings, and you dont feel like doing it at this point, but i can say for my experience, that this is one of those things you dont even want to think about, and years after you start using it and realize how you wasted time :-)recommend you to consider this if not know, in your very next project.

UPDATE

Some thoughts after Tomas' comment.

It's true that for not so big projects (especially if you are not very familiar with orms, or your model is very complex) it can be a big effort to integrate a vendor orm. But what i can say after years developing projects of any size, is that for any medium size one, i would use at least a custom, less serious and more flexible home-made orm, with a sort of generic classes, and as few as possible business oriented repositories, where an entity knows its table, and probably other related tables, and where you can encapsulate some sql or custom query function calls, but all around that entity (for example the main table of the entity, the table of pictures associated to that entity, and so) in order to provide to the controller a single interface to the data, so at any range the database engine is independent of the API of the model, and as much important as that, the controller doesn't have to be aware of any DBMS aspects, like the use of a transactions, something that is meant just to ensure a behavior that is purely model-related, and in a scandalous low level: related pretty much to DBMS technical needs. i mean, your controller could know that it is storing stuff in a database, but for sure it doesn't have to even know what a transaction is.

For sure this is a philosophical discussion, and it could be many equally valid points of view.

For any custom ORM, i would recommend to start looking for some DAO/DTO generator that can help you to create the main classes from your database, so you only need to adapt them to your needs at the points where you find exceptions to the normal behavior of a normal create-read-update-delete. This reminds me that you can also look for PHP CRUD and find some useful and fun tools.

Hola Carlos! This post is very interesting, though I think for most PHP projects it is overkill to do such an abstraction, that your app doesn't even know it uses a db. This comes with a cost, and for most projects it just won't pay off. For example, writing some more complex db query (with correlated subqueries, group by etc.) is very easy in SQL but in some reusable layer?? Such a simple thing may just get so complex that a mortal developer resigns on the sophisticated, but impractical, approaches. My experience. Greetings, I am by chance now in Barcelona too! :-) — Tomas, Dec 12 '13 at 19:13
hola tomas! i agree with what you say, and thats why for small projects, i wont probably use a vendor orm, but for sure, if it is medium size, i would use at least a custom, less serious and more flexible orm, with some generic classes, and as few as possible business oriented repositories, where an entity knows its table, and other related tables, and where you can encapsulate some sql, but all around that entity (for example the main table of the entity, the table of pictures associated to that entity, and so).I will update the answer with this! BTWm nice to have 11k reputation guy in town! — Carlos Robles, Dec 12 '13 at 20:35

How to make database transaction in PHP OOP

3 Answers3