BEGIN, COMMIT, ROLLBACK

Imagine we are at an ATM trying to transfer money from our savings account to our checking account. This single operation involves two steps: deducting money from savings and adding it to checking. What if the ATM successfully deducts from savings but then crashes before adding to checking? We’d be short on cash, and the bank’s records would be inconsistent! This is precisely the kind of problem that database transactions, managed by commands like BEGIN, COMMIT, and ROLLBACK, are designed to prevent. They ensure that multi-step operations are completed fully, or not at all, keeping our data accurate and reliable.

In this lesson, we’ll explore these vital commands. By the end of our session, we will be able to:

• Use BEGIN or START TRANSACTION to initiate a transaction.

• Make changes permanent using the COMMIT command.

• Undo changes within a transaction using the ROLLBACK command.

• Appreciate how these commands help maintain the integrity and consistency of our database, especially in dynamic environments like our OnlineStore.

Let’s get started on ensuring our database operations are as reliable as they can be!

Starting a transaction: The BEGIN command

By default, many database systems, including MySQL when using the InnoDB storage engine (which is very common), operate in what’s called autocommit mode. In this mode, every single SQL statement we execute (like an INSERT, UPDATE, or DELETE) is treated as its own transaction and is automatically committed (saved) immediately after it runs successfully. This is simple for single, standalone statements, but for those multi-step operations we discussed, we need more control. The BEGIN (or its synonym START TRANSACTION) command allows us to tell the database, “Hold on, I’m about to perform a series of operations that belong together. Don’t save anything permanently until I explicitly say so.” This temporarily suspends the autocommit behavior for our current session, allowing us to group multiple commands.

The BEGIN statement marks the beginning of a new transaction. Any SQL statements executed after BEGIN are considered part of this transaction. The changes made by these statements are not immediately permanent and are not typically visible to other database sessions until the transaction is explicitly finalized with a COMMIT command. ...