Welcome to the MySQL technical articles shared by the great SQL community. If you have any questions or want to learn, you can leave a message in the comment area below. After seeing it, you will answer it

problem

The original question is as follows:

If you add a shared lock to a row of a table in a MySQL transaction, in theory, the table itself will automatically add an intent shared lock. Can you use sql to find out that the table has an intent lock?

answer

The answer IS yes. Of course, you can execute the IS lock and lock state on the SQL query table.

First of all, we are discussing the InnoDB engine table in MySQL. The following discussion is based on this premise.

Before announcing the answer, let's introduce more knowledge about InnoDB engine lock. The main points are as follows

• InnoDB engine supports both table level and row level locks.
• The method of adding table level lock is the same as that of MyISAM table. Just execute LOCK TABLE READ/WRITE.
• The row lock of InnoDB table is added to the index. Therefore, if there is no appropriate index, all records in the table will be added with uplink lock. The consequences are the same as table level lock, but the impact is much greater than table level lock. Because the number of lock objects is much larger, it consumes a lot more memory.
• When adding an uplink lock, you also need to add a corresponding intention lock to the table. For example, if you want to add a shared lock (S lock) to a row of data, you should add an intention shared lock (IS lock) to the table accordingly; Similarly, if you want to add an exclusive lock (X lock) to a row of data, you should add an intention exclusive lock (IX lock) to the table accordingly.
• The intent lock is added to the root node of the clustered index, so no matter how many rows are locked, only one intent lock needs to be added.
• Here is the compatibility matrix between several locks

OK, let's take a look at how to view table level IS locks. In fact, it's very simple. You just need to view pfs.data_ Just use the locks table. Another table pfs.metadata_ The MDL lock details can be viewed in the locks table.

The query results are as follows:

[root@yejr.run] [(none)]>select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140701134495048:1350:140701396637648
ENGINE_TRANSACTION_ID: 422176111205704
            THREAD_ID: 87
             EVENT_ID: 95
        OBJECT_SCHEMA: yejr
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701396637648
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140701134495048:267:4:9:140701409130528
ENGINE_TRANSACTION_ID: 422176111205704
            THREAD_ID: 87
             EVENT_ID: 95
        OBJECT_SCHEMA: yejr
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140701409130528
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1

At this time, we can see that there are two locks on the t1 table, one IS a table level IS lock, and the other IS a shared lock on c1=1.

Similarly, we can also observe IX locks or other locks.

- session1 Perform the following SQL
[root@yejr.run] [yejr]>begin; update t1 set c4=rand()*1024 where c1=1;

- session2 query PFS.data_locks
[root@yejr.run] [(none)]>select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140701134495888:1350:140701396639728
ENGINE_TRANSACTION_ID: 104536
            THREAD_ID: 89
             EVENT_ID: 43
        OBJECT_SCHEMA: yejr
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701396639728
            LOCK_TYPE: TABLE
            LOCK_MODE: IX  <-- This is IX It's locked
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140701134495888:267:4:9:140701409135136
ENGINE_TRANSACTION_ID: 104536
            THREAD_ID: 89
             EVENT_ID: 43
        OBJECT_SCHEMA: yejr
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140701409135136
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1

Further, let's take a brief look at the MDL lock. Add shared row lock:

- session1 Add a shared row lock
[root@yejr.run] [yejr]>begin; select * from t1 where c1=1 for share;

- session2 What are in the query table MDL lock
[root@yejr.run] [(none)]>select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: yejr
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701215694512
            LOCK_TYPE: SHARED_READ  <- Shared read lock: multiple shared row locks can be added at the same time
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:5761
      OWNER_THREAD_ID: 87
       OWNER_EVENT_ID: 100

Also look at the exclusive row lock:

- session1 Add an exclusive lock
[root@yejr.run] [yejr]>begin; update t1 set c4=rand()*1024 where c1=1;

- session2 What are in the query table MDL lock
[root@yejr.run] [(none)]>select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: yejr
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701215694640
            LOCK_TYPE: SHARED_WRITE  <- Shared write locks, multiple exclusive row locks (different data rows) can be added at the same time
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:5761
      OWNER_THREAD_ID: 89
       OWNER_EVENT_ID: 43

Well, there are already methods. More situations can be played by yourself:)

testing environment

Server version: 8.0.23 MySQL Community Server - GPL

The above PFS functions of viewing row locks and MDL locks should be above 8.0.

Enjoy MySQL : )

Article recommendation:

Technology sharing | MGR best practice
https://mp.weixin.qq.com/s/66...

Technology sharing | the way to repair MGR Bug in Wanli database
https://mp.weixin.qq.com/s/Ia...

Macos system compiler percona and the difference of some functions on Macos system
https://mp.weixin.qq.com/s/jA...

Technology sharing | using systemd to manage single machine and multiple instances of MySQL
https://mp.weixin.qq.com/s/iJ...

Product | GreatSQL to create a better MGR ecology
https://mp.weixin.qq.com/s/By...

Product | GreatSQL MGR optimization reference
https://mp.weixin.qq.com/s/5m...

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.

Gitee:
https://gitee.com/GreatSQL/Gr...

GitHub:
https://github.com/GreatSQL/G...

Wechat & QQ group:

You can scan code to add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR exchange wechat group, or directly scan code to join GreatSQL/MGR exchange QQ group.

This article is composed of blog one article multi posting platform OpenWrite release!