It feels like the database has a lot of overstated problems, but it’s always going to be problematic in practice. It’s a good time to discuss this topic with a real pothole scenario. Climbers often start to understand a problem, and this idea was the start of a series of Articles for Pitfall Climbers.
In order to control the length (within 5000 words) of the article is divided into two parts, this article mainly talks about two kinds of Reading and innoDB lock design.
Customer complaint Form
This article uses this table as an example to explain, notice that the example has 1 clustered index and 2 secondary indexes:
- Clustered -index: id specifies the clustered index of the clustered index.
- Secondary-index: user_id and user_name are secondary indexes, not unique;
Problems on pit
A connection wants to modify all data for user 555 and does not allow new data to be generated.
select * from tb_complaint_user where user_id = 555 for update; # write lock-- Modify all data of user 555 commit;Copy the codeTo their disappointment, another thread successfully inserted user 555’s data, but another thread’s UPDATE 555 statement was blocked.
The solution
The experienced student immediately found that the problem came from the isolation level and looked at the isolation level and found the reat-commited level. (note that auto_commit is always false) Try changing the isolation level to REPEATABLE-READ in the test environment and retry the insert to block successfully. (Be careful not to change the configuration in production -)
What’s going on in this whole process?
Background knowledge
Innodb is based on transactions and indexes: 1) In InnoDB, only a select is in a transaction, not just an update. 2) InnoDB is index-based, with row data stored on leaf-records at clustered indexes;
In addition, if not specified, the isolation level in this example is read-COMMITTED, auto_commit = false;
Two reads
Innodb’s official documentation divides reads into locking reads and none-locking reads according to whether or not they need to request database locks.
None-locking reads are queries that are not locked, which is the most commonly used select. Under normal SELECT statements, our transaction will not be locked and therefore will not be blocked by any lock.
select * from tb_xxxx [where xxxx] [group by xxx] [having xxx] [order by xxx]
Copy the codeLocking reads is easy to understand. Locking reads are required to request a database lock, and locking reads may need to wait for the lock to be blocked. The following reading statements are all locking reads.
select xxx from tb_xxx for share;
select xxx from tb_xxx for update;
update xxx set xxx = xxx wherexxx = xxx; // Since the direct call to update/delete will also apply andforUpdate lock, delete from XXXwhere xxx;
Copy the codeWhat is the lock
Say first what is the lock, lock design actually very much not only is can use the db, redis, multithreading is used in development, business development and the design of the lock is also often used when: such as attend a double tenth goods couldn’t be pulled from the shelves, this time taobao system for the goods from the can add a lock products business.
What is a lock? From an implementation perspective, it’s probably just a row tag that identifies the type of lock, the object of the lock, the owner of the lock, and so on. Understanding lock is a mechanism used to restrict the use of a certain ability of a certain resource. This perspective brings us several perspectives to understand lock: what resource is locked, what ability the lock limits others, what ability the lock endows the owner, and whether multiple locks coexist.
Innodb lock classification
(1) data and metaData
Definition and Design
The first way innoDB classifies locks is by “reason”. MetaData locks that change the definition of metaData such as table,procedure, or DB are called metaData locks, and locks that change or restrict data are called datalock. For example, DDL statements need to lock the table definition to obtain a table meta lock, while modifying a row in a table requires a datalock lock.
Datalock is usually based on transaction, and the TRX that ends the lock is released (ROLLBACK or commit). Of course, when the session with the DB ends, the TRX will also end and the Datalock will release.
Metadata locks are usually session-based rather than transaction-based, so metadata locks cannot be terminated by terminating the current TRX. Unlock needs to display a call, wait for the DDL to complete, or close the session.
LOCK TABLES table_name [READ | WRITE]
UNLOCK TABLES;
Copy the codeSee the way
Mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql Of course, you can also see the lock information by looking at innoDB status directly.
select * from performance_schema.metadata_locks; select * from performance_schema.data_locks; Show engine innodb status;Copy the code
(2) of X and S
This part of the mutual exclusion (X=exclusive) and shared (S=shared) lock is more about the design of mutual exclusion and sharing, rather than a specific database lock, this kind of shared and mutual exclusion relationship for specific resources, whether a record or a table.
An S lock, like its name shared, can be accessed by multiple TRX (T1, T2, T3). Obtaining an S lock means that the resource (record/table) is being read “carefully”. The restriction is that no DML operation can occur during the read. If Java’s GC collects objects that cannot be collected because they are locked by the root node, then the lock is a shared lock that can be shared by multiple roots, limiting the ability to collect.
An X lock is exclusive and can only be acquired by one TRX at a time. The next TRX lock is usually blocked until the lock is released. An X lock assumes that the resource is being modified by a TRX lock and cannot be read by an S lock or written by another THREAD with an X lock. Sync and Lock in Java are exclusive locks that limit the execution permissions of object code.
(3) Row lock: record, gap, next-key
The general concept of row lock can be divided into record lock, insert lock, gap lock, and next-key lock according to the scope of row lock. Next-key is actually a combination of Record and gap, so the focus is on understanding recard, INSERT, and gap locks.
(3.1) record lock
design
Record Lock should be more accurately called index Record Lock. The goal of this lock is to hit the SQL conditionOne by oneIndex Records, hereIndex is the key used to execute the plan. In addition, a record lock is placed on the PK. So a hit record is likely to have multiple record locks
Another common misconception is that the granularity of index record is “one by one”. Key = XXX, pk= XXX, pk= XXX, pk= XXX (key= XXX) (key= XXX) (key= XXX) (key= XXX) (key= XXX)
Lock is record not gap (X/S,LOCK_REC_NOT_GAP).
Case analysis
Let’s take a look at the example to understand the above Settings. Now we have two rows of data in the user_complaint table:
We select by userID and name, explain SQL finds that the execution plan uses userID as index. If you look at the recordMode and indexName in the DATA_LOCKS table, innoDB uses recordLock on pk and userID as mentioned above. Select * from lockData where user_id (userId=555, id=1) and pk (user_id (id=1));
explain select * from tb_user_complaint where user_id = 555 and user_name = "macavity" for update;
select * from tb_user_complaint where user_id = 555 and user_name = "macavity" for update;
select * from performance_schema.data_locks;
rollback;
Copy the code
Select userID from recordLock (select userID from recordLock); recordLock (select userID from recordLock); recordLock (select userName from recordLock); If id=1, then pk is the only index lock in the plan.
X/S record locks
When record is combined with the X and S mechanism, it is what we most commonly call row sharing exclusive locking. The following mutex diagram is obtained. Note that X and S in the mutexes refer to rows locked on the same row. Do not confuse other rows with X and S of the table and rows X and S in this graph.
(3.2) Insert intention lock
Insert operations in DML are different from update and delete. Udpate and DELETE operate on existing data (index records). The insert operation (update to index is actually a different update+ INSERT operation) adds index records to the gap. So the lock involved in the INSERT operation is no longer a record lock but an INSERT intention lock.
Insert locks cannot be rejected by record locks because insert rows do not yet exist (except for those that hit unique key duplicate error). That’s the problem in this case. To prevent INSERT locks, InnoDB has designed a pivot for non-existent locks
Gap lock (3.3)
design
Gaplock, as its name suggests, no longer locks index records, but a gap between records. The lockmode of Gap lock is (X/S,Gap). It is important to note that a gap lock represents a gap and does not represent a gap boundary (provided there is no row lock on the boundary itself), so a gap lock is not mutually exclusive with a record lock that represents an entity row, but with an INSERT lock that also limits a gap.
Innodb only adopts gap lock in isolation>= REPEATble -read (RR) isolation level, so we need to change the isolation level. The relationship between isolation levels and locks will be discussed in more detail in the next article. The purpose of this article is to understand locks.
case
Let’s execute the following SQL again.
set @@session.transaction_isolation='repeatable-read';
commit;
select * from tb_user_complaint where user_id = 555 for update;
Copy the codeWhen the isolation level changes to RR, we execute the lock statement again and find an extra line of Gaplock. Gaplock marks the end of gap at (userId=580, id=36), which is the first record after (555,16). At this time (555, 16) to (580, 36) can not insert any data.
+------+----------------------------+---------+------------------------+------------+----------------------------+------ -----+ | id | created_at | user_id | contents | is_archive | last_updated_at | user_name | +------+----------------------------+---------+------------------------+------------+----------------------------+------ -- -- -- -- -- + | 1 | 2020-02-12 15:12:11. 922491 | 555 | | an - test - 1 ^ @ 20:12:44. | 2020-02-12 | 989804 macavity | | | 2 15:12:19 2020-02-12. 214543 | 222 | | an - test - 1 ^ @ 15:12:19. | 2020-02-12 | 214543 macavity | | | 10 2020-02-12 15:26:50. 164283 | 111 | | an - test - 1 ^ @ 15:26:50. | 2020-02-12 | 164283 macavity | | | 16 2020-02-12 15:42:58. 989849 | 555 | an - typed - by Bob | ^ @ 15:42:58. | 2020-02-12 | 989849 macavity | | | 36 in 2020-02-12 18:03:55. 962341 | 580 | | an - test - 1 ^ @ 18:03:55. | 2020-02-12 | 962341 macavity | | 1012 | 2020-02-12 20:52:51. 732739 | 600 | | an - test - 1 ^ @ 20:52:51. | 2020-02-12 | 732739 kiki | +------+----------------------------+---------+------------------------+------------+----------------------------+------ -----+Copy the code
It is important to note that (X, Gap) represents a Gap lock and does not represent a record lock, i.e., (580,36) is not locked by a row lock. The select for UPDATE line still acquires the (X, REC_NOT_GAP) lock for that row.
Gap and INSERT lock are mutually exclusive and shared
Let’s look at the co-exclusive relationship between gap locks and INSERT locks:
- Gaps are compatible with each other, and different TRX can have Gap locks in overlapping intervals. (The x and s in the gap lock are more indicative of what statement caused the gap lock.)
- Insert a gap lock first to block inserts within the range;
- Insert before commit: Does not cause a gap lock block, but affects the gap lock interval because the INSERT row already exists.
(3.4) next – the key lock
In the case of gap lock above, when we change the isolation level, the gap lock changes except the red X and gap, but the green part also changes with the isolation level. The previous two rows were X, REC_NOT_GAP, which becomes X here.
X/S is the lockmode for the next key lock, representing a record-lock R and a gap lock (R-1, R). The naming is too confusing. For example, (555,1) marked with X actually represents two locks:
- (555,1) X, rec_not_gap
- (222, 2),(555,1)) X, gap is the gap write lock between 555 and the previous record
(3.5) Row Lock summary
Before we talk about table locks, let’s summarize the row locks we talked about. Please refer to the following figure:
select * from tb_user_complaints where user_id = 555 for update;
Copy the code
(4) Table lock: IX and IS, X and S
Table locks are not encountered as often as row locks.
(4.1) Ideographic lock
When a row Record is locked, the table schema can be modified directly or dropped. To prevent such table-level operations innoDB designs intention locks to help quickly identify if a transaction is being locked or other DML operations within a table. Intent lock IS a table-level lock, lockType IS TABLE, lockMode IS IX/IS; However, intentional locking is intended to restrict the operation of the table because there are rows in the table, so intentional locking is more of a datalock than a true metalock. An S lock on a row triggers an IS lock, while a write on a row (update, INSERT related) triggers an IX lock.
IX and IS are shared locks because they do not lock the table itself. From metaLock’s perspective, IX IS a shared write lock and IS IS a shared read lock.
Select * from tb_user_complaint where user_id = 555 for share; The datalocks and MetadATA_LOCKS tables have records of intent locks:
(4.2) Read/write lock of the table
Another type of table lock is the X and S lock corresponding to row locks. The X lock at the table level is similar to the X and S locks at the S and row levels, except that it locks the metadata associated with the table, making this lock a true metalock. When we do DDL, such as RENAME, ALTER or DROP, we first acquire metalock’s write lock. To display the table X/S lock, execute the following statement.
LOCK TABLES table_name [READ | WRITE]
UNLOCK TABLES;
Copy the code(4.3) Mutually exclusive relationship of table locks
A table on a table object can also be used to represent a mutually exclusive shared relationship:
- The X lock must be mutually exclusive with all locks, including itself
This is easy to understand because an X lock usually represents a change to the Lock object, so other changes to the lock object itself are queued, as are locking data inside the object. So the X lock is also mutually exclusive with all intent locks (representing table internal data).
- S lock IS mutually exclusive with X and IX, compatible with S and IS
S IS compatible with class S locks (S, IS). It is also well understood that the S and X locks are mutually exclusive, just as row X and S are mutually exclusive. However, I have been unable to understand why the S lock is mutually exclusive with the IX lock. What is the relationship between the IX lock and the meta data locked in the table? Actually if out of meta this limit lock S understood to table a whole and the lock is easy to understand, S lock to lock the entire table, the table all can only read can’t write in both meta data.
- I locks are compatible with I locks
Because the I lock represents the locking of row data, mutual exclusion between I locks does not occur. Even if the I lock corresponds to the same row, the mutual exclusion problem is handled by the row lock, after all, the I lock is table level and no longer responsible for the row information.
- IX and XS of the table are mutually exclusive and compatible with IXIS
IX and XS are mutually exclusive. Both X and S freeze changes to the entire table. Of course, IX does not tolerate row changes.
- IS IS mutually exclusive with X only
IS and IXIS are not mutually exclusive because they are both read and do not affect each other. IS and IXIS are not mutually exclusive because they are both identified data. IS and X are mutually exclusive because X usually represents a schema change, which often leads to a row change and IS incompatible with the purpose of S lock.
Fixation of trailer
The next chapter will focus on several issues: There are four isolation levels and several cases of inconsistency (dirty read magic read and non-repeatable read). How none-locking reading is consistent in different isolation levels, and how locking reads are locked in different isolation levels to solve the inconsistency problem.
I hope you can give me a thumbs up if you think it is helpful. Your support is my motivation to continue sorting out cases!