MySQL lock learning notes!

Time:2022-5-15

According to lock granularity:Row lock, page lock and table lock
The number of locks at each level is limited, because locks will occupy memory space, and the size of lock space is limited. When the number of locks at a certain level exceeds the threshold of this level, lock upgrading will be carried out. Lock upgrade is to replace multiple smaller locks with larger locks

Row locking is to lock data according to the granularity of rows.
The locking force is small, the probability of lock conflict is low, and the degree of concurrency can be achieved is high. However, the cost of locking is relatively large, locking will be relatively slow, and deadlock is easy to occur.

Page locking is to lock on the granularity of the page. The locked data resources are more than row locks, because there can be multiple row records in a page.
The cost of page lock is between table lock and row lock, and deadlock will occur. The locking granularity is between table lock and row lock, and the concurrency is general.


Table locking is to lock the data table. The locking granularity is large, and the probability of lock conflict is also high,
The concurrency of data access is low. However, the advantage is that the cost of using locks is small, and locking will be fast.
MySQL lock learning notes!

image.png

Locks are divided from the perspective of database management:Shared lock (read lock)Exclusive lock (write lock)Intent lock
Shared lock is also called read lock or s lock. The resources locked by shared lock can be read by other users, but cannot be modified. If it is not released, you can only select, not insert, update or delete. An error will be reported. Other users will wait until the user is unlocked. This ensures that the data is not modified during reading.
Exclusive lock is also called exclusive lock, write lock or X lock. The data locked by the exclusive lock is only allowed to be used by the transaction performing the locking operation. Other transactions cannot query or modify the locked data. The exclusive lock will also be automatically used when adding, deleting or modifying the database to prevent other transactions from operating on the data row.
Intention lock is simply to give a higher level of space to indicate whether it has been locked.

show status like 'innodb_ row_ lock%'; # View row lock
 Unlock table # unlock the table

Meter reading lockDifferent transactions are allowed to share lock reading before, but other transactions are not allowed to modify or add exclusive locks. If there is any modification, it can be executed only after a transaction is committed, which is prone to deadlock

#Client 1
lock table test read;  # Read lock
 select * from test; # Query √
Errors will be reported for insert, update and delete: 
>1099 - table 'test' was locked with a read lock and can't be updated # error content

Select, insert, update, delete and other tables will report errors:
>1100 - table 'test2' was not locked with lock tables # error content


 
#Client 2 can only query 
select * from test; Query √  
Insert, update and delete the table# The addition, deletion and modification will wait until client 1 is unlocked

 Select, insert, update, delete and other tables are OK


#Row read lock
#Client 1:
 select * from test where id=1 lock in share mode  
Other transactions can add a read lock to the table, but cannot add a write lock. If a read lock is added to update the data, an error is reported or wait.

Write lockOther transactions are not allowed to add shared or exclusive lock reads. Modifications are unique and must wait for the previous transaction to commit

#Client 1
lock table write; Write lock
Select, insert, update and delete this table are OK, but adding, deleting, modifying and querying other tables will report an error:
> 1100 - Table 'user' was not locked with LOCK TABLES


#Client 2
Select, insert, update and delete the table is not released and cannot be read or written. It will wait all the time:
Select, insert, update, delete and other tables are OK

Divide from the programmer’s point of view:Pessimistic lockOptimistic lock
Optimistic lock and pessimistic lock are not locks, but the design idea of locks.
Optimistic locking believes that concurrent operations on the same data will not always occur, which belongs to a small probability event. It does not need to lock the data every time, that is, it is realized by program instead of using the locking mechanism of the database itself. In terms of program, we can adopt version number mechanism or timestamp mechanism.
Version number mechanism of optimistic lockTimestamp mechanism of optimistic lock
Optimistic lock is the permission of the programmer to control the concurrent operation of data. It basically adds a stamp (version number or time stamp) to the data line to prove whether the currently obtained data is up-to-date.
Pessimistic locking is also an idea. It takes a conservative attitude towards the modification of data by other transactions and will be realized through the locking mechanism of the database itself, so as to ensure the exclusivity of data operation.

MySQL lock learning notes!

image.png
Optimistic lock is suitable for scenarios with many read operations and relatively few write operations.
Its advantage lies in the program implementation, and there is no deadlock problem, but the applicable scenario will be relatively optimistic, because it can not prevent database operations other than the program.
Pessimistic locks are suitable for scenarios with many write operations because write operations are exclusive.
Pessimistic locking can prevent other transactions from operating permissions on the data at the database level and prevent read-write and write write write conflicts.

deadlock
Deadlock refers to the execution of multiple transactions (or multiple processes at the program level),
Congestion caused by competition for the same resource. Deadlocks often occur because locks are acquired step by step in transactions.

If the transaction involves multiple tables and the operation is complex, you can try to lock all resources at once instead of obtaining them step by step, which can reduce the probability of deadlock;
If the transaction needs to update most of the data in the data table, and the data table is relatively large, lock upgrading can be adopted, such as upgrading row level locks to table level locks, so as to reduce the probability of deadlock;
When different transactions read and write multiple data tables concurrently, the order of accessing the tables can be agreed, and the same order can be adopted to reduce the probability of deadlock.
The optimistic locking method will not cause deadlock;
There will be no deadlock in MySQL MyISAM storage engine. This is because MyISAM always obtains all locks at one time. In this way, it can be executed when all the locks are met, or it needs to wait for all the locks.
Before executing the query statement (select), MyISAM will automatically add read locks to all tables involved,
Before performing update operations (update, delete, insert, etc.), write locks will be automatically added to the involved tables
There are three ways to lock rows in InnoDB:
1) Record lock: add a lock for a single row record.
2) Gap locking: it can help us lock a range (the gap between indexes), but it does not include the record itself. The use of gap lock can prevent the occurrence of unreal reading.
3) Next key lock: help us lock a range and lock the record itself at the same time, which is equivalent to gap lock + record lock, which can solve the problem of unreal reading
MySQL lock learning notes!

image.png