MySQL锁 - lock table read 加什么锁

原创蔡璐 2024-09-04

本篇文章测试基于mysql 8.0.39版本

1. 默认参数配置

-- 查看innodb_table_lock、autocommit 参数，都为on
mysql> show variables like '%innodb_table_lock%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

mysql>
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

-- 新建表
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `addr` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

-- session 1
lock table t1 read;

-- session 2
mysql> select * from performance_schema.data_locks\G
Empty set (0.00 sec)

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: locktest
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281471151658720
            LOCK_TYPE: SHARED_READ_ONLY
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 47
       OWNER_EVENT_ID: 15
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281470973595856
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 45
       OWNER_EVENT_ID: 150
2 rows in set (0.00 sec)

在innodb_table_locks、autocommit 默认为on的情况下，lock table read 不加表级共享锁，而是加MDL锁（shared_read_only）。

2. innodb_table_locks=on、autocommit=off

-- 设置autocommit为off
mysql> set autocommit=off;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%innodb_table_lock%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

-- session 1
mysql> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)

-- session 2

-- INNODB 层添加表级共享锁
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473037856768:1064:281472963505072
ENGINE_TRANSACTION_ID: 562948014567424
            THREAD_ID: 48
             EVENT_ID: 15
        OBJECT_SCHEMA: locktest
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281472963505072
            LOCK_TYPE: TABLE
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
1 row in set (0.00 sec)

-- server 层对表添加MDL元数据锁（SHARED_READ_ONLY）
mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: locktest
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281471151556544
            LOCK_TYPE: SHARED_READ_ONLY
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 14
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281470973595856
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 45
       OWNER_EVENT_ID: 152
2 rows in set (0.01 sec)

在innodb_table_locks=on、autocommit=off的情况下，lock table read 加表级共享锁，同时加MDL锁（shared_read_only）。

3. innodb_table_locks=off、autocommit=on及innodb_table_locks=off、autocommit=off

-- 设置innodb_table_locks=off
mysql> set innodb_table_locks=off;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%innodb_table_lock%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | OFF    |
+--------------------+-------+
1 row in set (0.01 sec)

mysql>
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

-- session 1
mysql> lock table locktest.t1 read;
Query OK, 0 rows affected (0.01 sec)

-- session 2
mysql> select * from performance_schema.data_locks\G
Empty set (0.01 sec)

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: locktest
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281471151524288
            LOCK_TYPE: SHARED_READ_ONLY
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 49
       OWNER_EVENT_ID: 5
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281470973595856
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 45
       OWNER_EVENT_ID: 154
2 rows in set (0.01 sec)




-------
--session 1
mysql> set innodb_table_locks=off;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=off;
Query OK, 0 rows affected (0.01 sec)

mysql> lock table locktest.t1 read;
Query OK, 0 rows affected (0.01 sec)

mysql>  show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_table_lock%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> lock table locktest.t1 read;
Query OK, 0 rows affected (0.01 sec)


-- session 2
mysql> select * from performance_schema.data_locks\G
Empty set (0.01 sec)

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: locktest
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281471151658816
            LOCK_TYPE: SHARED_READ_ONLY
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 51
       OWNER_EVENT_ID: 6
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281470973595856
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6142
      OWNER_THREAD_ID: 45
       OWNER_EVENT_ID: 158
2 rows in set (0.01 sec)

在innodb_table_locks、autocommit 默认为on的情况下，lock table read 不加表级共享锁，而是加MDL锁（shared_read_only）。

4. 总结

在innodb_table_locks=on和autocommit=on的清况下，lock table read 并不会加表级共享锁，只加MDL锁（shared_read_only）。
在innodb_table_locks=on及autocommit=off的清况下，lock table read 加表级共享锁，同时加MDL锁（shared_read_only）。
innodb_table_locks=off 的清况下，lock table read 并不会加表级共享锁，只加MDL锁（shared_read_only）。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL锁 - lock table read 加什么锁

1. 默认参数配置

2. innodb_table_locks=on、autocommit=off

3. innodb_table_locks=off、autocommit=on及innodb_table_locks=off、autocommit=off

4. 总结

评论