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

Innodb locks can be analyzed from several dimensions: level, type level, row level lock, table level lock type shared lock (S), also known as write lock, level: row level lock intentional shared lock (IS), also known as intention write lock level: table level lock exclusive lock (X), also known as read lock belongs to row level lock: row level lock intentional exclusive lock (IX) Also known as intentional read-lock level: table-level row locking algorithm Record LocksGap LocksNext-Key LocksInsert intention LocksAUTO-INC LocksPredicate Locks for Spatial Indexes lock compatibility see the following figure, let's start with the type of shared lock to allow transactions that hold locks to read rows. If a transaction acquires a shared lock for row x, and then the transaction b also requests a lock for row x, it will be processed. If b requests a shared lock for row x, it will be granted immediately. At this time, both a transaction and b transaction have shared locks for rows x. If b requests an exclusive lock for x, it will not be granted immediately, because shared locks and exclusive locks are incompatible exclusive locks that allow transactions that hold locks to update or delete rows. If a transaction acquires an exclusive lock on the x line, then the b transaction also requests the lock on the x line. At this time, no matter whether the lock requested by the b transaction is a shared lock or an exclusive lock, it cannot be granted immediately. The b transaction can not be granted until the a transaction releases the exclusive lock on the x line, because the exclusive lock is incompatible with any lock. Innodb supports multi-granularity locking. This kind of locking allows transactions to exist on row-level locks and table-level locks. In order to support operations at different granularities, the innodb storage engine supports an additional locking method, called intentional locking, which divides locked objects into multiple levels, which means that transactions want to lock at a finer granularity, that is, if a transaction needs to add exclusive locks for record R Then it is necessary to lock the database, table, and page where record R is located, and finally add an exclusive lock to record R. if any part causes waiting, then the operation requires the completion of coarse-grained lock. The intent lock is a table-level lock, which is designed to reveal the type of lock that the next row will be requested in the next transaction. Because the innodb storage engine supports row-level locks, the intent lock does not actually block any request except a full table scan. The above may not be easy to understand, but let me give you a practical example. We have a student table mysql > show create table student. +- - -+ | Table | Create Table | +-+- - - -+ | student | CREATE TABLE student (id int (11) NOT NULL AUTO_INCREMENT) Student_num int (11) NOT NULL DEFAULT'0' COMMENT 'student number', name varchar (32) NOT NULL DEFAULT''COMMENT' student name', PRIMARY KEY (id) UNIQUE KEY uqidx_student_num (student_num)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | +-- - -+ 1 row in set (0.00 sec) mysql > select * from student + -. -+ 5 rows in set (0.00 sec) session1mysql > start transaction Query OK, 0 rows affected (0.00 sec) mysql > select * from student where student_num=4 for update +-+ | id | student_num | name | +-+ | 4 | 4 | zhaoliu | +-+ 1 row in set (0.00 sec) session2mysql > LOCK TABLE student write At this time, we found that session2 has been waiting, because session2 wants to obtain the write lock of the entire student table. If the session2 application is successful, it can modify any row of the student table. Then people will say that session1 has acquired the exclusive lock of student_num=4. If the session2 application is successful, then student_num=4 's record will also be modified by session2, so at this time session2 will definitely block. So according to what method does the database judge that the session2 is blocked? there are only two ways to determine whether the table is locked by other transactions to determine whether there is a row lock in each row of the table. According to the previous description, if we want to add an exclusive lock to a certain record, then we will first add a shared exclusive lock to the corresponding table of the record, and then add an exclusive lock to the record. At this point, we can find that session1 can add a shared exclusive lock to the student table, then session2 finds that there are other transactions and shared exclusive locks on the student, so it will block. If there is no intention lock, then session2 has to take method 2, then you need to judge each row, then you need to traverse the entire table, which is very inefficient, especially when there is a large amount of data in the table. The intention shared lock transaction wants to obtain the shared lock of certain rows in a table. The intention exclusive lock transaction wants to obtain the exclusive lock of certain rows in a table. Please note: for insert, update, delete,InnoDB will automatically add exclusive locks to the data involved. For general Select statements, InnoDB does not add any locks. Some people may wonder what is a general select and what is a special select. A general select is a select column from table where Xero1 lock in share mode or a special select is a select column from table where Xero1 lock in share mode or a select column from table where Xero1 for udpate, preceded by a shared lock, and the latter with an exclusive lock.

Row lock

A Record Locks record lock is a lock on an index record, such as SELECT C1 FROM t WHERE C1 = 10 FOR UPDATE Prevent any other transaction from inserting, updating, or deleting a row record lock with a value of 10 of t.c1. If a table does not define any index, like this, innodb creates a hidden clustered index and uses this index for record locking. Note that innodb's record lock is locked for the index, not for physical records, so although it accesses records of different rows However, if the same index key is used, there will be a lock conflict Gap Locks gap lock is the gap between locked index records, or the gap before the first index record or after the last index record. For example, SELECT C1 FROM t WHERE C1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, regardless of whether there are any such values in the column, because the intervals between all existing values in the range are locked. For statements that lock rows with a unique index, there is no need to use gap locks (this does not include the case where the search criteria include only some columns with multiple unique indexes In this case, gap locking does occur.) for example, if the id column is a unique index, the following statement will only add an index record lock on top of that row of id=100, regardless of whether other sessions (session) insert data in the previous gap. SELECT C1 FROM t WHERE C1 = 10 FOR UPDATE If id has no index or unique index, the statement will lock the main function of the gap lock mentioned above, that is, to form a Next-key lock with the record lock to solve the problem of phantom reading. the gap lock has different scope of function under different isolation levels, and the one that can play the role of gap lock is the 'REPEATTABLE READ' isolation level. at this level, the Next-Key lock with gap lock is used to solve the problem of phantom rows. This involves information about transaction isolation level and consistent reading, and I will update the corresponding article later. Next-Key Locksnext-key lock is a combination of record lock on index record and gap lock in front of index record, which is equivalent to Record Locks+Gap LocksInnoDB implementing row-level lock in this form. When it looks up or scans table indexes, it sets shared or exclusive locks on index records it encounters. Therefore, row-level locks are actually index record locks. Next-key locks also affect the gap before index records. That is, a next-key lock is an index record lock plus a gap lock before the index record. If one session has a shared or exclusive lock on the index of record R, another session cannot insert a new index record in the gap before R in the indexing order. Let's look at a table mysql > create table goods (- > id int not null auto_increment primary key,- > title varchar (32) not null default''comment' trade name',-> classify tinyint not null default 0 comment 'commodity type',-> index idx_classify (classify)->) engine=innodb charset=utf8 Mysql > insert into goods (title,classify) values ('Commodity 1), (' Commodity 3), ('Commodity 5), (' Commodity 5), ('Commodity 6), (3) (' Commodity 7), (5) ('Commodity 8), (' Commodity 10); mysql > select * from goods " +-+ | id | title | classify | +-- + | 1 | Commodity 1 | 1 | 2 | Commodity 2 | 3 | 3 | Commodity 3 | 5 | 4 | 8 | 5 | Commodity 5 | 10 | 6 | Commodity 6 | 1 | 7 | Commodity 7 | 3 | 8 | Merchant | Product 8 | 5 | 9 | Commodity 9 | 8 | 10 | Commodity 10 | 10 | +-+ 10 rows in set (0.00 sec) mysql > select distinct (classify) from goods +-+ | classify | +-+ | 1 | 3 | 5 | 8 | 10 | +-+ 5 rows in set (0.02 sec) # under the REPEATTABLE READ isolation level, when executing a query, the lock range for writing Next-Key is as follows (- ∞, 1) lock the gap before index item 1 and 1, because there are no other index entries before 1. So negative infinity (1 ∞ 3) locks the gap before 1 and 3, excluding 1, including 3 (3) ditto (5) ditto (5) ditto (8) ditto (8) ditto (10, ditto) the gap after locking index items 10 and 10, because there are no other index entries after 10, so for positive infinity, let's use a demo to verify Session1mysql > start transaction. Query OK, 0 rows affected (0.00 sec) mysql > select * from goods where classify=3 for update +-+ | id | title | classify | +-+ | 2 | Commodity 2 | 3 | 7 | Commodity 7 | 3 | + 2 rows in set (0.00 sec) # for the secondary index, the Next-Key lock is added. The locking range is (1). It is important to note that the innodb storage engine also adds gab lock to the next key value of the secondary index, that is, there is a lock Session2mysql > start transaction in the secondary index range (3). Query OK, 0 rows affected (0.00 sec)

Mysql > insert into goods (title,classify) select 'merchandise 111Zongjie 4 Shi # at this time, you can see that session2 is blocked, because the locking range of session1's Next-Key is (1meme 3), (3penny 5), which happens to include 4mysql > insert into goods (title,classify) select' merchandise 11LQ 6. Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 insert classify=6 immediately succeeds, because 6 is not in the range of (1), (3) Insert intention LocksInsert intention lock is a gap lock set by the INSERT operation before the row is inserted. This lock indicates that when the intention to insert is that multiple transactions are inserted into the same index gap, you do not have to wait for each other if they are not inserted in the same position in the gap. Suppose there are index records with values of 4 and 7. The two transactions try to insert 5 and 6, respectively, lock the gap between 4 and 7 with an insert intention lock, and then acquire an exclusive lock for the inserted row, but do not block each other because the rows are not conflicting. AUTO-INC LocksAUTO-INC locks are special table-level locks taken by transactions inserted into a table with an AUTO_INCREMENT column. In the simplest example, if a transaction is inserting values into a table, any other transaction must wait for its own insert to be performed on the table so that the row inserted by the first transaction receives consecutive primary key values. Predicate Locks for Spatial Indexesinnodb supports spatial indexes, but if Next-Key is used to support spatial indexes, it cannot meet the requirements. This is because ordinary indexes are key-value types, which means that the index has a direction, which is one-way, either ascending or descending, which enables the database storage engine to use indexes for general range queries, but on spatial data types. This one-way ordering becomes useless, because the spatial data is multi-dimensional, regional or spatial, and there is no definite direction order, so the one-way Next-Key can not meet the requirement that the spatial index is built on the love MBR, and innodb adds a predicate lock to the MBR of the index item. Implement concurrency control on spatial indexes links to Resources: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html Books: high performance MySQMySQL Technology Insider innoDB Storage engine Database transaction Management and concurrency Control

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.