Shulou(Shulou.com)05/31 Report--

This article mainly introduces the detailed analysis of MySQL lock mechanism, the article is very detailed, has a certain reference value, interested friends must read it!

Article catalogue

Lock

Lock classification

Shared lock (read lock, read and write mutually exclusive, read do not affect each other)

Exclusive lock (write lock, exclusive lock)

The difference between a shared lock and an exclusive lock

Watch lock

Why do you need to add an intention lock?

Intention lock: intention sharing lock and intention exclusive lock

Single table lock

Global table locking

Row lock

Upgrade row lock to table lock (row lock actually locks the index, if the entire table is scanned without using the index, the whole table will be locked)

When a row is modified or deleted, and the transaction is not committed, the row will always be locked by the row lock.

Record lock

Gap lock (Gap Locks)

The function of Gap Lock: preventing Phantom Reading

Conditions for innodb to automatically use Gap Lock

The area locked by the gap lock

Next-key lock [key lock]

The difference among record lock, gap lock and temporary lock

Lock

Lock is a mechanism for computers to coordinate multiple threads to access the same shared resource. it is mainly used to solve the concurrent security problem of multiple threads accessing the same shared resource.

Lock classification

(1) from the performance, it is divided into optimistic lock and pessimistic lock.

Version number control is used in MySQL and CAS is used in JVM.

(2) according to the type of database operation, it is divided into read lock (shared lock) and write lock (exclusive lock). Both read lock and write lock belong to pessimistic lock.

Read lock (shared lock): for peer records, multiple reads can be performed at the same time, but no transaction can be performed.

Write lock (exclusive lock): a transaction that acquires an exclusive lock can both read and modify data. Before the transaction that acquires the write lock is completed, it blocks other transactions from acquiring the write or read lock.

(3) divided into table locks and row locks in terms of granularity.

Note:

(1) both read and write locks belong to row-level locks, that is, transaction 1 to commodity An acquires a write lock, and transaction 2 to commodity B does not block each other.

(2) if the sql statement uses a row lock, when SQL uses a full table scan instead of an index, the row-level lock becomes a table lock.

(3) locks are released only when commit or rollback is executed, and all locks are released at the same time.

(4) when the Innodb engine executes ordinary modification and deletion sql statements, it will add row locks to the modified and deleted rows.

Shared lock (read lock, read and write mutually exclusive, read do not affect each other)

When transaction An acquires a record (or some) using a shared lock, transaction B can read the records and continue to add shared locks, but cannot modify them (when transaction C modifies or deletes these data, it enters a blocking state until the lock waits for timeout or transaction A commits)

How to use and release shared locks

# Lock SELECT... LOCK IN SHARE MODE# release lock commit;rollback

Action

SELECT... LOCK IN SHARE MODE adds a shared lock (share lock) to several rows of records that are read, and other transactions can only query these rows and cannot modify these records. Multiple transactions can add shared locks to the same row records, so transactions that acquire shared locks may not necessarily modify row data.

Usage scenario: reads the latest version of the result set while preventing other transactions from updating the result set

For example, the operation of commodity inventory under concurrency

Exclusive lock (write lock, exclusive lock)

Select... For update adds an exclusive lock to the read row record, allowing only the transaction that acquires the exclusive lock to modify the row record, preventing other transactions from modifying the row, just as a normal update statement adds a row lock when it is executed.

How to use an exclusive lock

# add exclusive lock select... For update / / exclusive lock # release lock commit;rollback

The difference between a shared lock and an exclusive lock

(1) once one transaction acquires an exclusive lock, other transactions can no longer acquire the exclusive lock.

Multiple transactions can add shared locks to peer data.

(2) A transaction with a shared lock on a specified row may not be able to modify the data of the row, because other transactions may also add a shared lock or an exclusive lock to the row; a transaction with an exclusive lock on the specified row can modify the data of the row with an exclusive lock

Watch lock

Table-level locking is mainly used by some non-transactional storage engines such as MyISAM,MEMORY,CSV.

Table locks are generally used during data migration.

Intention lock: intention sharing lock and intention exclusive lock

The premise for a row to add a shared lock is that the table in which the row data resides will first acquire the intended shared lock.

The premise of adding an exclusive lock on a row is that the table in which the row data is located will first acquire the intention exclusive lock.

Note: intention sharing locks and intention exclusive locks are table locks and cannot be created manually.

Why do you need to add an intention lock?

The purpose of intention locking is to inform mysql that the data in the table is already locked, without the need for progressive scanning whether to lock or not, and to improve the efficiency of locking.

Single table lock

Lock tables saas.goods read,saas.account write; / / add a read lock to the goods table in the saas library, and add a write lock to the account table to unlock it.

Global table locking

FLUSH TABLES WITH READ LOCK; / / all tables in the library are locked, read-only unlock tables; / / unlocked

Note: unlock tables is implicitly executed when the client is disconnected from the database. If you want the table lock to take effect, you must always keep the connection.

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency

Row-level locks: high overhead and slow locking; deadlocks occur; lock granularity is the smallest, lock conflict probability is the lowest, and concurrency is the highest.

Row lock

A row lock is to lock one or more rows of records.

MySQL's row locks are index-based, and row locks are added to the index, not to the row record.

As shown in the figure above, there are a primary key index and a normal index in the database. The sql statement in the figure is based on a normal index query and hits four records. In this case, two records are locked by a row lock, while other transactions modify either of these two records, which will always block [before the transaction acquiring the lock executes commit]. The following figure shows what happens when the commit statement is not executed in the above figure.

Upgrade row lock to table lock (row lock actually locks the index, if the entire table is scanned without using the index, the whole table will be locked)

In the where condition in the figure above, although template_name builds a normal index, the use of the or keyword causes the index of template_name to become invalid, so the whole table is scanned and the whole table is locked.

When a row is modified or deleted, and the transaction is not committed, the row will always be locked by the row lock.

A row is deleted in window 1, but is not committed. Updating the row in window 2 will always be blocked.

Record lock

Row lock: a row lock is a hit index, and a lock is one or more records of a table

Record lock: the record lock is a derivative lock of the row lock. The record lock is about a record in the table. The condition for the record lock to appear is that the index is accurately hit, and the index is a unique index (such as primary key id, unique index column).

Gap lock (Gap Locks)

Classic reference articles

The function of Gap Lock: preventing Phantom Reading

The purpose of the gap lock is to prevent misreading, which is achieved mainly in two ways:

(1) prevent new data from being inserted in the clearance.

(2) prevent existing data within the scope from being updated

Conditions for innodb to automatically use Gap Lock

(1) the data isolation level must be repeatable

(2) Index must be used for retrieval conditions (if no index is used, mysql will scan the whole table, which will lock all records of the whole table, including those that do not exist, and other transactions cannot be modified, deleted or added)

The area locked by the gap lock

Look for the record value A closest to the retrieval condition to the left according to the retrieval condition, as the left interval, and look for the record value B closest to the retrieval condition to the right as the right interval, that is, the locked gap is (Amemery B). In the following figure, if where number=5, then the interval range of the gap lock is [4jue 11]

Session 1:start transaction; the way to trigger the gap lock 1:select * from news where number=4 for update; the way to trigger the gap lock 2:update news set number=3 where number=4; session 2:start transaction; insert into news value (2mag4); # (blocking) insert into news value (2Mague 2); # (blocking) insert into news value (4mem4); # (blocking) insert into news value (4Mague 5); # (blocking) insert into news value (7jam 5); # (successful execution) insert into news value (9Mague 5) # (successful execution) insert into news value (11recovery5); # (successful execution)

Next-key lock [key lock]

The next-key lock actually includes a record lock and a gap lock, that is, locking a range and locking the record itself. The default locking method for InnoDB is next-key lock.

Select * from news where number=4 for update

The locking range of next-key lock is gap lock + record lock, that is, interval (2Magi 4), (4pm 5) plus gap lock, while the record of number=4 plus record lock, that is, the locking range of next-key lock is (2mem4], (4mem5).

The difference among record lock, gap lock and temporary lock

Update news set number=0 where id > 15

Sql adds a next-key lock by default. According to the figure above, the interval of the next-key lock is (- ∞, 1], (1∞ 5], (5∞ 9], (9mem11), (11meme + ∞), and the above id is more than 15. In fact, the next-key lock is added in the range of [11meme + ∞), not in the range of (15meme + ∞). Note: fields that need to use locks must be indexed because locks are added to the index and table locks are added if there is no index.

The above is all the contents of the article "detailed Analysis of MySQL Lock Mechanism". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.