The lock
Mysql provides row, table, and page locks to resolve data contention situations. On the basis of these three locks, MySQL provides different implementations. The specific locks used are determined by the storage engine.
Lock application rules:
- A read lock can be applied for if there is no lock, a read lock can be applied for if there is only a read lock, but a write lock cannot be applied for
- When you apply for a write lock, you can apply for a write lock if there is no lock, but you cannot apply for a read lock if there is a write lock, and you cannot apply for a write lock
Row locks
Row locking: As the name implies, a row is locked. That is, when MySQL locks data, the granularity of the lock is specific to one record, and the access to other records is not affected.
In InnoDB, row locks are implemented in three main categories: read locks (shared locks), intentional read locks, write locks (exclusive locks), intentional write locks and gap locks.
When applying for a lock, the system determines whether the record has an associated lock structure (whether other transactions have applied for locks). If no other transaction holds the lock (which is determined by the lock type), a lock record is created, the transaction ID is recorded, the lock acquisition status is marked as successful and the lock type, and the lock was successfully acquired. If another transaction has requested a lock, a lock record is created, recording the transaction ID, marking the failed lock acquisition status, and the lock type. When another transaction releases the lock, it looks for the lock record of the corresponding record, changes the status to acquire success, and wakes up the corresponding thread.
Read lock
SQL > select * from test for update; SQL > select * from test for update; Select * from test lock in share mode select * from test lock in share mode
When a read lock is attached to a record, the entire ideographic read lock is acquired first. When you need to add an exclusive lock to a MySQL table, you can check whether there is an intentional read lock to determine whether there is a read lock. (Write locks are covered later)
Write lock
Write lock is a write lock (exclusive lock) applied for by MysQL when updating data. When applying for a write lock, it will also be the same as the read lock, first to apply for an intentional write lock.
From the granularity of the above two locks, intentional read lock and intentional write lock do not conflict, because they are just a sign, representing whether there is a read lock or write lock in the table. Whether the lock is successfully added depends on the specific record of the lock information, and whether there is lock competition.
Clearance lock
At the isolation level, unreal reads occur both uncommitted and committed reads. Phantom reading occurs mainly during range queries, where if another transaction commits a batch of data, the results of the second query will be inconsistent between the two queries. The illusory case has been solved in repeatable reading. There are two ways to solve phantom reading: MVCC and gap locking.
Gap lock: The data locked is a range. For example, select * from test where ID < 17 for UPDATE (select * from test where ID < 17 for update (select * from test where ID < 17));
When a gap lock is used to resolve phantom reads, if another transaction attempts to insert data into the range, it blocks until the gap lock is released. Because other transactions cannot insert data, phantom reads do not occur. Gap locks can be used only when they are at the repeatable read isolation level.
Table locks
The following are all analyzed for MyIsam.
A table lock is a lock with a larger granularity than a row lock and locks a table. While the table is being modified, it will be blocked if other transactions want to modify it (conCURRENT_INSERT needs to be set to 0, otherwise concurrency is allowed).
If you want to be able to insert data in parallel, you can specify the value of concurrent_INSERT. If the specified value is 1, then data is allowed to be inserted at the end of the table if there is no void in the table (no deleted data); If the specified value is 2, data is inserted at the end of the table regardless of whether the table has a void.
Lock the page
Page locks are between row locks and table locks. The locked object is the data of a page, and the overhead of locking and releasing locks is between row locking and table locking.