• 唯一约束锁分析


    MySQL:8.0.20 MySQL Community Server - GPL

    隔离级别:RC

    8版中本有performance.schema.data_locks 表,方便分析锁问题

    创建测试数据:

    mysql> show create table t1G
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      `id` int NOT NULL AUTO_INCREMENT,
      `od` int DEFAULT NULL,
      `name` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `od` (`od`)
    ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    mysql> insert into t1(id,od,name) values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d'),(100,100,'fff');

    1、insert 唯一键不冲突

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into t1(id,od,name) values(50,50,'def');
    Query OK, 1 row affected (0.00 sec)

    performance.schema.data_locks 表 查询结果如下

    库名 表名 INDEX_NAME lock_type lock_mode LOCK_STATUS LOCK_DATA
    ceshi t1 NULL TABLE IX GRANTED NULL

    2、insert into 主键冲突

    mysql> begin;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into t1(id,od,name) values(4,4,'d');
    ERROR 1062 (23000): Duplicate entry '4' for key 't1.PRIMARY'
    库名 表名 INDEX_NAME lock_type lock_mode LOCK_STATUS LOCK_DATA
    ceshi t1 NULL TABLE IX GRANTED NULL
    ceshi t1 PRIMARY RECORD S,REC_NOT_GAP GRANTED 4

    3、insert into 唯一约束冲突

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into t1(id,od,name) values(400,4,'d');
    ERROR 1062 (23000): Duplicate entry '4' for key 't1.od'
    库名 表名 INDEX_NAME lock_type lock_mode LOCK_STATUS LOCK_DATA
    ceshi t1 NULL TABLE IX GRANTED NULL
    ceshi t1 od RECORD S GRANTED 4

    4、replace into ... 主键/唯一约束冲突

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> replace into t1(id,od,name) values(4,4,'ddddd');
    Query OK, 2 rows affected (0.00 sec)
    库名 表名 INDEX_NAME lock_type lock_mode LOCK_STATUS LOCK_DATA
    ceshi t1 NULL TABLE IX GRANTED NULL
    ceshi t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 4
    ceshi t1 od RECORD X,REC_NOT_GAP GRANTED 4
    ceshi   t1 od RECORD X,GAP GRANTED 4, 4
    ceshi t1 od RECORD X GRANTED 100, 100

    5、replace into ... 唯一约束冲突

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> replace into t1(id,od,name) values(null,4,'ddddd');
    Query OK, 2 rows affected (0.00 sec)
    库名 表名 INDEX_NAME lock_type lock_mode LOCK_STATUS LOCK_DATA
    ceshi t1 NULL TABLE IX GRANTED NULL
    ceshi t1 od RECORD X GRANTED 4, 4
    ceshi t1 od RECORD X GRANTED 100, 100
    ceshi   t1 PRIMARY RECORD X,REC_NOT_GAP GRANTED 4
    ceshi t1 od RECORD X,GAP GRANTED 4, 101

    6、同一事务 delete / insert into 唯一约束

    库名 表名 INDEX_NAME lock_type lock_mode LOCK_STATUS LOCK_DATA
    ceshi t1 NULL TABLE IX GRANTED NULL
    ceshi t1 od RECORD X,REC_NOT_GAP GRANTED 4, 4
    ceshi t1 od PRIMARY X,REC_NOT_GAP GRANTED 4
                 
    ceshi   t1 od RECORD S,GAP GRANTED 4, 4
    ceshi t1 od RECORD S GRANTED 100, 100
  • 相关阅读:
    jquery防冲突的写法
    easyUI.checkForm
    获取树形节根节点下面所有层级子节点
    自动发布web应用程序或者网站
    MVC UI Jquery
    Linq模糊查询
    常用正则表达式示例
    Easy UI中,当批量操作后,移除总复选框的选中状态
    常用的JS
    检查是否安装或运行了IIS服务
  • 原文地址:https://www.cnblogs.com/nanxiang/p/15120028.html
Copyright © 2020-2023  润新知