Index and lock are two core knowledge points in database , The implementation of isolation level is accomplished by lock
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 （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） 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 .
- 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 .
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