PHP PDO::commit Method

last modified April 19, 2025

The PDO::commit method in PHP finalizes a transaction, making all changes permanent in the database. It's used with PDO's transaction capabilities.

Basic Definition

PDO::commit commits the current transaction. It makes all changes made since the transaction began permanent. The method returns true on success.

Syntax: public PDO::commit(): bool. It throws a PDOException if no transaction is active or if the commit fails.

Simple Transaction Commit Example

This shows a basic transaction with commit.

pdo_commit_basic.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')");
    $pdo->commit();
    
    echo "Transaction committed successfully";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage();
}

This starts a transaction, inserts a record, and commits it. If any error occurs, the transaction is rolled back. The commit makes the insert permanent.

Multiple Operations in Transaction

This demonstrates committing multiple operations as a single transaction.

pdo_commit_multiple.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->beginTransaction();
    
    $pdo->exec("UPDATE products SET stock = stock - 5 WHERE id = 101");
    $pdo->exec("INSERT INTO orders (product_id, quantity) VALUES (101, 5)");
    
    $pdo->commit();
    echo "Order processed successfully";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Order failed: " . $e->getMessage();
}

This updates product stock and creates an order in one transaction. Both operations succeed or fail together. The commit applies both changes.

Commit with Prepared Statements

This shows committing a transaction using prepared statements.

pdo_commit_prepared.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->beginTransaction();
    
    $stmt = $pdo->prepare("INSERT INTO logs (message, created_at) VALUES (?, NOW())");
    $stmt->execute(['System started']);
    $stmt->execute(['User logged in']);
    
    $pdo->commit();
    echo "Log entries committed";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Logging failed: " . $e->getMessage();
}

This inserts multiple log entries using prepared statements in a transaction. The commit ensures all inserts are saved together. Prepared statements are safer.

Conditional Commit

This demonstrates committing only if certain conditions are met.

pdo_commit_conditional.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->beginTransaction();
    
    $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt->execute([100, 1]);
    
    $balance = $pdo->query("SELECT balance FROM accounts WHERE id = 1")->fetchColumn();
    
    if ($balance >= 0) {
        $pdo->commit();
        echo "Transaction committed";
    } else {
        $pdo->rollBack();
        echo "Insufficient funds - transaction rolled back";
    }
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage();
}

This checks account balance before committing. The transaction only commits if the balance remains positive. Otherwise, it rolls back the changes.

Nested Transactions with Commit

This shows how PDO handles nested transactions (emulated).

pdo_commit_nested.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->beginTransaction(); // Outer transaction
    
    $pdo->exec("INSERT INTO events (name) VALUES ('Process started')");
    
    try {
        $pdo->beginTransaction(); // Inner transaction
        $pdo->exec("UPDATE counters SET value = value + 1 WHERE name = 'events'");
        $pdo->commit(); // Commits inner transaction
    } catch (Exception $e) {
        $pdo->rollBack(); // Rolls back inner transaction
        throw $e;
    }
    
    $pdo->commit(); // Commits outer transaction
    echo "All transactions committed";
} catch (PDOException $e) {
    $pdo->rollBack(); // Rolls back outer transaction
    echo "Error: " . $e->getMessage();
}

PDO doesn't support true nested transactions but emulates them. Each commit corresponds to its beginTransaction. The outer commit makes all changes permanent.

Commit with Error Handling

This demonstrates proper error handling around commit operations.

pdo_commit_error_handling.php

<?php

declare(strict_types=1);

$pdo = null;

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->beginTransaction();
    $pdo->exec("DELETE FROM temp_data WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY)");
    
    if (!$pdo->commit()) {
        throw new Exception("Commit failed");
    }
    
    echo "Old data cleaned successfully";
} catch (PDOException $e) {
    if ($pdo && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Database error: " . $e->getMessage();
} catch (Exception $e) {
    if ($pdo && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Error: " . $e->getMessage();
}

This shows robust error handling around commit. It checks the commit return value and ensures rollback happens in all error cases. The inTransaction check prevents errors.

Commit in a Database Class

This demonstrates commit used within a database wrapper class.

pdo_commit_class.php

<?php

declare(strict_types=1);

class Database {
    private PDO $pdo;
    
    public function __construct(string $dsn, string $user, string $pass) {
        $this->pdo = new PDO($dsn, $user, $pass);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    
    public function transferFunds(int $from, int $to, float $amount): bool {
        try {
            $this->pdo->beginTransaction();
            
            $stmt = $this->pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
            $stmt->execute([$amount, $from]);
            
            $stmt = $this->pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
            $stmt->execute([$amount, $to]);
            
            $this->pdo->commit();
            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            return false;
        }
    }
}

$db = new Database('mysql:host=localhost;dbname=testdb', 'user', 'password');
if ($db->transferFunds(1, 2, 50.00)) {
    echo "Funds transferred successfully";
} else {
    echo "Transfer failed";
}

This encapsulates transaction handling in a class method. The commit happens only if both updates succeed. The method returns a boolean indicating success.

Best Practices

Always check return value: Commit can fail silently.
Use try-catch: Handle potential PDOExceptions.
Keep transactions short: Minimize lock duration.
Verify inTransaction: Before commit or rollback.
Test error scenarios: Ensure proper rollback behavior.

Source

PHP PDO::commit Documentation

This tutorial covered the PDO::commit method with practical examples showing transaction handling in different scenarios.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all PHP PDO Functions.

Ebooks