Understanding database transaction level isolation

Problems that arise when transactions run simultaneously

When two transactions are running at the same time, one can read the data that the other transaction is changing. Isolation levels determine how much other transactions may affect each other's reading.

Read Committed

This level only allows transactions to read committed data. This is the default in many databases such as PostgreSQL and SQL Server.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
SELECT saldo FROM rekening WHERE id = 1;
-- transaksi lain bisa mengubah dan commit di antara dua SELECT ini
SELECT saldo FROM rekening WHERE id = 1;
COMMIT;

Two. SELECT above can produce different values because other transactions have committed in between. This condition is called non-repeatable read.

Repeatable Read

This level guarantees that the read line does not change in value during the transaction. MySQL with InnoDB uses this level as the default.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Repeatable Read can still experience phantom read, where a new line appears when the query with the same condition is repeated.

Serializable

This tightest level makes transactions seem to run one by one in a row. Consistency is most assured, but transaction risks must be repeated as conflicts also increase.

Choosing the right level

Tighter levels give higher consistency but reduce concurrency. Use Read Committed for most daily operations, and raise to tighter levels only on operations that are truly sensitive to data accuracy, such as balance calculations.

practice;

Try running two transactions in a separate window using Read Committed then Repeatable Read, and observe when each sees changes from the other transactions.