Learn Atomicity

SQL Optimization and Query Features

Swipe to show menu

Atomicity is a fundamental property of database transactions that guarantees all operations within a transaction are treated as a single, indivisible unit. This means that either every operation in a transaction is completed successfully, or none of them are applied to the database. Think of atomicity as an all-or-nothing rule. Imagine you are transferring money from one bank account to another using the bank_accounts table. The transfer should only happen if both the debit from one account and the credit to the other account succeed. If something goes wrong after debiting but before crediting, you do not want the money to disappear or appear twice. Atomicity ensures that you never end up in this inconsistent state.

For example, suppose you want to move $1,000 from Alice Johnson's account to Bob Smith's. This involves two steps: decreasing Alice's balance and increasing Bob's balance. Both steps must succeed, or neither should take effect. If a system crash or error occurs after Alice's account is debited but before Bob's account is credited, atomicity ensures that the entire transaction is undone, so both account balances remain unchanged.


              12345678910111213141516171819
            
-- Begin a transaction to transfer $1000 from Alice (account_id 1) to Bob (account_id 2)
BEGIN;

UPDATE bank_accounts
SET balance = balance - 1000
WHERE account_id = 1;

-- Simulate an error, such as insufficient funds or a system issue
-- RAISE EXCEPTION 'Simulated error: transfer failed';

UPDATE bank_accounts
SET balance = balance + 1000
WHERE account_id = 2;

-- If all statements succeed, commit the transaction
COMMIT;

-- Query balances to see the effect of the transaction
SELECT account_id, owner_name, balance FROM bank_accounts;

In the code sample above, you start a transaction with BEGIN. The first UPDATE statement deducts $1,000 from Alice's account. If an error occurs before the second UPDATE (for example, due to a simulated exception or a system failure), the transaction is not committed. Instead, a ROLLBACK command is issued, which undoes all changes made in the transaction so far. This means Alice's balance returns to its original value, and Bob's balance remains unchanged. Rollback is the mechanism that enforces atomicity in SQL: it ensures that, if anything goes wrong before the transaction is committed, none of the partial changes are saved to the database. Only when all parts of the transaction complete successfully do you use COMMIT to make the changes permanent.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 2

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 1. Chapter 2

Atomicity

1. What happens if a transaction fails after partially updating data?

2. Which SQL command is used to undo a transaction?

3. How does atomicity relate to error handling in SQL?