A brief description of pessimistic locking and optimistic locking

Pessimistic Lock, as the name suggests, it is very pessimistic. Every time you go to get the data, you think that others will modify it, so you will lock it every time you get the data, so that others will Taking this data will block until it gets the lock. Many such lock mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks, etc., which are all locked before operations.

The most commonly used one is select... for update, which is a row lock that locks the result rows of the select before the transaction is committed or rolled back. Other transactions are not allowed to perform update, delete, or for update operations on these rows.

Optimistic Lock, As the name suggests, it is very optimistic. Every time you go to get the data, you think that others will not modify it, so it will not be locked. During this period, the data can be used as you wish. It is read by others, but when updating, it will be judged whether others have updated the data during this period. You can use mechanisms such as version numbers.

The version number mechanism is the most commonly used method of optimistic locking. It is to add a version number field to the table. Before updating, check it to get the version number, and then update it as the where condition of the update statement. If the data is in After obtaining the version number, if it has been changed before the update, the update will fail because 0 pieces of data were updated in the end. If the update number obtained by the Java background is 0, it means that the update failed and a concurrency problem occurred. Then do the specific deal with.

For example, two people modify a certain piece of data at the same time. The process is as follows:

Operator A operates as follows:

select id, balance, version from table where id=“1”;

Query results: id=1, balance=1000 , version=1

update table set balance=balance+100, version=version+1 where id=“1” and version=1;

After execution, the returned update result is 1, indicating that one item has been updated. The result in the database is: id=1, balance=1100, version=2

Operation Operator B operates as follows:

select id, balance, version from table where id=“1”;

Query results: id=1, balance=1000, version=1, indicating that operator A has not modified it yet.

update table set balance=balance-50, version=version+1 where id=“1” and version=1 ;

When checking, operator A has not modified it yet. When it is time to update, operator A has modified it successfully first, so the actual values ​​in the database are id=1, balance=1100, version=2,

Operator B also increments the version number by one (version=2) and attempts to submit data to the database (balance=950), but at this time the data where id="1" and version=1 cannot be found,

So the update failed, and the execution result was 0, indicating that no data was updated successfully.

Now check again, the result is still the result after operator A completes the operation

select id, balance, version from table where id="1";

Query Result: id=1, balance=1100, version=2

The above is the principle of implementing the version number mechanism by yourself. The version number mechanism actually used is the mechanism provided by the database itself. Once it is found that the updated version number is not The latest ones will be rejected.

The above is the detailed content of A brief description of pessimistic locking and optimistic locking. For more information, please follow other related articles on the PHP Chinese website!