【数据库】mysql innodb存储引擎加锁实验

原创 已于 2023-06-29 20:23:55 修改
· 182 阅读 · 0 ·
版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #mysql

于 2023-06-29 20:20:30 首次发布

环境

实验隔离级别：READ COMMITTED（RC，读已提交）
注意innodb默认隔离级别是【REPEATABLE READ（RR，可重复读）】

实验过程

1 启动服务端
C:\Program Files\MySQL\MySQL Server 8.0\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
2 启动客户端

分别打开N个终端，作为两个数据库客户端

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p密码
3 客户端配置修改

（1）关闭自动提交

show variables like 'autocommit';
set autocommit = 0;

（2）查看&设置隔离级别

# 查看隔离级别——————————————————————————————————————————————
SELECT @@GLOBAL.transaction_isolation;
SELECT @@SESSION.transaction_isolation;
show variables like '%iso%';

# 设置全局隔离级别
set global transaction isolation level REPEATABLE READ;
set global transaction isolation level READ COMMITTED;
set global transaction isolation level READ UNCOMMITTED;
set global transaction isolation level SERIALIZABLE;

# 相应隔离级别参数加入配置文件（保证重启持久化）
[mysqld]
......
transaction-isolation=Read-Committed
4 建库、建表

（1）建库

CREATE DATABASE test1;
show databases;
use test1;

（2）建表

CREATE TABLE `record_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `station_code` varchar(16) NOT NULL DEFAULT '' COMMENT '编码',
  `unique_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '唯一id',
  `package_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'package_id',
  `call_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'call_id',
  `status` smallint(10) NOT NULL DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_station_code_unique_id` (`station_code`,`unique_id`),
  KEY `idx_station_code_call_id` (`station_code`,`call_id`),
  KEY `idx_station_code_package_id` (`station_code`,`package_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='记录测试表';

show tables;
desc record_test;
show columns from record_test;
5 插入数据

（1）单条插入

mysql>insert into record_test (unique_id,station_code,package_id,call_id) VALUES  (1,"A000003938",1001,2001);

（2）文件批量导入
注意一定要用local，不然配置会比较麻烦，解决了好久没有成功，本地测试使用，就不折腾了

mysql>load data local infile "C:/Users/lily/Desktop/test/sql1.txt" into table record_test fields terminated by"," lines terminated by"\n" (station_code,unique_id,package_id,call_id);

查入后有用数据如下：（共1w条，仅展示测试使用的几条）

mysql>SELECT * FROM record_test where station_code = "A000003938" and call_id in (2001,2002);
+------+--------------+-----------+------------+---------+--------+
| id   | station_code | unique_id | package_id | call_id | status |
+------+--------------+-----------+------------+---------+--------+
| 1028 | A000003938   |         1 |       1001 |    2001 |      1 |
| 1029 | A000003938   |         2 |       1002 |    2001 |      0 |
| 1030 | A000003938   |         3 |       1003 |    2001 |      1 |
| 1031 | A000003938   |         4 |       1003 |    2002 |      1 |
| 2056 | A000003938   |     21000 |       1001 |    2002 |      0 |
+------+--------------+-----------+------------+---------+--------+

开始试验

场景1 联合索引在均为=的情况下的加锁

  • 客户端A

package_id = 1003 and call_id = 2001

mysql> update record_test set status = 1 where station_code = "A000003938" and package_id = 1003 and call_id = 2001;
mysql> select * from performance_schema.data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME                  | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA                |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| INNODB | 2585232842912:1062:2585196756456     |                  8284 |        63 |       50 | hexixi_test   | record_test | NULL           | NULL              | NULL                        |         2585196756456 | TABLE     | IX            | GRANTED     | NULL                     |
| INNODB | 2585232842912:3:19:80:2585196753672  |                  8284 |        63 |       50 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_call_id    |         2585196753672 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 2001, 1030 |
| INNODB | 2585232842912:3:18:163:2585196754016 |                  8284 |        63 |       50 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                     |         2585196754016 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1030                     |
| INNODB | 2585232842912:3:20:75:2585196754360  |                  8284 |        63 |       50 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_package_id |         2585196754360 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 1003, 1030 |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+

分析可得，使用索引【idx_station_code_call_id】和【idx_station_code_package_id】，将【‘A000003938’, 2001】和【‘A000003938’, 1003】的索引加锁，同时也对找到的【id=1030】的唯一记录加锁
结果符合预期，加锁如下：

索引加锁值
idx_station_code_call_id‘A000003938’, 2001
idx_station_code_package_id‘A000003938’, 1003
PRIMARY1030

由于是RC隔离级别，故没有间隙锁；

  • 客户端B
    package_id = 1001 and call_id = 2001，call_id和客户端A的一致，预期只会加package_id 的锁
mysql> update record_test set status = 1 where station_code = "A000003938" and package_id = 1001 and call_id = 2001;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME                  | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA                |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| INNODB | 2585232842080:1062:2585196751480     |                  8285 |        68 |        9 | hexixi_test   | record_test | NULL           | NULL              | NULL                        |         2585196751480 | TABLE     | IX            | GRANTED     | NULL                     |
| INNODB | 2585232842080:3:20:73:2585196748696  |                  8285 |        68 |        9 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_package_id |         2585196748696 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 1001, 1028 |
| INNODB | 2585232842080:3:18:161:2585196749040 |                  8285 |        68 |        9 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                     |         2585196749040 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1028                     |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+

使用索引【idx_station_code_call_id】和【idx_station_code_package_id】，但【‘A000003938’, 2001】已经加锁，故仅将【‘A000003938’, 1001】的索引加锁，同时也对找到的【id=1028】的唯一记录加锁
结果符合预期，加锁如下：

索引加锁值
idx_station_code_package_id‘A000003938’, 1001
PRIMARY1028
  • 客户端C
    package_id = 1003 and call_id = 2002，package_id 和客户端A的一致，预期只会加call_id 相关的锁
mysql> update record_test set status = 1 where station_code = "A000003938" and package_id = 1003 and call_id = 2002;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME                  | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA                |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| INNODB | 2585232843744:1062:2585196761432     |                  8286 |        69 |        9 | hexixi_test   | record_test | NULL           | NULL              | NULL                        |         2585196761432 | TABLE     | IX            | GRANTED     | NULL                     |
| INNODB | 2585232843744:3:19:82:2585196758648  |                  8286 |        69 |        9 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_call_id    |         2585196758648 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 2002, 1031 |
| INNODB | 2585232843744:3:18:164:2585196758992 |                  8286 |        69 |        9 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                     |         2585196758992 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1031                     |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+

结果符合预期，加锁如下：
|–|–|
| idx_station_code_call_id| ‘A000003938’, 2002|
| PRIMARY | 1031|

  • 客户端D
    package_id = 1001 and call_id = 2002，package_id 和客户端B相同，call_id与客户端C相同，预期等待超时。
    结果符合预期，等待超时。

场景2 联合索引在存在in的情况下的加锁

  • 客户端A
    package_id 使用in，且有多值，call_id 使用=，只使用idx_station_code_call_id的索引
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update record_test set status = 3 where station_code = "A000003938" and package_id in (1001,1002,1003) and call_id = 2001;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from performance_schema.data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+--------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME               | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA                |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+--------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| INNODB | 2585232843744:1062:2585196761432     |                  8295 |        69 |       25 | hexixi_test   | record_test | NULL           | NULL              | NULL                     |         2585196761432 | TABLE     | IX            | GRANTED     | NULL                     |
| INNODB | 2585232843744:3:19:78:2585196758648  |                  8295 |        69 |       25 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_call_id |         2585196758648 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 2001, 1028 |
| INNODB | 2585232843744:3:19:79:2585196758648  |                  8295 |        69 |       25 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_call_id |         2585196758648 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 2001, 1029 |
| INNODB | 2585232843744:3:19:80:2585196758648  |                  8295 |        69 |       25 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_call_id |         2585196758648 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 2001, 1030 |
| INNODB | 2585232843744:3:18:161:2585196758992 |                  8295 |        69 |       25 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                  |         2585196758992 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1028                     |
| INNODB | 2585232843744:3:18:162:2585196758992 |                  8295 |        69 |       25 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                  |         2585196758992 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1029                     |
| INNODB | 2585232843744:3:18:163:2585196758992 |                  8295 |        69 |       25 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                  |         2585196758992 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1030                     |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+--------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
索引加锁值
idx_station_code_call_id‘A000003938’, 2001
idx_station_code_call_id‘A000003938’, 2001
idx_station_code_call_id‘A000003938’, 2001
PRIMARY1028
PRIMARY1029
PRIMARY1030
  • 客户端B
    package_id 使用in，但为单值，call_id 使用=
mysql> update record_test set status = 4 where station_code = "A000003938" and package_id in (1003) and call_id = 2002;
Query OK, 1 row affected (23.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from performance_schema.data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME                  | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA                |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
| INNODB | 2585232844576:1062:2585196770520     |                  8296 |        70 |       31 | hexixi_test   | record_test | NULL           | NULL              | NULL                        |         2585196770520 | TABLE     | IX            | GRANTED     | NULL                     |
| INNODB | 2585232844576:3:19:82:2585196763624  |                  8296 |        70 |       31 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_call_id    |         2585196763624 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 2002, 1031 |
| INNODB | 2585232844576:3:18:164:2585196763968 |                  8296 |        70 |       31 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                     |         2585196763968 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1031                     |
| INNODB | 2585232844576:3:20:75:2585196764312  |                  8296 |        70 |       31 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_package_id |         2585196764312 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 1003, 1030 |
| INNODB | 2585232844576:3:20:76:2585196764312  |                  8296 |        70 |       31 | hexixi_test   | record_test | NULL           | NULL              | idx_station_code_package_id |         2585196764312 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A000003938', 1003, 1031 |
| INNODB | 2585232844576:3:18:163:2585196765000 |                  8296 |        70 |       32 | hexixi_test   | record_test | NULL           | NULL              | PRIMARY                     |         2585196765000 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1030                     |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------------------+-----------------------+-----------+---------------+-------------+--------------------------+
索引加锁值
idx_station_code_call_id‘A000003938’, 2002
idx_station_code_package_id‘A000003938’, 1003
idx_station_code_package_id‘A000003938’, 1003
PRIMARY1030
PRIMARY1031

结论：使用in，且是单个，索引还是会merge，但此时因为是in，不确定是那一行，索引会将in索引下所有行都锁住；

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值