Two phase locking is claimed to be a solution for ensuring serial execution. However, I'm not sure how it adequately solves the lost update problem during a read-modify-write cycle. I may be overlooking / misunderstanding the locking mechanism here!

For example, assuming we have a database running using 2PL:

Given a SQL table account with an integer column email_count, lets assume we have the following record in our database:

| ID | email_count |

| ----- | ----- |

| 1    | 0  |

Now lets assume we have two concurrently executing transactions, T1 and T2. Both transactions will read email_count from accounts where ID = 1, increment the count value by 1, and write back the result.

Here's one scenario in which 2PL does not seem to address the lost update problem (T1 represents transaction 1):

T1 -> Obtains a non-exclusive, shared read lock. Read from email_count for ID = 1. Gets the result 0. Application sets a new value (0 1 = 1) for a later write.

T2 -> Also obtains a non-exclusive, shared read lock. Read from `email_count' for ID = 1. Gets the result 0. Application also sets a new value (using a now stale pre-condition), which is 1 (0 1 = 1).

T1 -> Obtains an exclusive write lock and writes the new value (1) to our record. This will block T2 from writing.

T2 -> Attempts to obtain write lock so it can write the value 1, but is forced to wait for T1 to complete its transaction and release all of T1's own locks.

Now here's my question:

Once T1 completes and releases its locks (during the "shrink" phase of our 2PL), T2 still has a stale email_count value of 1! So when T1 completes and T2 proceeds with its write (with email_count = 1), we'll "lose" the original update from T1.

CodePudding user response：

If T2 has read-lock, T1 cannot acquire an exclusive lock until T2 releases the read lock. Thus, the execution sequence you describe cannot happen. T1 would be denied the write lock, and T2 continues the transaction.