What are transactions used for in a database?

Transactions in a database ensure reliable and consistent execution of multiple operations as a single atomic action.

Main goals:

• Atomicity: All operations in a transaction are either fully successful or have no effect at all (rollback). This guarantees data integrity.
• Consistency: A transaction transitions the database from one consistent state to another. It does not violate existing constraints.
• Isolation: Concurrent transactions do not see intermediate results of each other until committed. This prevents issues like "dirty reads," "non-repeatable reads," and "phantom reads."
• Durability: After a transaction is successfully committed, the changes are saved and will not be lost even in case of system failure.

They are used for operations where it is necessary to guarantee the execution of all steps or none, for example, when transferring money between accounts.

Example illustrating atomicity:

-- Start of transaction
START TRANSACTION;

-- Withdraw funds from account A
UPDATE accounts SET balance = balance - 100 WHERE id = account_A_id;

-- Deposit funds into account B
UPDATE accounts SET balance = balance + 100 WHERE id = account_B_id;

-- Commit the transaction (if both operations are successful)
COMMIT;

-- Or rollback (if any operation fails)
-- ROLLBACK;