Update loss refers to the situation that two updates are performed simultaneously, and the latest update overwrites the previous update. Update loss is caused by data inconsistency. Here’s an example:

  • User A has 100 yuan in his bank card. At one moment, user B transfers 50 yuan to A (called operation B), while user C transfers 50 yuan to A (called operation C).
  • B operations from the databasereadHis current balance is 100, so calculate the new balance as 100+50=150
  • C operations are also taken from the databasereadHis current balance is 100, so calculate the new balance as 100+50=150
  • Operation B sets balance=150writeDatabase, and then C operation will also balance=150writeThe database
  • So the balance of A eventually becomes 150

The example above, A receive two 50 yuan transfer at the same time, the final balance should be 200 yuan, but because the problem of concurrency into 150 yuan, the reason is that B and C to initiate A transfer request, opened the two database session at the same time, the two transactions, A transaction after got the intermediate state before A transaction data, leading to lost updates. There are two common solutions:

  • Lock synchronization
  • Check data consistency before update

Pessimistic locking

As the name implies, pessimistic locks assume that someone else will modify the data when reading data, so when fetching data, a lock will be added to the current data, and other operations will not be allowed to change before the end of the operation. Note that both pessimistic and optimistic locks are defined at a level of business logic, and different designs may have different implementations. A common pessimistic lock implementation in mysql layer is to add an exclusive lock. An exclusive lock is implemented by using a select XX for UPDATE statement in a transaction. An exclusive lock adds a row-level lock to the current row and prevents other transactions from updating until the current transaction commits.

However, this is not the case; instead, it is data that is locked exclusively and cannot be locked by other transactions until the lock is released (end of transaction)


The exclusive lock prevents update and delete operations because update and delete operations automatically add the exclusive lock

This means that exclusive locks cannot prevent select operations. The select XX for UPDATE syntax allows exclusive locking of select operations. Select for update(s) from transaction (s);

begin;
select * from account where id = 1 for update;
update account set balance=150 where id =1;
commit;Copy the code

In this way, operation C cannot obtain an exclusive lock before operation B commits, preventing repeated updates to the Account from losing updates.

Optimistic locking

Optimistic locking means that data is not locked when it is acquired. Optimistic locking means that the operation will not conflict, and the conflict will be checked during update. Example:

begin; select balance from account where id=1; - to get the balance = 100; Update account set balance= 150 where id=1 and balance=100; commit;Copy the code

If the value of affected (affected) is 0, the balance value is not 100. In this case, services can return a failure or re-select the data