Database Transactions
Learn how to ensure data consistency using database transactions, what ACID means, and what we can do when transactions are unavailable.
Relational databases provide us with a tool for improved consistency. We can feel secure knowing that our application can crash during a complex update because of database transactions.
Some updates require multiple database queries, and an error in the middle of an update can leave the database in an invalid state. Database transactions solve this issue by grouping multiple SQL commands into one big update.
Why do we need transactions?
Let’s look again at our example schema for a theater application. This time we’re interested in the orders and tickets tables. And, this time, the orders table has an amount property containing the amount of money we want to request from the user.
To persist the order data, we need to create an order record and multiple ticket records. We need at least two queries for that, more if we save tickets one by one. If our application suddenly stops in the middle of an update, we’ll leave some tickets uncreated, and that’s a big problem. No one wants to order two tickets, pay for both, and receive one ticket instead. Situations like that would require manual resolution.
Any application can suddenly stop at any moment. It doesn’t have to be buggy to crash randomly. Solar storms and sudden server turnoffs can stop even perfect applications. Also, the error could originate from the database. What if the seat we’re trying to reserve was already reserved by ...