Transactions

Transactions guarantee that all operations within a transaction are either executed successfully or none of them are executed. They are useful for maintaining data consistency in more complex operations.

Basic Usage

The simplest way to use transactions looks like this:

$db->beginTransaction();
try {
    $db->query('DELETE FROM articles WHERE id = ?', $id);
    $db->query('INSERT INTO audit_log', [
        'article_id' => $id,
        'action' => 'delete'
    ]);
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

You can achieve the same result more elegantly with the transaction() method:

$db->transaction(function ($db) use ($id) {
    $db->query('DELETE FROM articles WHERE id = ?', $id);
    $db->query('INSERT INTO audit_log', [
        'article_id' => $id,
        'action' => 'delete'
    ]);
});

The transaction() method can also return values:

$count = $db->transaction(function ($db) {
    $result = $db->query('UPDATE users SET active = ?', true);
    return $result->getRowCount(); // returns the number of rows updated
});

Nested Transactions

Nette Database supports nested transactions using SQL savepoints. This means you can start a transaction inside another transaction. Here's a simple example:

$db->transaction(function ($db) {
    // Main transaction
    $db->query('INSERT INTO users', ['name' => 'John']);

    // Nested transaction
    $db->transaction(function ($db) {
        $db->query('UPDATE users SET role = ?', 'admin');
        // If an error occurs here, only the nested transaction will be rolled back,
        // while the main transaction will continue.
    });

    // Continuing the main transaction
    $db->query('INSERT INTO user_log', ['action' => 'user created']);
});

Internally, only a single database transaction is used, and nested transactions are simulated using savepoints. This behavior is consistent across all databases and is completely transparent to the developer.

Auto-commit Mode

Auto-commit determines whether each query is automatically executed in a separate transaction. By default, auto-commit is enabled, meaning every query forms a separate transaction.

You can disable auto-commit in the configuration:

database:
    dsn: 'mysql:host=127.0.0.1;dbname=test'
    user: root
    password: secret
    options:
        autoCommit: false    # disables auto-commit

Or in the code:

$db->setAutoCommit(false);

When auto-commit is disabled, a new transaction automatically starts in the following cases:

• When connecting to the database.
• After the previous transaction is completed (commit or rollback).

If you change the auto-commit setting while a transaction is active, the current transaction will be automatically committed to maintain consistency.