Mysql Optimistic locking Pessimistic Lock Row Lock Table Lock Is Classified from Which Aspects? For example, the watch lock is also a Optimistic locking. Can it also be a pessimistic lock?

  mysql, question

As the topic is. Mysql Optimistic locking Pessimistic Lock Row Lock Table Lock Is Classified from Which Aspects? For example, the watch lock is also a Optimistic locking. Can it also be a pessimistic lock?

The impact of locking on concurrent access is reflected in the granularity of locks. It can be seen that row locks have the smallest granularity, concurrent access is the best, page locks have the largest granularity, and table locks are between 2.
There are two kinds of locks: pessimistic locks and Optimistic locking. Pessimistic lock assumes that the probability of other users attempting to access or change the object you are accessing or changing is very high, so in a pessimistic lock environment, the object is locked before you start changing the object, and the lock is not released until you submit the changes. The disadvantage of pessimism is that whether it is a page lock or a row lock, the locking time may be very long, which may restrict the access of other users for a long time, that is to say, the concurrent access of pessimistic locks is not good.
Contrary to pessimistic locking, Optimistic locking thinks that the probability of other users attempting to change the object you are changing is very small, so Optimistic locking will not lock the object until you are ready to submit the changes, and will not lock the object when you read and change the object. It can be seen that the locking time of Optimistic locking is shorter than pessimistic locking, and Optimistic locking can obtain better concurrent access performance with larger lock granularity. However, if the second user reads the object just before the first user commits the changes, then when he completes his own changes and commits, the database will find that the object has changed, so the second user has to re-read the object and make changes. This shows that in the Optimistic locking environment, the number of times concurrent users read objects will increase.