CODEWITHSUNDEEP
phpmysqlsqlitepdo
MarcoR88
MarcoR88

Reputation: 23

Using PDO, is there a way to handle a transaction across two drivers?

So, let's say I'm using two drivers at the same time (in the specific mysql and sqlite3)

I have a set of changes that must be commit()ted on both connections only if both dbms didn't fail, or rollBack()ed if one or the another did fail:

<?php

interface DBList
{
    function addPDO(PDO $connection);

    // calls ->rollBack() on all the pdo instances
    function rollBack();

    // calls ->commit() on all the pdo instances
    function commit();

    // calls ->beginTransaction() on all the pdo instances
    function beginTransaction();
}

Question is: will it actually work? Does it make sense?

"Why not use just mysql?" you would say! I'm not a masochist! I need mysql for the classic fruition via my application, but I also need to keep a copy of a table that is always synchronized and that is also downloadable and portable!

Thank you a lot in advance!

Upvotes: 0

Views: 249

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65284

I suspect you put the cart before the horses! If

  1. two databases are in sync
  2. a transaction commits successfully on one DB
  3. No OS-level error occures

then the transaction will also commit successully on the second DB.

So what you would want to do is: - Start the transaction on MySQL - Record all data-changing SQL (see later) - Commit the transaction on MySQL - If the commit works, run the recorded SQL against SQlite - if not, roll back MySQL

Caveat: The assumption above is only valid, if the sequence of transactions is identical on both DBs. So you would want to record the SQL into a MySQL table, which is subject to the same transaction logic as the rest. This does the serialization work for you.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157889

You mistake PDO with a database server. PDO is just an interface, pretty much like the database console. It doesn't perform any data operations of its own. It cannot insert or select data. It cannot perform data locks or transactions. All it can do is to send your command to database server and bring back results if any. It's just an interface. It doesn't have transactions on it's own.

So, instead of such fictional trans-driver transactions you can use regular ones.

Start two, one for each driver, and then rollback them accordingly. By the way, with PDO one don't have to rollback manually. Just set PDO in exception mode, write your queries and add commit at the end. In case one of queries failed, all started transactions will be rolled back automatically due to script termination.

Upvotes: 0

Related Questions