Pessimistic lock and optimistic lock in database

Time:2019-12-7

Now let’s talk about pessimistic locks and optimistic locks in the database.

Pessimistic lock

Pessimistic lock, as its name suggests, is more pessimistic. It is always believed that every time the data is modified, other threads will also modify the data at the same time. So the pessimistic way to lock is to lock after reading the data(eg: select...for update)In this way, when other threads read the data, they need to wait for the current thread to release the lock, and the thread that obtains the lock can obtain the read-write permission of the data. Thus, the problem of concurrent modification of data errors is guaranteed. However, due to blocking, the throughput is not high.Pessimistic lock is more suitable for more writing and less reading.

Pessimistic lock and optimistic lock in database

Scene:Classmate a and classmate B should give you 500 yuan (happy bad, so you can get 1000 yuan in the end).

The process of using pessimistic lock:

  1. Student a gets your account balancebalance = 0And lock the record.
  2. Student B gets your account balance. Since classmate a has locked this record, classmate B needs to wait for classmate a to complete the transfer (release the lock) to get the balance.
  3. Student a completes the transfer and releases the lock. At this time, your account balancebalance=balance + 500 = 500
  4. Student B gets your account balancebalance = 500, and lock the record (if you are popular, you need to wait for classmate B to complete the transfer when classmate C transfers money to you)
  5. Classmate B completes the transfer and releases the lock (if classmate C wants to transfer money to you, then classmate C can get the lock and transfer money). Your account balance isbalance = balance + 500 = 1000
  6. At last you got 1000 yuan happily.

Assuming the transfer process is unlocked, let’s see what happens:

  1. Student a gets your account balancebalance_a = 0(without lock, student B can also get the account balance)
  2. Student B gets your account balancebalance_b = 0
  3. Student a’s transfer is completed, and your account balance isbalance = balance_a + 500 = 500
  4. Student B completes the transfer, and your account balance isbalance = balance_b + 500 = 500
  5. In the end, classmate a and classmate B turned 500, but you only got 500 in the end. It must be unacceptable.

Pessimistic lock and optimistic lock in database

Where’s the 500 yuan lost? From step 2, you can see that the account balance obtained by Student B is 0, not the balance 500 after transfer by student a. So the problem is here, which is a common problem in high concurrency scenarios. So it is very necessary to lock. But with the pessimistic lock, students have to queue up to transfer money to me. For those who are impatient, they will not transfer money directly. Am I not missing a good chance to get rich. What’s the best way? The answer isOptimistic lock

Optimistic lock

As the name suggests, optimistic lock is more optimistic. He onlyWhen updating the data, it will check whether the data has been updated by other threads(like pessimistic locks, pessimistic locks are locked when reading data.). If this data is found to be updated by other threads when updating the data, the update fails. If the data is not updated by another thread, the update succeeds. BecauseOptimistic lock has no lock waiting and improves throughput, so optimistic lock is suitable for more reading and less writing scenarios.

The common optimistic lock implementations are version and CAS (compare and swap). Only version number mode is described here.

To adopt the version number, you need to add a new field version in the database table to indicate the updated version of this record. For each change of the record, the version number is increased by 1. The above transfer example is still used:

  1. Student a gets your account balancebalance = 0Version numberversion_a = 0
  2. Student B gets your account balancebalance = 0Version numberversion_b = 0
  3. Student a transfer completedupdate table set balance = ${balance}, version = version + 1 and version = 0。 (at this time, the version number is 0, so the update is successful)
  4. Student B transfer completedupdate table set balance = ${balance}, version = version + 1 and version = 0。 (at this time, the version number is 1, so the update fails. After the update fails, student B can transfer again.)
  5. After student B transfers money again, you still get 1000.

summary

Pessimistic lock: add lock when reading, release lock after updating, and other threads will block during this process, resulting in low throughput, which is suitable for multi write scenarios.

Optimistic lock: do not lock, only verify whether the data is updated by other threads when updating, with high throughput, applicable to multi read scenarios.