Oracle DB Transactions — Lost Updates

Transaction T1 reads a data item and then T2 updates the data item (possibly based on a previous read), then T1 (based on its earlier read value) updates the data item and commits

r1[x]…w2[x]…w1[x]…c1

💡 How to read the shorthand notations? w means write, r means read, number(1 or 2) indicates transaction id, c means commit and a means abort/rollback

Example:

Transaction 1

• Read the price of “product b”
• Increment the price by 10% — Assume some logic has been applied to the fetched data
• Update the price of “product b”

Transaction 2

• Updates the price of “product b” by 100%

Lost update occurs in “READ COMMITTED” isolation level in Oracle

Lost update does not occur in “SERIALIZABLE” isolation level in Oracle

💡Serializable isolation level in Oracle is actually a “Snapshot Isolation”

Transaction 1

Transaction 2