What are transactions in the context of databases and how do they work?

A transaction is a sequence of operations on a database that is treated as a single unit. Either all operations are successfully completed (commit), or none are applied (rollback). This ensures data integrity.

Transactions possess the ACID properties:

• Atomicity: The entire transaction is either fully completed or not executed at all.
• Consistency: Each successful transaction transforms the database from one consistent state to another.
• Isolation: The results of concurrently executing transactions do not affect each other.
• Durability: Changes made by a successful transaction are saved even in case of system failure.

Transactions work as follows:

1. Begin Transaction (BEGIN/START TRANSACTION): The database starts recording all subsequent changes but does not apply them immediately to permanent storage.
2. Execute operations: SQL queries (INSERT, UPDATE, DELETE) are performed.
3. Commit: All changes made during the transaction are permanently applied to the database and become visible to other transactions (considering the isolation level).
4. Rollback: In case of an error or need to cancel, all changes made since the start of the transaction are undone. The database reverts to the state before the transaction began.

Example of usage in PHP with PDO:

<?php
// Connecting to the database
$dbh = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');

try {
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Enable exceptions on errors
    $dbh->beginTransaction(); // Start transaction

    // Operations within the transaction
    $stmt1 = $dbh->prepare("INSERT INTO accounts (name, balance) VALUES (?, ?)");
    $stmt1->execute(['Alice', 1000]);

    $stmt2 = $dbh->prepare("UPDATE accounts SET balance = balance - ? WHERE name = ?");
    $stmt2->execute([100, 'Alice']);

    $stmt3 = $dbh->prepare("UPDATE accounts SET balance = balance + ? WHERE name = ?");
    $stmt3->execute([100, 'Bob']);

    $dbh->commit(); // Commit the transaction if all went well
    echo "Transaction completed successfully.";

} catch (Exception $e) {
    $dbh->rollBack(); // Rollback in case of error
    echo "Transaction error: " . $e->getMessage();
}
?>