Mysql乐观锁悲观锁行锁表锁是从哪几个方面来分类的？比如说表锁也可是乐观锁，也可以是悲观锁吗？

Question

如题。Mysql乐观锁悲观锁行锁表锁是从哪几个方面来分类的？比如说表锁也可是乐观锁，也可以是悲观锁吗？

Answer 1

The impact of locking on concurrent access is reflected in the granularity of the lock. It can be seen that the row lock granularity is the smallest, the concurrent access is the best, the page lock granularity is the largest, and the table lock is between the two.
There are two types of locks: pessimistic locking and optimistic locking. Pessimistic locking assumes that the probability that other users attempt to access or change the object you are accessing or changing is very high. Therefore, in a pessimistic locking environment, the object is locked before you start changing it, and until you commit The lock is released only after the changes have been made. 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, which means that the concurrent access of pessimistic locks is not good.
Contrary to pessimistic locking, optimistic locking believes that the probability of other users trying to change the object you are changing is very small, so optimistic locking does not lock the object until you are ready to commit the changes. When you read and The object is not locked when changing it. It can be seen that the locking time of optimistic locking is shorter than that of pessimistic locking, and optimistic locking can obtain better concurrent access performance with larger lock granularity. But if the second user happens to read the object before the first user commits his changes, then when he completes his changes and commits, the database will find that the object has changed, so the second user must not The object is not reread and changes are made. This shows that in an optimistic locking environment, the number of times concurrent users read objects will increase.