0

Say I have 3 threads, T1, T2 and T3 from an application all starting at the exact same time. They all read the same value from a certain table, row and column. Lets say this value is 50.
1. T1 is fast. Completes in 100ms. Upon completion, it updates the value it read with +5. So, it writes 55 in the database.
2. T2 is a bit slower. Completes in 700ms. And upon completion, it adds +10 to the value it read. So it updates the data with 60.
3. T3 is even slower and completes in 1300ms. On completion this thread adds +15. So it the value becomes 65.

The solution I'm hoping to learn from SO is how does one handle the data consistency in such cases. Since, clearly at the end of T3 the value should be 80 (50 + 5 T1 + 10 T2 + 15 T3) as opposed to 65.

Do let me know if I can make my question any clearer if needed.

latestVersion
  • 458
  • 1
  • 6
  • 17

1 Answers1

0

Well, given that we're talking about an SQL database, you must use transactions with serializable isolation level. Read about it here.

forty-two
  • 12,204
  • 2
  • 26
  • 36
  • Based on what I understand from the link, Serializable isolation level puts a read/write lock on the data that is read. What would happen in case all 3 threads access the same exact data at the same exact time (say down to nanoseconds). Would it then have to be some sort isolation level on the Application side rather than database? – latestVersion Apr 26 '18 at 15:59
  • Can you point me to some doc which addresses transaction management where multiple transactions start at the exact instant in time? I'm not able to picture how the DB would handle that. How does it keep track of its current transactions to figure out how it should handle Serializable isolation. – latestVersion May 01 '18 at 14:00
  • This is "ordinary" lock management; only one entity can own the write lock. Granting a write lock has to be serialized as well, so "exact instant in time" doesn't apply. – forty-two May 02 '18 at 10:26