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%
Image for post
Image for post
Lost update occurs in “READ COMMITTED” isolation level in Oracle
Image for post
Image for post
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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store