Shulou(Shulou.com)06/01 Report--

Write at the front

A database is essentially a shared resource, so while maximizing concurrent access performance, you still need to ensure that each user can read and modify data in a consistent manner. Locking mechanism (Locking) is the best weapon to solve this kind of problem.

First, create a new table test, where id is the primary key, name is the secondary index, and address is the unique index.

CREATE TABLE `test` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` int (11) NOT NULL, `address` int (11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idex_ unique` (`address`), KEY `idx_ index` (`name`) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4

Row locks in the INSERT method

It can be seen that if two transactions perform INSERT operations on row records with the same primary key, because transaction A gets the row lock first, transaction B can only wait until transaction A commits and the row lock is released. Similarly, if you insert for the unique index field address, you also need to acquire a row lock, which is similar to the primary key insertion process and will not be repeated.

However, if both transactions insert for the secondary index field name, you do not need to wait for the lock to be acquired, because even if the secondary index field has the same value, it operates on different record rows in the database and does not conflict.

The result of Update method is similar to that of Insert method.

Table lock and row lock under SELECT FOR UPDATE

The transaction A SELECT FOR UPDATE statement gets the Table Lock of the table test, and transaction B will block to perform the insert operation until transaction A commits and releases the table lock. Transaction B can not acquire the corresponding row lock to perform the insert operation.

However, if the SELECT FOR UPDATE statement of transaction An is followed by WHERE id = 1, then this statement will only acquire a row lock, not a table lock, and transaction B's modification of other primary keys will not be blocked.

Gap Lock under Auxiliary Index

First, take a look at the data under the test table:

Mysql > select * from test;+----+ | id | name | address | +-- + | 3 | 1 | 3 | 6 | 1 | 2 | 7 | 2 | 4 | 8 | 10 | 5 | +-+ 4 rows in set (0.00 sec)

Gap lock can be said to be a kind of row lock, the difference is that it locks a range of records, the function is to avoid illusion, that is, the sudden increase or decrease of interval data entries. The main solutions are:

To prevent new data from being inserted in the gap, it is called a gap lock to prevent existing data from becoming data in the gap after the update operation (for example, if the name field of id = 7 is updated to 1, then the number of name = 1 will change from 2 to 3)

The conditions for InnoDB to automatically use gap locks are:

Repeatable Read isolation level, which is the default working level of MySQL. Search conditions must be indexed (if there is no index, a full table scan will be performed, which will lock all records of the entire table)

When InnoDB scans an index record, it first adds a row lock to the selected index row record, and then adds a gap lock to the gap on both sides of the index record (scan to the left to the first value smaller than the given parameter, and to the right to the first value larger than the given parameter to build an interval). If a gap is locked by transaction A, transaction B cannot insert records in that gap.

The "gap lock" we are talking about here is actually not GAP LOCK, but NEXT_KEY LOCK in RECORD LOCK + GAP LOCK,InnoDB.

Let's take a look at an example. When we build the table, we specify name as the secondary index. At present, the values of this column are [1mem2, 10]. The gap ranges are: (- ∞, 1), [1 ∞ 1], [1 minute 2], [2 minute 10], [10, + 1].

Round 1:

Transaction A SELECT... WHERE name = 1 FOR UPDATE; pair (- ∞, 2) add gap lock transaction B INSERT. Name = 1 blocking transaction B INSERT... Name =-100blocking transaction B INSERT... Name = 2 successful transaction B INSERT... Name = 3 successful

Round 2:

Transaction A SELECT... WHERE name = 2 FOR UPDATE; adds gap lock transaction B INSERT for [1,10). Name = 1 blocking transaction B INSERT... Name = 9 blocking transaction B INSERT... Name = 10 successful transaction B INSERT... Name = 0 successful

Round 3:

Transaction A SELECT... WHERE name

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.