Programming Languages

PHP MySQL Transactions

Use transactions for data consistency and rollback support

MySQLi Transactions

$conn->begin_transaction(); # start transaction
try { # error handling
    $conn->query("UPDATE accounts SET balance=balance-100 WHERE id=1"); # deduct money
    $conn->query("UPDATE accounts SET balance=balance+100 WHERE id=2"); # add money
    $conn->commit(); # save changes
} catch (Exception $e) { # handle error
    $conn->rollback(); # undo all changes
}

PDO Transactions

$pdo->beginTransaction(); # start transaction
try { # try block
    $pdo->exec("INSERT INTO orders (user_id, total) VALUES (1, 299.99)"); # create order
    $pdo->exec("UPDATE inventory SET stock=stock-1 WHERE product_id=5"); # reduce stock
    $pdo->commit(); # commit changes
} catch (Exception $e) { # catch error
    $pdo->rollBack(); # rollback transaction
}

Transaction Properties

$conn->autocommit(FALSE); # disable autocommit
$conn->commit(); # manually commit
$conn->rollback(); # rollback changes
$conn->autocommit(TRUE); # re-enable autocommit