Aprenda What is ACID?

Otimização de SQL e Recursos de Consulta

Deslize para mostrar o menu

When working with relational databases, you often hear about the importance of ACID properties. ACID stands for Atomicity, Consistency, Isolation, and Durability. These four principles are the foundation for reliable transaction processing. Each property plays a critical role:

Atomicity: guarantees that every transaction is treated as a single, indivisible unit. Either all operations in the transaction succeed, or none of them do;
Consistency: ensures that a transaction brings the database from one valid state to another, preserving all rules, constraints, and data integrity;
Isolation: means that concurrent transactions do not interfere with each other. Each transaction executes as if it were the only one running in the system;
Durability: makes sure that once a transaction has been committed, its changes will persist, even in the case of system failures.

Without these properties, databases would be prone to errors, data loss, and unpredictable outcomes, especially when handling multiple users or system crashes.


              12345678910111213141516171819
            
-- Begin a transaction for transferring $200 from Alice (account_id = 1) to Bob (account_id = 2)
BEGIN;

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

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

INSERT INTO transfers (from_account, to_account, amount)
VALUES (1, 2, 200.00);

-- Commit the transaction to make changes permanent
COMMIT;

-- View resulting balances of all accounts
SELECT account_id, owner_name, balance FROM bank_accounts;

Take a closer look at the transaction code above to see how the ACID properties work in practice. You start by beginning a transaction, then subtract $200 from Alice's account, add $200 to Bob's account, and record the transfer. The transaction is finalized with a COMMIT.

Atomicity: If any step fails — such as Alice not having enough funds — the entire transaction is rolled back. No money is deducted, and no transfer is recorded. This all-or-nothing behavior is the essence of atomicity.
Consistency: The transaction enforces business rules, such as not allowing negative balances or incorrect totals. If a rule would be violated, the transaction fails, and the database remains in a valid state.
Isolation: If another user tries to read or update Alice's or Bob's balances while this transfer is in progress, they will not see partial updates. Each transaction operates as if it is the only process touching the data.
Durability: Once the COMMIT is issued, the changes are saved to disk. Even if the database server crashes immediately after, the transfer record and updated balances will be there when the system restarts.

These properties work together to ensure your data remains accurate, reliable, and protected from both software errors and hardware failures.

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 1. Capítulo 1

Pergunte à IA

Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo

Seção 1. Capítulo 1

What is ACID?

1. Which ACID property ensures that a transaction is all-or-nothing?

2. Why is durability important in database transactions?

3. Which property prevents dirty reads in concurrent transactions?