MD Pabel
Back to Notes

Database Transactions and Concurrency

MD Pabel

MD Pabel / Apr 1, 2024

6 min read

Table of contents

A transaction is a sinlge unit of work that execute one or more SQL statements. It represents a single unit of work that should be executed as a whole, either entirely successful or entirely unsuccessful.

Loading code...

In the provided example, a transaction starts with START TRANSACTION, executes SQL statements to insert data into tables, and then commits the transaction with COMMIT. If any error occurs during the transaction, it can be rolled back using ROLLBACK.

ACID Properties:

  1. Atomicity: Transactions should be like a single, unbreakable action. Everything inside a transaction should succeed or fail together, so we don't end up with partial changes that mess things up.

  2. Consistency: Transactions keep the database in a good, consistent state. For example, if we add an order, we also need to add the items for that order to keep things sensible.

  3. Isolation: Transactions should happen in their own little world, so they don't mess each other up. Each transaction should wait its turn to make changes and not interfere with others.

  4. Durability: Once a transaction is done, its changes should stick around even if something bad happens like a power outage or a system crash.

Concurrency Problems:

  1. Lost Updates: If two actions try to update the same thing at the same time, one might overwrite the other and cause confusion.

Imagine two people are updating the same data at the same time. Let's say we have a customers table:

Person A and Person B both try to update the points of customer with ID 1:

Loading code...

In this scenario, if Person B's transaction commits after Person A's, Person B's changes will override Person A's changes, resulting in a lost update.

  1. Dirty Reads: Imagine if one action reads data that's in the middle of being changed by another action. It might get incorrect or messy information.

Let's say we have a products table:

Person A is updating the price of a product, and Person B is trying to read the price while Person A's transaction is still ongoing:

Loading code...

In this case, Person B might see the updated price even though Person A's transaction hasn't finished yet, resulting in a dirty read.

  1. Non-Repeatable Reads: If an action reads the same data multiple times, but it changes between reads because of other actions, that's a non-repeatable read.

Continuing from the previous example, let's say Person B tries to read the price of the same product again:

Loading code...

If Person A commits the transaction and updates the price to 15.99 before Person B's second read, Person B will see different prices in the two reads, causing a non-repeatable read.

  1. Phantom Reads: This is like reading a book where pages magically appear or disappear as you flip through. If one action sees a set of data, and then another action changes the data so the first action sees something different, that's a phantom read.

Suppose we have an orders table:

Loading code...

Person A is counting the total number of orders, and Person B is inserting a new order:

Loading code...

In this scenario, if Person A's transaction finishes before Person B's insertion, Person A might count fewer orders than there actually are, resulting in a phantom read.

Transaction Isolation Levels:

  1. READ UNCOMMITTED: This is the lowest isolation level where transactions can see uncommitted changes made by other transactions, leading to dirty reads.
Loading code...
  1. READ COMMITTED: This isolation level ensures that transactions only see committed changes made by other transactions, preventing dirty reads. However, it may still result in non-repeatable reads. Example:
Loading code...
  1. READ COMMITTED: This isolation level ensures that transactions only see committed changes made by other transactions, preventing dirty reads. However, it may still result in non-repeatable reads. Example:
Loading code...
  1. SERIALIZABLE: This is the highest isolation level that ensures complete isolation between transactions, preventing all concurrency problems (lost updates, dirty reads, non-repeatable reads, and phantom reads). Example:
Loading code...

Deadlocks:

  • A deadlock occurs when two or more transactions are waiting for each other to release locks on resources that they need. This results in a standstill where none of the transactions can proceed.
  • Deadlocks can be detected and resolved by the database management system, typically by aborting one of the transactions involved in the deadlock.

Transactions — MongoDB

In MongoDB, transactions provide ACID properties (Atomicity, Consistency, Isolation, Durability) for operations that modify multiple documents or collections.

Loading code...

Transactions — Redis

Redis, being an in-memory data store, does not natively support transactions in the same way as traditional relational databases. However, Redis provides a form of atomicity for multiple commands through its MULTI/EXEC transaction mechanism.

Loading code...