Why you should not forget to clearly complete nested transactions in Yii2

Database transactions is a very handy tool. They allow us automatically rollback all changes which were created by a failed program procedure. And we cannot avoid nested transactions in most projects.

Simple example. There is a method which aggregates and updates some data in a database for a specified period. As a logic is complex, method body must be placed into transaction. This method is called by task manager. But there is one more method for importing data from a remote server, which running in transaction too. And when import is complete, we need to call the first method. In this way, inside a transaction will be called method, which itself runs inside a transaction.

This situation is usual and Yii2 provides us handy methods for working with database: Connection::beginTransaction(), Transaction::commit() and Transaction::rollBack(). We can works with nested transactions by using this methods.

In this article I want to tell you about one non-obvious moment in this mechanism because of which I wasted a lot of time and nervous.

Basic work with transactions

For example let's create a simple table, in which we will insert data:

CREATE TABLE test_tbl (
  msg VARCHAR(10) PRIMARY KEY
)

Insert two rows into table.

Notification In this article we will use a very simple code. Without exceptions and errors handling. A minimum code to understand a main things.

$db = \Yii::$app->getDb();
$transaction = $db->beginTransaction();
$db->createCommand()->insert('test_tbl', ['msg' => 'message 1'])->execute();
$db->createCommand()->insert('test_tbl', ['msg' => 'message 2'])->execute();
$transaction->commit();

As a result the next SQL query will be generated and executed.

BEGIN;
    INSERT INTO test_tbl (msg) VALUES ('message 1');
COMMIT;

It's important to understand that transaction will be completed anyway. We can do it with Transaction::commit() and Transaction::rollBack() methods. But if we don't do it clearly, transaction will be completed automatically. Will transaction committed or rolled back depends of database settings. So code

$db = \Yii::$app->getDb();
$transaction = $db->beginTransaction();
$db->createCommand()->insert('test_tbl', ['msg' => 'message 1'])->execute();
$db->createCommand()->insert('test_tbl', ['msg' => 'message 2'])->execute();

generates and executes next SQL query:

BEGIN;
    INSERT INTO test_tbl (msg) VALUES ('message 1');
    INSERT INTO test_tbl (msg) VALUES ('message 2');
ROLLBACK;

Work with nested transactions

A work with nested transactions in Yii2 is easily too.

$db = \Yii::$app->getDb();
$outerTransaction = $db->beginTransaction();
    $db->createCommand()->insert('test_tbl', ['msg' => 'message 1'])->execute();

    $innerTransaction = $db->beginTransaction();
        $db->createCommand()->insert('test_tbl', ['msg' => 'message 2'])->execute();
    $innerTransaction->rollBack();

    $db->createCommand()->insert('test_tbl', ['msg' => 'message 3'])->execute();
$outerTransaction->commit();

We suppose that this code will return a next SQL query:

BEGIN;
  INSERT INTO test_tbl (msg) VALUES ('message 1');

  BEGIN;
    INSERT INTO test_tbl (msg) VALUES ('message 2');
  ROLLBACK;

  INSERT INTO test_tbl (msg) VALUES ('message 3');
COMMIT;

But this query is incorrect. The fact is that not all databases can work with nested transactions. In Yii2 only msssql uses a real nested transactions. For other databases savepoint is used. This mechanism imitates real nested transactions. So the aforecited PHP code will generate next SQL query:

BEGIN;
  INSERT INTO test_tbl (msg) VALUES ('message 1');

  SAVEPOINT pnt1;
    INSERT INTO test_tbl (msg) VALUES ('message 2');
  ROLLBACK TO SAVEPOINT pnt1;

  INSERT INTO test_tbl (msg) VALUES ('message 3');
COMMIT;

Its result is the same as if PostgreSQL can work with nested transactions.

It's important to complete transactions clearly

And now let's talk about why it is important to clearly complete transactions in Yii2. To comply with the polymorphism, work with savepoints in Yii2 is hidden behind a transactions interface. Thereby programmer should not worry about that. He works with nested transactions as if the database can work with them. And framework uses savepoints when it needs.

In fact method Connection::beginTransaction() always returns same transaction object. On second calling of method, Yii2 creates a new savepoint and returns the same transaction. On committing or rolling back of the transaction, all happen conversely: at first all sapepoints will be completed and finally the transaction will be completed. In this way, if transaction will be completed in less cases then created, transaction will not be completed.

It is not obvious for programmers who don't know that they work with not real nested transactions. Programmer can don't complete the inner transaction clearly and expect what it will be completed automatic. But in this case the outer transaction will not be completed and full query will not be completed. I.e. this code

$db = \Yii::$app->getDb();
$outerTransaction = $db->beginTransaction();
    $db->createCommand()->insert('test_tbl', ['msg' => 'message 1'])->execute();

    $innerTransaction = $db->beginTransaction();
        $db->createCommand()->insert('test_tbl', ['msg' => 'message 2'])->execute();
    // $innerTransaction->rollBack();

    $db->createCommand()->insert('test_tbl', ['msg' => 'message 3'])->execute();
$outerTransaction->commit();

will generate not this query:

BEGIN;
  INSERT INTO test_tbl (msg) VALUES ('message 1');

  SAVEPOINT pnt1;
    INSERT INTO test_tbl (msg) VALUES ('message 2');
  ROLLBACK TO SAVEPOINT pnt1;

  INSERT INTO test_tbl (msg) VALUES ('message 3');
COMMIT;

but this:

BEGIN;
  INSERT INTO test_tbl (msg) VALUES ('message 1');

  SAVEPOINT pnt1;
    INSERT INTO test_tbl (msg) VALUES ('message 2');
  RELEASE SAVEPOINT pnt1;

  INSERT INTO test_tbl (msg) VALUES ('message 3');
ROLLBACK;

And nothing will be inserted into table instead messages message 1 and message 3.

Conclusion

What conclusion can we done from that? Necessary clearly complete all open transactions in your code. This is one another example of that, it's useful to know how your framework/library works inside.

You can read this article in Russian here.

Comments

Use Markdown
Thanks for your comment!
It will be published after approval.