Answer

In SQL, transactions allow grouping several operations (insert/update/delete) into a single atomic unit of work that can either be completely applied or rolled back. The lifecycle of a transaction is built on commands:

• BEGIN or START TRANSACTION — start of the transaction;
• COMMIT — commit the changes;
• ROLLBACK — roll back all changes within the transaction.

SQL supports transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable), which determine the visibility of data between parallel transactions and protect against issues like "dirty reads" or "phantom rows".

To ensure data integrity, it's necessary to carefully:

• Choose the isolation level (for example, for banking applications — most likely, Serializable).
• Explicitly manage transactions, especially where one entity is edited concurrently (for example, SELECT ... FOR UPDATE).

Example in PostgreSQL:

BEGIN;

-- Get and lock the product row
SELECT * FROM inventory WHERE id = 1 FOR UPDATE;

UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;

COMMIT;

Trick Question

What is the default isolation level set in popular DBMS (PostgreSQL, MySQL) and how does it differ from SERIALIZABLE?

Answer:
In PostgreSQL, the default level is Read Committed — in this level, the transaction sees only committed data at the time of the request, but "non-repeatable reads" are possible.
In MySQL (InnoDB) — Repeatable Read. The difference from Serializable is that only the latter completely prevents any phantom or concurrent modifications, but works significantly slower due to global locks.

Example:

-- In Repeatable Read SELECT may return the same rows, while in Read Committed — new rows may appear between two SELECTs within the transaction.

---

History

In a large financial system, during mass transfers between accounts at a low isolation level (Read Committed), situations periodically arose where the same balance was used simultaneously by multiple transactions. This led to double spending (race condition). After switching to Serializable and proper lock management, the issue was resolved.

---

History

In e-commerce, a transaction with UPDATE product SET stock = stock - 1 without wrapping it in a transaction led to selling more products than available in stock. The problem was identified only with a large number of competing orders. The solution was to use transactions and row locking via SELECT ... FOR UPDATE.

---

History

In a logistics system, one table was frequently updated without an explicit commit. In case of failures, some data was lost due to autocommit or incorrect rollback. The result was the loss of records and costly auditing.