Transactions

Database transactions protect data integrity and, used well, query performance. Propulsion uses transactions internally for every save()/delete() call, and exposes a plain PDO-flavored API for wrapping your own code in one.

Wrapping queries in a transaction

Propulsion connections are PropulsionPDO instances, so transactions use PDO’s built-in transaction support verbatim — beginTransaction(), commit(), and rollBack():

<?php
use Propulsion\Propulsion;

public function transferMoney($fromAccountNumber, $toAccountNumber, $amount)
{
    $con = Propulsion::getWriteConnection(\Map\AccountTableMap::DATABASE_NAME);

    $fromAccount = AccountQuery::create()->findPk($fromAccountNumber, $con);
    $toAccount   = AccountQuery::create()->findPk($toAccountNumber, $con);

    $con->beginTransaction();

    try {
        $fromAccount->setValue($fromAccount->getValue() - $amount);
        $fromAccount->save($con);

        $toAccount->setValue($toAccount->getValue() + $amount);
        $toAccount->save($con);

        $con->commit();
    } catch (\Exception $e) {
        $con->rollBack();
        throw $e;
    }
}

If saving either account throws, the whole transfer rolls back — the money never “vanishes” partway through (Atomicity). If both saves succeed, the transaction commits and both changes persist together.

`Propulsion::transaction()`: the same thing without the boilerplate

Propulsion::transaction() begins the transaction, calls your closure with the connection, and commits if it returns or rolls back if it throws. The closure’s return value is the method’s return value:

$book = Propulsion::transaction(function ($con) {
    $book = BookQuery::create()->filterByISBN($isbn)->findOne($con);
    $book->setStock($book->getStock() - 1);
    $book->save($con);

    return $book;
});

Propulsion::transaction($work, $name, $policy) takes the datasource name as its second argument, defaulting to the default one. A closure called while the connection is already in a transaction runs in a nested one — a real SAVEPOINT where the platform has them, per nested transactions below.

This is also the form that can retry: with connection.retry enabled, a transaction that loses a deadlock or a serialization check is replayed rather than surfacing to you. That puts one requirement on the closure — it has to be safe to run more than once — so read Connection resilience before switching it on. Retrying is off by default, and a closure with side effects outside the transaction can opt out per call with RetryPolicy::none().

`save()`/`delete()` already run in a transaction

BaseXXX::save() wraps its own INSERT/UPDATE in a transaction together with anything registered in a preSave()/postSave() hook, so denormalized counters, audit rows, or related-object saves triggered from those hooks commit or roll back atomically with the row itself:

<?php
class Book extends BaseObject implements Persistent, Poolable, WritableModelInterface
{
    use BookGenerated;

    public function postSave(?PropulsionPDO $con = null): void
    {
        $author = $this->getAuthor();
        $author->setNbBooks($author->countBooks($con));
        $author->save($con);
    }
}

If anything in postSave() throws, the book’s own insert/update rolls back too — the hook runs inside the same transaction save() already opened.

Nested transactions

PDO itself has no concept of nested transactions, but Propulsion provides them across every supported database: calling beginTransaction()/commit()/rollBack() while an outer transaction is already open never starts or ends a second real database transaction. Only the outermost pair does that.

Where the platform supports savepoints, a nested transaction is a real SAVEPOINT, so a nested rollBack() undoes only its own work and the outer transaction can still commit normally afterwards. See Savepoints and platform differences below.

<?php
function deleteBooksWithNoPrice(PropulsionPDO $con): void
{
    $con->beginTransaction();
    try {
        $c = new Criteria();
        $c->add(\Map\BookTableMap::PRICE, null, Criteria::ISNULL);
        \Map\BookTableMap::doDelete($c, $con);
        $con->commit();
    } catch (\Exception $e) {
        $con->rollBack();
        throw $e;
    }
}

function cleanup(PropulsionPDO $con): void
{
    $con->beginTransaction();
    try {
        deleteBooksWithNoPrice($con); // nested: no real commit here
        $con->commit();               // this one actually commits
    } catch (\Exception $e) {
        $con->rollBack();
        throw $e;
    }
}

If an exception surfaces from a nested transaction, it propagates up to the outermost catch, so the entire outer transaction rolls back — as long as every nested catch rethrows. This lets you compose transactional functions freely without worrying about nesting depth.

Savepoints and platform differences

On every supported platform, a nested beginTransaction() issues a real savepoint, and a nested rollBack() undoes only that scope’s work while leaving the outer transaction committable. PostgreSQL, MySQL/MariaDB, SQLite, and Oracle use standard SAVEPOINT / ROLLBACK TO SAVEPOINT; MSSQL uses T-SQL’s SAVE TRANSACTION / ROLLBACK TRANSACTION.

<?php
$con->beginTransaction();
$book->save($con);

// A best-effort sub-operation that's allowed to fail.
$con->beginTransaction();
try {
    $optional->save($con);
    $con->commit();          // releases the savepoint
} catch (\Exception $e) {
    $con->rollBack();        // ROLLBACK TO SAVEPOINT: undoes only $optional
}

$con->commit();              // the book is still committed

Neither Oracle nor MSSQL has an explicit “release savepoint” statement, and neither needs one — a savepoint is discarded when the outer transaction commits (and on Oracle, reusing a savepoint name replaces the old one). Propulsion accounts for both.

Using transactions to boost performance

Each transaction has its own overhead, which for many small writes can dominate the cost of the queries themselves. Wrapping a batch of otherwise-independent save() calls in one outer transaction collapses their implicit inner transactions into a single commit:

<?php
$con = Propulsion::getWriteConnection(\Map\BookTableMap::DATABASE_NAME);
$con->beginTransaction();
for ($i = 0; $i < 2002; $i++) {
    $book = new Book();
    $book->setTitle($i . ': A Space Odyssey');
    $book->save($con); // nested — no per-row commit
}
$con->commit();

Always pass the connection explicitly

Every example on this page passes $con explicitly to findPk()/save(). Propulsion can resolve a connection on its own if you omit it, but passing it explicitly is worth doing anyway:

Propulsion skips looking the connection up, a small performance win.
It lets you target a specific connection — required in master/replica setups, to keep reads and writes apart.
Most importantly, a transaction is tied to one connection. Two queries against different connections can never share a transaction, so Propulsion throws if you mix them.

Limitations

Unless you’re deliberately using savepoints as shown above, a nested transaction’s catch block should always rethrow — swallowing the exception risks the outer transaction never rolling back.
If you roll back and then ignore the thrown exception anyway, some objects can end up out of sync with the database. Let a transaction exception propagate until it stops execution rather than catching and discarding it.

Connection resilience — retrying a transaction that lost a deadlock or a serialization check, and what is deliberately never retried.
Locking — SELECT ... FOR UPDATE/FOR SHARE and optimistic concurrency, both of which need a transaction.
Unit of Work — batching a whole graph of writes into one ordered, transactional flush.