PHP - The choice between pessimistic locking and optimistic locking under high concurrency

Question

Tell me what you think and please correct me if I am wrong. The implementation principle of optimistic locking is cas operation, and lightweight locks in Java are also implemented based on cas. The biggest problem with pessimistic locking is blocking. In an in-depth understanding of the Java virtual machine, it is mentioned that lightweight locks are generally better than heavyweight locks (mutex locks)...

怪我咯 · Answer

Simply speaking, under normal circumstances, optimistic locking is suitable for operations that only read and does not write, and pessimistic locking is suitable for mixed reading and writing operations. If the write operation is very simple and short, such as increasing the number of visitors, you can also use optimistic locking, or when there is no need to ensure that the data read is the latest. Using optimistic locking is indeed a better choice in 80% of cases.

CAS relies on hardware CPU instruction support to implement atomic-level operations, so it is generally faster under high concurrency conditions. However, this speed is not without its shortcomings. The disadvantage is that when there is reading and writing, the CPU cache failure rate may increase, unless Your CPU is single-core (non-Intel platform embedded).

In short, to improve high concurrency performance, we still need actual measured data to explain the problem. What I mentioned above are all theories. Hope it helps you.

阿神 · Answer

Whether it is pessimistic lock or optimistic lock, it is actually an idea of concurrency control, and it is not limited to the database. The specific choice of optimistic lock and pessimistic lock is based on the business scenario.
Pessimistic lock:
Generally, the pessimistic lock we use is to add an exclusive lock at the database level. If the lock is successful, the data can be modified and the transaction can be submitted. If the transaction is submitted successfully and unlocked, failure means that the data is being modified. If you use mysql's innodb, be careful to turn off the mysql auto-commit attribute, because mysql uses the autocommit mode by default. That is to say, when you perform an update operation, MySQL will immediately submit the result, and also pay attention to the lock level. , by default innodb uses row-level locks, but row-level locks are based on indexes. If your sql does not use indexes, then mysql will use table-level locks to lock the table. set autocommit=0Advantages and Disadvantages:
Pessimistic lock is a conservative strategy of "get the lock first and then access", which provides a guarantee for the security of data processing. However, in terms of efficiency, the locking mechanism will cause additional overhead to the database and increase the chance of deadlock. In addition, since there will be no conflicts in read-only transaction processing, there is no need to use locks. Doing so will only increase the system load. It also reduces parallelism. If a transaction locks a certain row of data, other transactions must wait for the transaction to be processed before processing that row of data.

Optimistic lock:

Optimistic lock actually assumes that the data will not cause conflicts under normal circumstances, so when the data is submitted for update, the conflict of the data will be officially detected. If a conflict is found, the user will be returned Error information, letting the user decide what to do.
Generally, it can be written directly in the code logic layer. Compared with pessimistic lock, when processing the database, optimistic lock does not use the lock mechanism provided by the database. It is usually implemented by using a version number or timestamp. When using a version number, you can specify a version number when initializing the data, and each update operation on the data will perform a +1 operation on the version number. And determine whether the current version number is the latest version number of the data. Such as:

1.查询出商品信息
select (status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

Advantages and Disadvantages:

Optimistic locking believes that the probability of data competition is very small. Therefore, proceed as directly as possible and do not lock until submission, so no locks and deadlocks will occur. But if you do this simply, you may still encounter unexpected results. For example, if two transactions read a certain row of the database and then write it back to the database after modification, you will encounter a problem.
The failure of optimistic locking is a small probability event and requires the cooperation of multiple conditions to occur. Such as:

The application adopts its own strategy to manage the primary key ID. For example, it is common to take the maximum value of the current ID field + 1 as the new ID.
The default value of version number field version is 0.
User A reads a certain record and prepares to modify it. This record happens to be the record with the largest ID and has not been modified before. The version is the default value 0.
After user A completed reading, user B happened to delete the record. After that, user C inserted a new record.
At this time, by mistake, the ID of the newly inserted record is consistent with the ID of the record read by user A, and both version numbers are the default value 0.
After user C completes the operation, user A modifies the completed record and saves it. Since both ID and version can be matched, user A saves successfully. However, the record inserted by user C was overwritten.
The fundamental reason for the failure of optimistic locking at this time is that the primary key ID management strategy used by the application is incompatible with optimistic locking to a very small extent.

PHPz · Answer

Before I started working, I had the same idea as the questioner. In actual work, there is actually not much difference between the two. The most time-consuming part of the system under real high concurrency is always network connection, database query and active sleep of threads. Locks bring overhead. Basically can be ignored

我想大声告诉你 · Answer

The key question is, are the facts pessimistic or optimistic?

If your resource competition is fierce and cannot be shared, optimistic locking will just defeat the hope of a large number of requests.

If there is no competition for your resources (this is not necessarily related to the level of concurrency, but has a greater impact on the business), then pessimistic locking means unnecessary locking. If the resource was originally shareable (for example, the resource supports multiple read-only parties), then pessimistic locking means losing the original usable time.

In the in-depth understanding of the Java virtual machine, it is mentioned that lightweight locks are generally better than heavyweight locks (mutex locks); if the competition for high-concurrency locks is fierce, lightweight locks will be locked for a long time. Time spin consumes CPU, making the performance of lightweight locks slower than traditional heavyweight locks. Then optimistic locking also includes spin and cas, so pessimistic locking under high concurrency does not seem to be a good solution.

I don’t know much about JVM. But what does "Spin and cas also exist in optimistic locking" mean? cas is an implementation method of spin lock. Why should it be parallelized? What does "also" mean? Pessimistic locking is a blocking operation, there is no spin, and it does not continuously consume the CPU.