Transactions
************

.[perex]
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:

```php
$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:

```php
$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:

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


Nested Transactions .{data-version:4.0}
=======================================

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

```php
$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']);
});
```

.[note]
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 .{data-version:4.0}
====================================

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:

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

Or in the code:

```php
$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).

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