Lost Update | Dirty Read | Unrepeatable Read | Phantom Read

Lost Update
丢失更新：提交或者回滚一个事务，把其它事务已提交的更新的数据覆盖了。
If the two transactions want to change the same columns, the second transaction will overwrite the first one, therefore losing the first transaction update.

In this example Bob is not aware that Alice has just changed the quantity from 7 to 6, so her UPDATE is overwritten by Bob’s change.

A lost update is a typical problem in transaction processing in SQL. It happens when two queries access and update the same data from a database. This problem can be understood by the below given diagram. Here, A is processing an order for a client for 150 items. He checks from the ITEMS table that there are 300 available items. So he starts placing the order. After few seconds B gets an order for 200 items. He also checks the items table and finds that there are 300 items available. So he also starts placing an order. Meanwhile, a confirms the order for 150 items and updates the ITEMS table and sets the quantity to 150. A few seconds later B confirms the order and updates the ITEMS table and sets the quantity to 100. This problem is known as lost update problem because both the orders of the users A and B have been accepted, but there is not enough items available. Hence, the updates are lost.

The lost Update Problem in the Context of Transactions
The Lost Update Problem - Part 2: Preventing Lost Updates in Web Applications
The Lost Update Problem - Part 3 : Applied Prevention

Ditry Read(Uncommitted Data)
脏读：一个事务读到另一个事务未提交的更新数据。
Dirty Read occurs when a transaction reads data from a row that has been modified by another transaction, but has not yet committed.

We come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value.


In a dirty read problem, A is processing an order for a client for 150 items. He checks from the ITEMS table that there are 300 available items. So he starts placing the order. A confirms the order for 150 items and updates the ITEMS table and sets the quantity to 150. Now, B receives an order for 200 items. He checks the ITEMS table to find that there is not enough inventories (150 available) and rejects the order. By using business rules and transactions a note is sent informing that more items are required. Now, due to some reason client asks A to cancel the order, so A cancels the order, rolls back and updates the ITEMS table back to 300 items. This problem is known as dirty read because B saw the uncommitted update of A.

Unrepeatable Read
不可重复读：一个事务两次读同一行数据，可是这两次读到的数据不一样。
A transaction reads a row twice and reads different state each time. For example, another transaction may have written to the row, and committed, between the two reads.

Non Repeatable Reads happen when in a same transaction same query yields different results. This happens when another transaction updates the data returned by other transaction.

Phantom Read
幻读：一个事务执行两次查询，但第二次查询比第一次查询多出了一些数据行。
A transaction executes a query twice, and the second result set includes rows that weren’t visible in the firstresult set. This situation is caused by another transactioin inserting new rows between the executions of the two queries.

Phantom read occurs where in a transaction same query executes twice, and the second result set includes rows that weren’t visible in the first result set. This situation is caused by another transaction inserting new rows between the execution of the two queries.