Using Transactions to Prevent Database Errors

Marian Dziubiak
transactions, preventing errors, online banking, isolation

What happens when a database receives commands from two different users? We look at the problems that can arise and how to avoid them.

Every time you execute a statement in your database, you change the database’s state. When working in a multi-user environment with asynchronous database access, it will sometimes happen that two users are trying to change the same record at the same time. If both of their statements is an operation that changes the database’s state, this can create erroneous results.

Let’s illustrate this with something we are familiar with: bank accounts. You’ve probably spotted the occasional mistake in your bank statement that like a simple math error. How might this happen?

The Bank Example

Suppose we have a bank application and a user that is trying to withdraw $20 from their account and send it to PayPal. We’re going to experience an unexpected power outage in the middle of this transaction and check out the database’s state.

USER 
SELECT money FROM bank
WHERE account = “User1”
returns $358
 
Remove $20 from the account
UPDATE bank SET money = 338
WHERE account = “User1”
POWER OUTAGE

At this moment, the money is lost. It’s left the user’s account, but it wasn’t added anywhere and the system has failed.

What can we do in a situation like this?

Use the Transaction System

Most of today’s databases are transactional, meaning that they support transactions. Transactions are nothing more that groups of commands that have a defined beginning and end. A transaction is an atomic set of operations: either all of them will get executed or none will. In case a system fails, the previous valid state is preserved.

As we’ve seen, using individual commands to complete a multi-step change can be problematic. Maybe a power failure interrupts them and leaves the change half-done. Maybe another user unwittingly starts working on the same record. Either way, there will be errors unless transactions are used. The transaction system has been designed to prevent these errors.

Let’s see how our previous example looks when we use transactions instead of separate commands:

USER 
BEGIN
 
SELECT money FROM bank
WHERE account = “User1”
returns $358
 
Remove $20 from the account
UPDATE bank SET money = 338
WHERE account = “User1”
POWER OUTAGE

We start the transaction with the BEGIN clause (note that we didn’t use this in the first example) and we issue the same statements. Because of the power outage, the transaction can’t be finished. In this case, none of our changes actually happen – no money leaves the account.

Finishing the Transaction

After we have successfully executed all the statements in the transaction, it’s time to close it. If everything is correct, we enter a COMMIT statement and the transaction is executed in the database, changing its state. Otherwise, we use the ROLLBACK statement, which cancels our transaction and does not change the database.

Transactions are Isolated

Another thing to know about transactions is that they are isolated. This means that if one user is performing an operation on a record, the changes he or she makes are not visible to other users until the transaction is completed.

Let’s illustrate this by adding a second account user that PayPal is reimbursing $10. If this transaction is carried out at the same time as the first account user is sending $20 to PayPal, what happens?

USER 1 USER 2 
BEGIN
 
BEGIN
 
SELECT money FROM bank 
WHERE account = “User1” 
returns $358
 
SELECT money FROM bank 
WHERE account = “PayPal” 
returns $12854
 
Remove $20 from the account 
UPDATE bank SET money = 338 
WHERE account = “User1”
 
Remove $10 from the account 
UPDATE bank SET money = 12844 
WHERE account = “PayPal”
 
SELECT money FROM bank 
WHERE account = “PayPal” 
returns $12854
 
Add $20 to the account 
UPDATE bank SET money = 12874 
WHERE account = “PayPal”
 
SELECT money FROM bank 
WHERE account = “User2” 
returns $227
 
Add $10 to the account 
UPDATE bank SET money = 237 
WHERE account = “User2”
 
COMMIT
 
COMMIT

At the end of User 1’s transaction, there’s $12,874 in the PayPal account. At the end of User 2’s transaction, there’s $12,844. Depending which transaction is recorded first, there will be either $10 more or $20 less in the system than there should be.

Depending on the order of the users’ actions and their interlacing, these two transactions might be carried out correctly – or not. However, we like to have deterministic results, and we can get these when the transactions are properly isolated.

This situation can be avoided by setting the right isolation level and having one user wait until the other finishes their transaction. However, we won’t get into how to do that here.

Transactions are a great tool for preventing errors from unexpected failures and complicated logical scenarios. As you continue learning about SQL in Vertabelo SQL Academy, you’ll discover many ways to work with the data in your database. Why not check out one of our courses today?

Marian Dziubiak

Junior Technical Writer @ Vertabelo

Related Posts: