MySQL行锁理解

MySQL行锁的理解以及实验操作

---

MySQL Lock

一、概图

• 内存模型 (https://dev.mysql.com/doc/refman/5.7/en/innodb-in-memory-structures.html)

  • buffer pool （https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html）

  • change buffer (https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html)

• 磁盘模型 (https://dev.mysql.com/doc/refman/5.7/en/innodb-on-disk-structures.html)

二、行锁（MDL）

1、意义

• 保证数据安全。
• 减少锁粒度，保证MySQL性能。

2、行锁的取决

• 索引（主键、唯一索引）。

3、索引和事务的关系

• 事务从逻辑上包含锁的，一个事务可能会存在一个或者多个。
• 锁只有在事务提交之后才会释放，或者锁超时也会释放锁。

三、锁（实操演示）

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `height` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),  #主键索引
  UNIQUE KEY `age` (`age`) USING BTREE, #唯一索引
  KEY `height` (`height`) #普通索引
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

1、MDL锁

1.1 表结构操作

• 修改表字段属性
• 添加表字段
• 删除表等

1.2 基本操作

• 1、是否开启MDL记录

  select * from performance_schema.setup_instruments WHERE `NAME`='wait/lock/metadata/sql/mdl';
  

• 2、开启、关闭MDL记录

  # 开启记录
  update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
  # 关闭记录
  update performance_schema.setup_instruments set enabled='NO',TIMED='NO' where name='wait/lock/metadata/sql/mdl';
  

1.3 MDL锁实验

• tx1

  BEGIN;
  SELECT * FROM test;
  COMMIT;
  

• tx2

  BEGIN;
  alter table test add address varchar(1000);
  COMMIT;
  

• 查看锁状态

  SELECT
      locked_schema,
      locked_table,
      locked_type,
      waiting_processlist_id,
      waiting_age,
      waiting_query,
      waiting_state,
      blocking_processlist_id,
      blocking_age,
      substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
      sql_kill_blocking_connection
  FROM
      (
          SELECT
              b.OWNER_THREAD_ID AS granted_thread_id,
              a.OBJECT_SCHEMA AS locked_schema,
              a.OBJECT_NAME AS locked_table,
              "Metadata Lock" AS locked_type,
              c.PROCESSLIST_ID AS waiting_processlist_id,
              c.PROCESSLIST_TIME AS waiting_age,
              c.PROCESSLIST_INFO AS waiting_query,
              c.PROCESSLIST_STATE AS waiting_state,
              d.PROCESSLIST_ID AS blocking_processlist_id,
              d.PROCESSLIST_TIME AS blocking_age,
              d.PROCESSLIST_INFO AS blocking_query,
              concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
          FROM
              performance_schema.metadata_locks a
          JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
          AND a.OBJECT_NAME = b.OBJECT_NAME
          AND a.lock_status = 'PENDING'
          AND b.lock_status = 'GRANTED'
          AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
          AND a.lock_type = 'EXCLUSIVE'
          JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
          JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
      ) t1,
      (
          SELECT
              thread_id,
              group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' 
                                                           THEN "transaction_begin" 
                                                           ELSE sql_text 
                                                           END 
                                                           ORDER BY event_id SEPARATOR ";" ) AS sql_text
          FROM
             performance_schema.events_statements_history
          GROUP BY thread_id
      ) t2
  WHERE
        t1.granted_thread_id = t2.thread_id ;
  

2、主键（唯一）索引锁 （行锁 record）

• tx1

  BEGIN;
  UPDATE test SET `name` = '美国' WHERE id = 8;
  COMMIT;
  

• tx2

  DELETE FROM test WHERE id = 8;
  

• 锁状态

  select m.*, t.* from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id = t.thread_id;
  

结论：主键索引和唯一索引效果一样。如果命中就只会锁住当前行，反之会锁住当前所属左开右开区间。

3、 普通索引（height）

(-∞, 10], （10,20] （20,50] （50,100] （100，+∞]

• tx1

  UPDATE `shouzhi`.`test` SET `name` = '美国20'  WHERE `height` = 20;
  

• tx2

  INSERT INTO `shouzhi`.`test` (`age`, `name`, `height`) VALUES (188, '美国30', 30);
  

结论：普通索引不是自己会锁住自己所在的前开后闭区间，如果是自己则会锁住自己（不包含自己）下一个区间。