postgres

Lost Update

The lost update anomaly occurs when two transactions read the same table row, then one of the transactions updates this row, and finally the other transaction updates the same row without taking into account any changes made by the first transaction. Suppose that two transactions are going to increase the balance of the same account by $���. The first transaction reads the current value ($�,���), then the second transaction reads the same value. The first transaction increases the balance (making it $�,���) and writes the new value into the database. The second transaction does the same: it gets $�,��� after increasing the balance and writes this value. As a result, the customer loses $���.

Dirty Reads and Read Uncommitted

The dirty read anomaly occurs when a transaction reads uncommitted changes made by another transaction. For example, the first transaction transfers $��� to an empty account but does not commit this change. Another transaction reads the account state (which has been updated but not committed) and allows the customer to withdraw the money—even though the first transaction gets interrupted and its changes are rolled back, so the account is empty. The standard allows dirty reads at the Read Uncommitted level.

Non-Repeatable Reads and Read Committed

The non-repeatable read anomaly occurs when a transaction reads the same row twice, whereas another transaction updates (or deletes) this row between these reads and commits the change. Consequently, the first transaction gets different results. For example, suppose there is a consistency rule that forbids having a negative balance in bank accounts. The first transaction is going to reduce the account balance by $���. It checks the current value, gets $�,���, and decides that this operation is possible. At the same time, another transaction withdraws all the money from this account and commits the changes. If the first transaction checked the balance again at this point, it would get $� (but the decision to withdraw the money is already taken, and this operation causes an overdraft). The standard allows non-repeatable reads at the Read Uncommitted and Read Committed levels.