Transactions

Harry Bui — Thu, 16 Jan 2025 17:15:55 +0000

In this section, we will explore the concept of transactions and ACID properties—an important topic in database management to ensure data integrity and reliability.

What is a Transaction?

Imagine a database transaction as the action of withdrawing money from your bank account. You need to perform multiple actions, such as checking your balance, entering the amount to withdraw, and completing the withdrawal. You expect the entire process to run smoothly without errors.

Similarly, a database transaction is a unit of work that consists of a collection of queries, which must be executed as a whole or not at all. This all-or-nothing principle ensures data integrity and reliability. Transactions are primarily used to change or modify data.

Transaction Lifespan

Use BEGIN to start a transaction.
Once all operations are successfully performed, use COMMIT to finalize and save the changes to the database.
If any errors occur during the transaction, use ROLLBACK to undo all changes that have not yet been saved to the database.

Example: How a Transaction Works

Scenario: Transferring money between bank accounts.

Table: `bank_accounts`

id	balance
1	$1000
2	$200

Transaction Steps:

User 1 wants to send $100 to User 2.

BEGIN; -- Start Transaction
SELECT balance FROM bank_accounts WHERE id = 1; -- Check User 1's balance (should be > $100)
UPDATE bank_accounts SET balance = balance - 100 WHERE id = 1; -- Deduct $100 from User 1
UPDATE bank_accounts SET balance = balance + 100 WHERE id = 2; -- Add $100 to User 2
COMMIT; -- Finalize and save the transaction

Conclusion

This example demonstrates how transactions work in practice to ensure data integrity. By wrapping the operations in a transaction, we guarantee that the money transfer is completed entirely or not at all, preventing issues like incorrect balances if an error occurs midway.

In the next section, we will explore the ACID properties, which further define how transactions ensure consistency, durability, and reliability in databases.

Series: Learn Database Fundamental

Harry Bui — Thu, 16 Jan 2025 16:28:40 +0000

Introduction

In this series, I will note and share all things I learn about Database Fundamentals. This series will not focus on the SQL language syntax. Instead, we will focus on topics that you can apply in database engineering, which are common across all databases (e.g., MySQL, SQL Server, PostgreSQL, etc.).

For this series, we will primarily discuss PostgreSQL and cover the following topics:

Transactions
ACID Properties
Database Internals
Index in Database
Explain B-Tree and B+Tree
Partition, Sharding, Replica in Database
Locking
Normalization and Denormalization
How to Design a Database: Step-by-Step

Ask for Help or Give Feedback

If you need any assistance or have feedback to share, you can reach out to me through the following channels:

Email: mr.flooo1230@gmail.com
GitHub: harrybui03
LinkedIn: Harry Bui

DEV Community: Harry Bui