preface
Database is a system that allows multi – user, multi – session and multi – thread access. In order to ensure the consistency and integrity of data in concurrent access, transactions are generally used for control, with locks as auxiliary means. So today I took a look at locking and how it works in transactions.
The classification of the lock
Let’s first look at the classification of the lock and its role in which some, so that we can better analyze the use of the scene.
Optimism/pessimism lock
Optimistic locking: Data is read on the assumption that each transaction is independent and that they will handle their own portion of the data. If another transaction changes its own data while updating the data, all previous operations are rolled back.
Pessimistic lock: a conservative lock acquisition policy is adopted. If another transaction has acquired the lock, it must wait for the lock to be released before continuing.
Shared/exclusive lock
Shared lock: Also known as read lock. When the current transaction is reading, other transactions are allowed to read concurrently, but other transactions are not allowed to lock exclusively.
Exclusive lock: also known as write lock, while the write lock is occupied, if other transactions want to acquire the read/write lock, they must wait in a queue.
Table/row locks
Table lock: When data is being manipulated, the entire table is directly locked. The operation granularity is very large, which makes it easy for other transactions to wait, but does not cause deadlock.
Row lock: For row record concurrency control, the lock granularity is very fine, can support high concurrency, but can not rule out the occurrence of deadlock. SQL > alter table lock table lock table lock table lock table lock
Among them, row locks are subdivided into the following locks:
Record lock: Locks only one record. Record locks are used when precise queries are made against unique indexes, including primary keys.
Gap lock: When a range query is used, the interval data that meets the criteria is locked. Gap locks are used for queries involving normal (that is, not unique) indexes.
Next-key: a temporary lock, which can be understood as a record lock and a gap lock. A temporary lock is used when a range lookup on a unique index or a lookup on a unique index does not exist, which can be interpreted as locking nonexistent records.
The above gap lock and temporary lock effectively prevent transaction illusorality and avoid data addition or deletion during the search.
Intent locks
Intention lock is a kind of table lock, it only represents a kind of operation intention. When we use the row lock with relatively small granularity, we need to check whether there is a lock line by line, which is inefficient.
After the intent lock, there is no need to go through the investigation line by line, but only need to detect the corresponding intent lock.
Intentional locks include intentional shared locks and intentional exclusive locks. Mysql specifies that when a transaction is locked on a shared/exclusive lock, it must first acquire a compatible intent lock, otherwise it must wait for the lock to be released.
For example, if there is an intention to share the lock on the current data, if other transactions want to exclude the lock, it is incompatible and must wait to be released. The compatibility table for intentional locks and shared/exclusive locks is as follows:
| Intent shared lock | Intent exclusive lock | |
|---|---|---|
| A Shared lock | Compatible with | The mutex |
| Exclusive lock | The mutex | The mutex |
MVCC
MVCC is not part of locking operations, but it is a technique used at the transaction isolation level, and because it has snapchat-like capabilities, it can greatly reduce the use of locks and concurrency conflicts. Check out this article: MVCC.
The use of locks in transactions
In the previous transaction explanation article, we talked about transaction isolation, and transaction isolation is dependent on the use of locks, so now let’s look at the specific use.
Repeatable read
Repeatable reads use MVCC snapshots, so locks are not required most of the time when reading data.
However, if you use UPDATE, DELETE, or SELECT with FOR UPDATE or FOR SHARE, the lock will be used according to the following conditions:
- A record lock is used to find exactly a record on a unique index
- For other searches, InnoDB locks the range of scanned indexes, using gap locks or temporary locks to prevent phantom reads
Read the submission
UPDATE, DELETE, or SELECT with FOR UPDATE or FOR SHARE. When the storage engine sends filtered records to the mysql Server layer, irrelevant data is unlocked, so no gap locks or temporary locks are involved.
They are only used for foreign key constraint checking and duplicate key checking.
Phantom reading may occur because gap locks are disabled.
Uncommitted read
In innodb storage engines in mysql do SELECT won’t do any locks, if is the myisam storage engine, the Shared lock at the meeting.
If you use UPDATE, DELETE, or SELECT with FOR UPDATE or FOR SHARE, the same principles apply to read commits.
Serializable read
Serializable reads are automatically converted to select… FOR SHARE to ensure read and write serialization.
conclusion
This article introduces the classification of locks and the use of locks in transactions. Most of the time, mysql transactions are automatically locked without our intervention.
Therefore, we should pay special attention to performance loss when using display lock statements such as SELECT with FOR UPDATE (exclusive lock) or FOR SHARE (shared lock), which may cause gap locks and temporary locks, causing irrelevant records to be blocked and waiting.
Interested friends can search the public account “Read new technology”, pay attention to more pushed articles.
Thank you for your support!
Read new technology, read more new knowledge.
