Catalog

1、 background

In the project , We often use update sentence , that update Statement will lock the records in the table ? Here we use some simple cases to simulate . Here is my own understanding , If that place is misunderstood , Welcome to point out

2、 Pre knowledge

2.1 Isolation level of database

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

2.2 Database version

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)

2.3 Database storage engine

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)

2.4 Is the lock on the record or on the index

The lock is added to the index , If there is no index in the table , Is it added to the table ? It's not , It is also added to the index , There will be a default .

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking

Reference link : https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks

2.5 update...where The basic unit of locking is

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters

Here we can understand that the unit of locking is : next-key lock

2.6 Row-level locks

2.6.1 Record Locks

Record locks , That is, only one record will be locked . In fact, it locks the index of this record .

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

2.6.2 Gap Locks

Clearance lock , A gap lock is a lock on the gap between index records , That is, lock an interval . Front and back sections , Not including the record itself .

Clearance lock If using Single column unique index value To update , Yes. degeneration become Record Lock.

The purpose of the clearance lock

  1. Prevent new data from being inserted into the gap
  2. Prevent existing data from being updated into the gap .

Gap locking is not needed for statements that lock rows using a unique index to search > for a unique row. (This does not include the case that the search condition includes only > some columns of a multiple-column unique index; in that case, gap locking does occur.)

2.6.3 Next-Key Locks

Next-Key Lock yes On the index record Of Record locks and Before indexing records Of Clearance lock on clearance The combination of . It also locks a section , Front open back close interval . Including the record itself .

If the index value includes 1,5,10,30, that next key The lock may cover the following sections

(negative infinity, 1]
(1, 115
(5, 10]
(10, 30]
(30, positive infinity)

negative infinity It means negative infinity .positive infinity It means positive infinity .

2.6.4 Test the table structure of the lock table

create table test_record_lock
(
id int not null comment ' Primary key ',
age int null comment ' Age , General index ',
name varchar(10) null comment ' full name , No index ',
constraint test_record_lock_pk
primary key (id)
)
comment ' Test record lock '; create index test_record_lock_age_index
on test_record_lock (age);

2.6.5 Test data in table

mysql> select * from test_record_lock;
+----+------+--------+
| id | age | name |
+----+------+--------+
| 1 | 10 | Zhang San |
| 5 | 20 | Li Si |
| 8 | 25 | Wang Wu |
+----+------+--------+
3 rows in set (0.00 sec)

2.7 View the current lock in the database

select * from performance_schema.data_locks;

Field explanation :

Field value explain
lock_typeTABLE The lock is on the watch
RECORD Lock the record
lock_modeIX Intention exclusive lock
X perhaps Snext-key lock
Lock the record itself and the gap before the record
X,REC_NOT_GAPRecord Lock Lock only the record itself
S,REC_NOT_GAPRecord Lock Lock only the record itself
X,GAPgap lock
X,INSERT_INTENTION Insert intention lock
lock_data A specific number Represents the value of the primary key
value , value The first value is : The value of a normal index
Second value : Primary key value

doubt :X,GAP Can it be understood as X The lock degenerated into GAP lock .

3、 Test data is locked

3.1 Unique index test

This applies to the unique index of a single field , Not suitable for a unique index of multiple fields

3.1.1 Equivalent update - Records exist

explain :

  1. Add next-key lock, Then the locked record range is (1,5].
  2. Because it's the only index , And the query value exists ,next-key lock Degenerate into record lock, That is to say, in the end, only id=5 This line of data . The rest of the data does not affect .

3.1.2 Equivalent query - Record does not exist -01

explain :

  1. Add next-key lock, Then the locked record range is (5,8].
  2. Because it's the only index , And the query value does not exist ,next-key lock Degenerate into gap, That is, the final locked data range is (5,8). The rest of the data does not affect .

3.1.3 Equivalent update - Record does not exist -02

3.1.4 Scope update

1、 Less than or equal to the maximum critical value

At this point, you can find that all the scanned records in the table have been added next key lock( Lock on index )

2、 Greater than or equal to the minimum critical value
mysql> begin;
Query OK, 0 rows affected (0.01 sec) mysql> update test_record_lock set name = 'aaa' where id >= 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0 mysql> select LOCK_TYPE,INDEX_NAME,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
+-----------+------------+---------------+------------------------+
| LOCK_TYPE | INDEX_NAME | LOCK_MODE | LOCK_DATA |
+-----------+------------+---------------+------------------------+
| TABLE | NULL | IX | NULL |
| RECORD | PRIMARY | X,REC_NOT_GAP | 1 |
| RECORD | PRIMARY | X | supremum pseudo-record |
| RECORD | PRIMARY | X | 8 |
| RECORD | PRIMARY | X | 5 |
+-----------+------------+---------------+------------------------+
5 rows in set (0.01 sec)

Only records smaller than the minimum threshold can be inserted into the table .

3、 normal range

3.2 General index test

3.2.1 Equivalent update - Records exist

explain :

  1. First, the general index age add next-key lock, The scope of locking is (10,20]
  2. next-key lock And lock this record , So in id The value of the index is equal to 5 Added Record Lock
  3. Because it is a normal index and the value still exists , Therefore, a clearance lock will be added to the next section of this record Gap Lock, The locked range is (20,25)
3.2.2 Equivalent update - Record does not exist

explain :

  1. obtain next-key lock The locked range is (10,20]
  2. Because the record to be updated does not exist ,next-key lock Degenerate into gap lock, So the locked range is (10,20)
  3. Because it is a normal index and the record does not exist , So there is no need to find the next interval again .
3.2.3 Scope update

explain :

  1. Range update of normal index ,next-key-lock It does not degenerate into gap lock.

3.3 No index update

It can be seen from the above figure , Updating data tables without indexes is dangerous , It needs to be handled with care . No index update , Causes a full table scan , This causes all the scanned records to be added with next-key lock.

3、 Reference link

1、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks

2、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

Make a note of MySql update More relevant articles on what range of data will be locked

  1. Mysql update Own data in a table

    update  table ucf, table t2 set ucf.pcid = t2.pcid where t2.name = 'liming' and t2.gid= ucf.gid and ...

  2. MySQL When the record does not exist insert, When records exist update(ON DUPLICATE KEY UPDATE, REPLACE sentence )

    MySQL When the record does not exist insert, Update when records exist There are basically three solutions on the Internet . The first one is : Example 1 :insert Multiple records Suppose there is a primary key of client_id Of clients surface , You can use the following words ...

  3. MySQL update Statement and insert The writing of insert sentence is totally different , Don't confuse

    1.mysql update sentence : update user set name = 'xiaoming',age = 18 where uid = 3000; When updating records update Operation does not need to write ta ...

  4. PHP MySQL Update

    UPDATE Statement is used to modify data in a database table . Update the data in the database UPDATE Statement is used to update the existing records in the database table . grammar UPDATE table_name SET column1=value, ...

  5. mysql update Set a field in one table to the value of a field in another table

    A new field has been added to the table , There is no doubt that it is null . So I want to write the value of a field in another table to the modified table . sql The statement is not complex , But keep a record , Because I checked for a while , It may be used in the future . mysql> update cent ...

  6. When building a personal website linux Related configuration records in (mysql,jdk,nginx,redis)

    One . Development plan ( Including preparatory work , General requirements of the website, etc ) Two . The server (linux/centos) Buy . Corresponding environment configuration (jdk), Software installation (mysql, nginx, redis). Domain name resolution 3、 ... and . Prototype map . Code development (v ...

  7. [ turn ]MySQL update join sentence

    Original address :https://www.jianshu.com/p/f99665266bb1 In this tutorial , You will learn how to use MySQL UPDATE JOIN Statement to perform cross table updates . We'll step through how to use INNE ...

  8. mysql update Subquery update is not supported

    Let's look at the example first : SELECT uin,account,password,create_user_uin_tree FROM sys_user result : In the table create_user_uin_tree Mark the article ...

  9. Cross table update ,Mysql Update Join

    background A new batch of personnel data has been imported into the project , Some of these people's Department names have changed , Some of the contacts have changed , Let's call this table t_dept_members, There is another table in the system t_user_info Recorded personnel information . The request will ...

  10. Python MySQL Update

    chapter Python MySQL introduction Python MySQL Create database Python MySQL Create table Python MySQL Insert table Python MySQL Select Python M ...

Random recommendation

  1. js Quick input date

    Click here to see the effect http://keleyi.com/keleyi/phtml/jstexiao/10.htm The following code : <!DOCTYPE html> <html> < ...

  2. 【BZOJ-3667】Rabin_Miller Algorithm Randomize prime numbers

    3667: Rabin-Miller Algorithm Time Limit: 60 Sec  Memory Limit: 512 MBSubmit: 983  Solved: 302[Submit][Status ...

  3. poj2243

    Knight Moves Time Limit: 1000MS   Memory Limit: 65536K Total Submissions: 13433   Accepted: 7518 Des ...

  4. DB The weather Alpha Version instructions

    One Product introduction DB Weather is a software that can forecast the weather accurately , It is characterized by its simple design style , And the sweet way of forecasting . It's a non mainstream little fresh weather APP. Two Function is introduced So let's talk about that DB The main interface of weather and the realization of weather function ...

  5. Linux Learning notes sharing

    From learning to now , already 3 It's been months , Less than a month linux The course is coming to an end , The general situation is as follows : A week of prep , It's mainly about learning the Internet , The content of Cisco linux basic course , From scratch linux shell Script linux Project reality ...

  6. springboot Second kill course learning arrangement 1-2

    1) From database to front end , Three levels of conversion have been made , Finally, the format returned to the front end is unified DO-> model: Put it in service in , The purpose is to assemble the data from the database , Some fields come from different tables , This layer is equivalent to a real business model ...

  7. 18/03/18 04:53:44 WARN TaskSchedulerImpl: Initial job has not accepted any resources; check your cluster UI to ensure that workers are registered and have sufficient resources

    1: The problem is to start bin/spark-shell in the future , so what , perform spark Realization wordcount There was a mistake in the case of , Such as : scala> sc.textFile()).reduceBy ...

  8. getting data from the keybroad

    public static String getString() throws IOException{ InputStreamReader isr = new InoutStreamReader(S ...

  9. hiho once The first 145 Zhou

    subject 1 : The quiz The time limit :5000ms Single point time limit :1000ms Memory limit :256MB describe Small Hi. Small Ho And being small Hi Strong pull small Z, Prepare to team up for a quiz . The competition adopts the pass system , total N A level . In the i ...

  10. HDU3605(KB11-M State compression + Maximum flow )

    Escape Time Limit: 4000/2000 MS (Java/Others)    Memory Limit: 65536/65536 K (Java/Others)Total Subm ...