Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

What are database transactions?

Abel Lifaefi Mbula

Overview

In simple words, a transaction or a unit of work is a set of database operations that we want to treat as  "a whole." It has to either happen completely or not at all.

Visual representation of database transaction

To ensure the correctness of a transaction, a database must be atomic, consistent, isolated, and durable. These four properties are commonly known under the acronym ACID.

Atomicity

More often, but not always, a transaction is made of multiple SQL statements. The atomic property states that all the statements must either be complete entirely or have no effect whatsoever. No partial execution should be permitted. The idea is that a transaction must always leave the database in a consistent state. This leads us to the second property.

Consistency

From the previous explanation, we understand that the transaction must only bring the database from one valid state to another. This property ensures that there are no database constraints violations.

Isolation 

The isolation property guarantees that the uncommitted state changes are not visible or do not affect other concurrent transactions.

Durability

This property states that a committed transaction must permanently change the state of the database, even in case of a system failure like a power outage or crash. This implies that a successful transaction must always be recorded in non-volatile memory and/or a persisted transaction log.

Code example

A typical example to understand transactions is bank transfers.

Let's say we have two clients in our database, Sarah and John. The first one wants to transfer an amount of 20 dollars to the second client. Here's the process so far:

  1. Decrease Sarah's total amount by 20 dollars
  2. Increase John's total amount by 20 dollars

But the problem is that both of these two operations must be fully completed (commit) or not at all (rollback).

Let's make sure our database works properly:

SELECT *
FROM client;
Checking data in the database

Now we'll proceed with our operations above.

The first operation looks like this:

UPDATE client
SET amount = amount - 20
WHERE id = 1;

SELECT * FROM client
Decreasing Sarah's total

Let's implement the second operation:

UPDATE client
SET amount = amount + 20
WHERE id = 2;

SELECT * FROM client
Increasing John's total

The operation is completed. We run two separate operations in this process. The best thing to do is to run both of these as ONE operation. This leads us to transactions.

BEGIN TRANSACTION; -- <-- transaction begins here

-- decrease first
UPDATE client
SET amount = amount - 20
WHERE id = 1;

-- than increase
UPDATE client
SET amount = amount + 20
WHERE id = 2;

COMMIT TRANSACTION; -- <-- transaction ends here

SELECT * FROM client
Example of the complete transaction

RELATED TAGS

sql
RELATED COURSES

View all Courses

Keep Exploring