编程知识 cdmana.com

What are pessimistic lock and optimistic lock in MySQL?

Index and lock are two core knowledge points in database , The implementation of isolation level is accomplished by locks, and the locks are divided according to the lock particles  ?

Locks are used to lock data , We can partition the lock from the granularity of the lock object , They are row locks 、 Page lock and table lock .

  • Row locking is to lock data according to the granularity of rows . The locking force is small , The probability of lock conflict is low , High concurrency can be achieved , But it's expensive to lock , Locking is slower , Prone to deadlock .

  • Page locking is locking on the granularity of the page , More data resources are locked than row locks , Because a page can have multiple row records . When we use page locks , There will be a waste of data , But most of this waste is data rows on a page . The overhead of page lock is between table lock and row lock , A deadlock occurs . Lock granularity is between table lock and row lock , The concurrency is average .

  • Table lock is to lock the data table , The locking granularity is very large , At the same time, the probability of lock conflict will be higher , The concurrency of data access is low . But the advantage is that the use of locks costs less , Locking will be quick .

There are also zone locks and database locks .

 

 

There is a limit to the number of locks per level , Because locks take up memory space , The size of the lock space is limited . When the number of locks in a level exceeds the threshold of that level , Lock escalation will occur . Lock upgrade is to replace multiple smaller granularity locks with larger granularity locks , such as InnoDB Upgrade middle row lock to table lock , The advantage of this is that it takes less lock space , But at the same time, the concurrency of data has also decreased .

 

From the perspective of database management, the lock is divided

Shared lock and exclusive lock

  • Shared locks are also called read locks or S lock , Shared lock locked resources can be read by other users , But it can't be modified . It's going on SELECT When , The object will be locked with a shared lock , When the data is read , Will release the shared lock , This ensures that the data will not be modified when it is read .

  • Exclusive lock is also called exclusive lock 、 Write lock or X lock . It is only allowed to lock transactions using lock operations , Other transactions cannot query or modify locked data .

When we update the data , That is to say INSERT、DELETE perhaps UPDATE When , The database will also automatically use exclusive locks , Prevent other transactions from operating on the data row .

 

Intent locks (Intent Lock), In short, it is to give a larger level of space to indicate whether the lock has been applied .

 

Divide locks from the programmer's point of view

Optimism lock

Optimism lock (Optimistic Locking) It is believed that concurrent operations on the same data will not always occur , It's a small probability event , You don't have to lock the data every time , That is to say, it does not use the lock mechanism of the database itself , It's a program . On procedure , We can use version number mechanism or time stamp mechanism to implement .

  • Optimistic lock version number mechanism

Design a version field in the table version, The first time I read it , Will get version Value of field . Then update or delete the data , Will execute UPDATE ... SET version=version+1 WHERE version=version. At this time, if a transaction has changed this data , The modification will not succeed .

  • The timestamp mechanism of optimistic lock

Timestamp is the same as the version number mechanism , It's also when the update is submitted , Compare the timestamp of the current data with that obtained before the update , If both are consistent, the update is successful , Otherwise it's a version conflict .

 

Pessimistic locking

Pessimistic locking (Pessimistic Locking) It's also an idea , Be conservative about data being modified by other transactions , It will be realized through the lock mechanism of the database itself , So as to ensure the exclusivity of data operation .

Applicable scenario

  • Optimistic lock is suitable for scenarios with more reading operations , Relatively speaking, there are fewer operations to write . Its advantage lies in program implementation , There is no deadlock problem , But the applicable scenario will also be relatively optimistic , Because it can't stop database operations other than programs .

  • Pessimistic lock is suitable for scenarios with many write operations , Because write operations are exclusive . Adopt pessimistic lock mode , You can prevent other transactions from operating on the data at the database level , Prevent reading - Write and write - The conflict of writing .

summary

Optimism and pessimism are not locks , But the design idea of lock .

Avoid deadlock :

    • If the transaction involves multiple tables , Complicated operation , Then try to lock all the resources at once , Instead of getting , This can reduce the probability of deadlock ;

    • If a transaction needs to update most of the data in the data table , The data table is bigger , In this case, lock upgrade can be used , For example, upgrade row level lock to table level lock , So as to reduce the probability of deadlock ;

    • Different transactions read and write multiple data tables simultaneously , You can agree on the order in which the tables are accessed , Use the same order to reduce the probability of deadlock .

版权声明
本文为[Code farmer architecture]所创,转载请带上原文链接,感谢

Scroll to Top