SQL Transactions

Imagine you're writing in a diary. Sometimes, you might write multiple things that are related, like events of a single day. If you make a mistake, you'd want to erase all related writings and start over.

In databases, a "transaction" is like that day's entry. It's a group of operations that either all happen successfully together or don't happen at all. If something goes wrong in the middle, transactions allow the database to "erase" or undo the changes, ensuring everything stays consistent and error-free.

Begining a Transaction

To begin a transaction with PDO, you use the beginTransaction() method on a PDO instance.

$pdo->beginTransaction();

After calling beginTransaction(), subsequent database operations will be part of the transaction.

For example:

// Start the transaction
$pdo->beginTransaction();
 
// Execute some database operations using query()
$pdo->query("INSERT INTO students (name) VALUES ('John')");
$pdo->query("INSERT INTO students (name) VALUES ('Jane')");

In this example, the query() method is used to execute SQL statements. The key thing to note is that within the context of the transaction started by beginTransaction(), if any operation (like the query() calls) fails, the entire set of operations can be undone. They're all a part of the same transaction.

Committing a Transaction

After you've performed all the queries you'd like to perform, the next step is to commit the transaction. To "commit" a transaction means to finalize or save all the changes made during that transaction to the database. Once a transaction is committed, the modifications become permanent, and other database users can see them.

In the context of PDO, committing a transaction is the process of confirming that every operation within the transaction has been completed successfully, and thus, all the changes should be saved.

To commit a transaction with PDO, you use the commit() method on a PDO instance.

try {
  // Start the transaction
  $pdo->beginTransaction();
 
  // Perform some database operations
  $pdo->query("INSERT INTO students (name) VALUES ('John')");
  $pdo->query("INSERT INTO students (name) VALUES ('Jane')");
 
  // Commit the changes
  $pdo->commit();
} catch (Exception $e) {
  echo "Error: " . $e->getMessage();
}

In this example, after starting a transaction and executing some operations, we commit the transaction using $pdo->commit(). This will save the changes (inserting 'John' and 'Jane' into the students table) to the database.

When using transactions, it's recommended to wrap them with try-catch statements to catch errors. If there was any problem during the transaction, the error gets outputted.

Rolling Back a Transaction

If a transaction fails, you must rollback any queries performed. To "rollback" a transaction means to undo or revert all the changes made during that transaction. If, during a transaction, an error occurs or if, for any other reason, you decide not to continue with the operations, you can rollback to bring the database back to the state it was in before the transaction started. This ensures the database remains in a consistent state.

Rolling back a transaction is particularly useful in situations where a sequence of database operations must all succeed together; if one fails, all the preceding operations in the transaction are undone.

In the context of PDO, rolling back a transaction is done using the rollback() method on a PDO instance.

Here's an example:

try {
  // Start the transaction
  $pdo->beginTransaction();
 
  // Perform some database operations
  $pdo->query("INSERT INTO students (name) VALUES ('John')");
 
  // Let's say we intentionally cause an error here
  $pdo->query("INSERT INTO non_existent_table (name) VALUES ('Jane')");
 
  // Commit the changes (this line won't be reached due to the error above)
  $pdo->commit();
} catch (Exception $e) {
  // If there's any error, rollback the transaction
  $pdo->rollback();
  echo "Error: " . $e->getMessage();
}

In the example above, the second query will cause an error since the table non_existent_table doesn't exist. Because of the error, the catch block is executed, and $pdo->rollback() is called. This means the first query, which inserted 'John' into the students table, will be undone, and 'John' won't be added to the database.

Key Takeaways

• A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Either all operations in the transaction succeed or none do.
• In PDO, start a transaction with the beginTransaction() method.
• "Committing" a transaction means finalizing or saving all the changes made during the transaction. In PDO, you commit using the commit() method.
• If an error occurs or if you decide not to proceed with the operations in the transaction, you can "rollback" or undo all the changes. In PDO, use the rollback() method.
• Transactions ensure data consistency. If any operation in the transaction fails, the entire set of operations can be rolled back, keeping the database in a consistent state.
• Combining transactions with try-catch blocks in PDO is a common pattern. If an exception is thrown within the transaction, the catch block can handle the error and decide whether to rollback.