When does the database use the exclusive lock? Is the exclusive lock really inefficient and inefficient?

RT: When does the database use the exclusive lock? Is the exclusive lock really inefficient and inefficient? What should I do? RT: When does the database use the exclusive lock? Is the exclusive lock really inefficient and inefficient? What should I do?

Reply content:

RT: When does the database use the exclusive lock? Is the exclusive lock really inefficient and inefficient? What should I do?

We recommend that you apply the X lock to data modification operations, such as INSERT, UPDATE, or DELETE. Make sure that multiple updates are not performed for the same resource at the same time.
Efficiency and Security cannot both be used.

You can use a Memcached-like CAS (Check And Set) conflict detection mechanism.
Http://php.net/manual/zh/memcached.cas.php

When obtaining data, obtain the user's balance and version number (The table contains the version field ):

SELECT balance, version FROM user WHERE id = 1 AND balance> 10;

When updating data, update the corresponding user ID and the version number when obtaining data.
If the condition is met, the balance is updated and the version number is + 1.
If the condition is not met, the data is updated. Therefore, this operation is invalid.

UPDATE user SET balance = balance-10, version = last_version + 1 WHERE id = 1 AND version = last_version;

Note that last_version in UPDATE is the version number of this operation obtained by SELECT.