One, foreword
Lock is a synchronization mechanism used by computers to concurrently access the same shared resource when multithreading or thread is executed. The lock in MySQL is implemented at the server layer or storage engine layer to ensure the consistency and effectiveness of data access.
MySQL locks can be classified by mode: optimistic locks and pessimistic locks. According to granularity, it can be divided into global lock, table lock, page lock and row lock. According to the attributes can be divided into: shared lock, exclusive lock. According to the state: intention to share lock, intention to exclude lock. According to the algorithm: clearance lock, key lock, record lock.
The following will be explained in accordance with the above figure.
Global lock, table lock, page lock, row lock
1. Global lock
(1) concept
A global lock is a lock on the entire database instance.
(2) Application scenarios
Full logical backup (mysqldump)
(3) Implementation method
MySQL provides a method for adding a global read lock by using Flush tables with read lock (FTWRL).
You can use this command when you need to make the entire library read-only, after which the following statements from other threads will be blocked: data update statements (adding, deleting, or modifying data), data definition statements (including table creation, table structure modification, etc.), and update class transaction commit statements.
Risk:
If the backup is on the master repository, updates cannot be performed during the backup and business can essentially stop.
If the backup is performed on a secondary database, the secondary database cannot execute the binlog synchronized from the primary database during the backup, resulting in a master-slave delay.
Solutions:
Mysqldump starts a transaction with a single transaction argument to ensure that the consistency view is retrieved. Due to the support of MVCC, data can be updated normally during this process.
2. The table level lock
(1) concept
The current operation of the entire table lock, the most commonly used MyISAM and InnoDB support table level locking.
Meta Data lock (MDL); meta data lock (MDL);
(2) Implementation method
Lock tables… The read/write;
For example lock tables T1 read, T2 write; Command, and statements that write t1 or write T2 from other threads will be blocked. In addition, thread A can only read T1 and read T2 before executing unlock tables. Write t1 is not allowed, and you cannot access other tables before unlock tables.
Metadata lock: MDL does not need to be used explicitly, but is automatically added when accessing a table. In MySQL 5.5, MDL is introduced to add, delete, change, or query data to a table. Add an MDL write lock to a table when making structural changes to the table.
(3) the risk point
Reference: www.cnblogs.com/keme/p/1106…
To add a field to a table, or to modify a field, or to add an index, you need to scan the entire table. When operating on large tables, you must be careful not to affect online services. In fact, even small table, careless operation will be a problem.
1. sessionA:
begin;
select* from t limit 1;
2. sessionB:
select* from t limit 1;
3. sessionC:
altertable t add f int;
# will MDL lock
4. sessionD:
select* from t limit 1;
We can see that session A starts first and an MDL read lock is placed on table T. Since session B also requires an MDL read lock, it works fine.
Session C is then blocked because session A’s MDL read lock has not been released, whereas sessionC requires an MDL write lock and can only be blocked.
It doesn’t matter if only Session C itself is blocked, but all subsequent requests for MDL read locks on table T will also be blocked by Session C. Alter table t alter table T alter table T alter table T alter table T alter table T alter table T
If the queries on a table are frequent and the client has a retry mechanism, meaning that a new session will be requested after a timeout, the library threads will quickly fill up.
The MDL lock in a transaction is claimed at the beginning of a statement execution, but is not released immediately after the statement completes, but after the entire transaction commits. ** Note: Generally, row locks have lock timeout. However, MDL locks have no timeout limit and will remain locked as long as the transaction is not committed. 支那
(4) Solutions
First we need to resolve long transactions, which will hold the MDL lock until they commit. In the Innodb_TRx table of MySQL’s Information_SCHEMA library, you can look up the transaction that is currently executing. If the table to which you want to make DDL changes happens to have a long transaction in progress, consider suspending the DDL first or killing the long transaction. This is why DDL changes need to be made during peak periods.
3. Page-level locks
(1) concept
Page-level locking is a kind of lock whose granularity is between row-level locking and table-level locking in MySQL. Table level locking is fast but has many conflicts, while row level locking is slow but has few conflicts. Therefore, a compromise page-level locking is adopted, locking adjacent sets of records at a time. The BDB engine supports page-level locking.
4. Row-level locks
(1) concept
Row-level lock is the lock with the lowest granularity, the lowest probability of lock conflict and the highest concurrency. However, locking is slow, expensive and prone to deadlock.
Only InnoDB in MySQL supports row-level locking, which is divided into shared locking and exclusive locking.
(2) Implementation method
In MySQL, row-level locking does not directly lock records, but locks indexes. There are two types of indexes: primary key index and non-primary key index. If a SQL statement operates on a primary key index, MySQL locks the primary key index. If a statement operates on a non-primary key index, MySQL locks the non-primary key index and then the related primary key index. During UPDATE and DELETE operations, MySQL not only locks all index records scanned by the WHERE condition, but also locks adjacent keys, which is called next-key locking.
(3) the actual combat
Update session1 and session2 to see if the lock is locked.
Session1 is waiting for session1 to release the lock timeout warning.
Session2 (id=2);
The operation with id=2 can succeed.
Optimistic and pessimistic locks
1. Optimistic locking
(1) concept
Optimistic locks are compared to pessimistic locks. Optimistic locks assume that data will not cause conflicts in general. Therefore, when data is submitted for update, data conflicts will be formally detected.
(2) Application scenarios
This method is recommended because a large number of write operations increase the possibility of write conflicts, and the service layer needs to retry constantly, which greatly reduces system performance.
(3) Implementation method
Generally, the Version recording mechanism is used to implement, adding a numeric type “Version” field in the database table to implement. When the data is read, the value of the Version field is read together, incrementing the version value with each update of the data. When we submit the update, compare the current version information of the database table with the version value extracted for the first time. If the current version number of the database table is equal to the version value extracted for the first time, it will be updated; otherwise, it is considered as expired data.
(4)
In the order table, ID,status,version represent the order ID, order status, and version number respectively.
1. Query the order information
select id,status,versionfrom order where id=#{id};
2. The payment is successful
3. Modify the order status
update set status=Pay for success,version=version+1 where id=#{id} and version=#{ version};
2. The pessimistic locking
(1) concept
Pessimistic locks, as its name suggests, has strong characteristics of monopoly and exclusive, every time to fetch the data that people will change, the data by outsiders (including the system current other affairs, and transaction processing) from an external system changes, and conservative attitude, therefore, in the process of the whole data processing, the data is locked.
(2) Application scenarios
This method is applicable to scenarios with small concurrency, frequent write operations, and high data consistency.
(3) Implementation method
MySQL > set autocommit=0; Shared lock and exclusive lock are different implementations of pessimistic lock, both of which belong to the category of pessimistic lock.
(4)
In the goods table, ID,name, and number represent product ID, product name, and product inventory respectively.
1. Enable the transaction and disable automatic commit
setautocommit=0;
2. Query product information
selectid,name,number from goods where id=1 for update;
3. Users place orders and generate orders
4. Modify the inventory
updateset number= number-1 where id=1;
5. Commit the transaction
commit;
Instructions: select… MySQL > update (goods) lock (id 1); update (goods) lock (id 1) Select id,name,number from goods where id=1for update; The transaction must wait until the transaction commits. This ensures that the current data will not be modified by other transactions.
Note: MySQL InnoDB defaults to row-level locking. Row-level locks are index-based. If an SQL statement does not use an index, row-level locks are used to lock the entire table.
Shared locks and exclusive locks
1. A Shared lock
(1) concept
Shared lock is also called read lock, or S lock for short. After transaction A adds read lock to the data, other transactions can only add read lock to the data and cannot perform any modification operations, that is, cannot add write lock. Only after the read lock is released on transaction A can other transactions add A write lock to it.
(2) Application scenarios
Shared locks are used to support concurrent data reads. When reading data, other transactions are not allowed to modify the current data, thus avoiding the “unrepeatable” problem.
Mysql > alter table child_id = ‘child_id’; mysql > alter table child_id = ‘c_child_id’; mysql > alter table child_id = ‘child_id’; Insert c_child_id=100 into the parent table. Insert child_id=100 into the parent table. Insert child_id=100 into the parent table. Select * from parent where c_child_id=100 lock in share mode select * from parent where c_child_id=100 lock in share mode Insert into Child (child_id)values (100) will not cause this problem.
(3) Implementation method
The select… lock in share mode
(4)
Session1 holds the shared lock and is not committed. Session2 queries are unaffected, but update operations are blocked until timeout.
2. The exclusive lock
(1) concept
Exclusive lock, also known as write lock, X lock for short. After a transaction adds a write lock to the data, other transactions can neither add read/write nor write lock to the data. Write lock and other locks are mutually exclusive. Only after the current data write lock is released, other transactions can add write lock or read lock to it.
The MySQL InnoDB engine automatically assigns exclusive locks to all data related to update, DELETE, and INSERT. The select statement does not add any lock type by default.
(2) Application scenarios
The write lock prevents other transactions from modifying or reading the current data, effectively avoiding the dirty read problem.
(3) Implementation method
The select… for update
(4)
Session1 exclusive lock query. Session2 also blocks exclusive lock queries.
5. Intentional shared lock and intentional exclusive lock
1. The concept
Intentional locks are table locks. In order to coordinate row locks and table locks, multi-granularity (table locks and row locks) locks are supported.
2. The role
If transaction A has A row lock, MySQL will automatically add an intent lock to the table. If transaction B wants to apply for A write lock for the entire table, it does not need to go through every row to determine whether there is A row lock, but directly determines whether there is an intent lock, which improves performance.
3. Compatibility and mutual exclusion of intent lock
Note: exclusive/shared locks refer to table locks. Intent locks are not mutually exclusive with row-level shared/exclusive locks.
Session1 acquired an exclusive lock for a row and did not commit:
select*from goods where id=1 for update;
There are two locks in the GOODS table: the intentional exclusive lock on the GOODS table and the exclusive lock on the data row with id 1.
Session2 wants to acquire the goods table’s shared lock:
LOCK TABLES goods READ;
Session1 must hold exclusive locks on some rows in the goods table. Then session2’s goods table lock request will be rejected (blocked). There is no need to detect whether an exclusive lock exists in each row of the table.
Six, clearance lock, key lock, record lock
-
concept
Record lock, gap lock and key lock are all exclusive locks, and the use of record lock is consistent with the exclusive lock introduction.
-
Record locks
A record lock is a blocking record. A record lock is also called a row lock, for example:
select *from goods where **`id`=**1 for update;
It places a record lock on the row where id=1 to prevent other transactions from inserting, updating, or deleting the row.
-
Clearance lock
Gap locking is based on non-unique indexes and locks index records within a range. Using gap locks locks an interval, not just each piece of data in that interval.
select* from goods where id between 1 and 10 for update;
That is, all rows within (1, 10) will be locked, and all rows with ids 2, 3, 4, 5, 6, 7, 8, and 9 will be blocked, but rows 1 and 10 will not be locked.
-
Key in the lock
Key lock is a combination of record lock and gap lock. Its blocking range includes both index record and index interval, which is a left open and right closed interval. The main purpose of keylocking is also to avoid Phantom Read. If the isolation level of the transaction is downgraded to RC, the temporary key lock will also fail.
A key lock exists on a non-unique index column of each data row. When a transaction holds a key lock on that data row, it will lock the data in a range of open and closed data. It is important to note that InnoDB row-level locking is index-based. Temporary locks are only associated with non-unique index columns. There are no temporary locks on unique index columns (including primary key columns).
Hidden key locks in the goods table are :(-∞, 96],(96, 99],(99, +∞]
Session1 update column (number 96); session1 update column (number 96);
InnoDB acquires a key LOCK FOR a non-unique index when performing an UPDATE FOR UPDATE LOCK IN SHARE MODE: Left gap Lock + Record Lock + right gap Lock.
Session1 is locked at (-∞, 99) after executing the above SQL.
Welcome to follow our wechat official account: CodingTao
