MVCC之最佳实践-亲试ok
-
求证 Mysql中普通的select语句会生成 锁 吗？
---session1
创建示例库
mysql> create database myown;
Query OK, 1 row affected (0.09 sec)
mysql> use myown;
Database changed
mysql> show tables;
Empty set (0.00 sec)
#创建示例表
mysql> create table tab01(id int,n1 char(10),n2 time);
Query OK, 0 rows affected (0.29 sec)
#插入示例数据
mysql> insert into tab01 values(1,'aaa',now());
Query OK, 1 row affected (0.04 sec)
---session1
#开启一个事务
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
---session2
mysql> alter table tab01 add index idx_id01(id);
hang住
---session3
mysql> select * from tab01;
hang住
#检查目前数据库中被阻塞的操作
mysql> show processlist;
+----+------+-----------+-------+---------+------+---------------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+---------------------------------+------------------------------------------+
| 2 | root | localhost | myown | Query | 0 | starting | show processlist |
| 3 | root | localhost | myown | Query | 278 | Waiting for table metadata lock | alter table tab01 add index idx_id01(id) |
| 4 | root | localhost | myown | Query | 272 | Waiting for table metadata lock | select * from tab01 |
+----+------+-----------+-------+---------+------+---------------------------------+------------------------------------------+
id=3在 请求对表对象的一个metadata锁（产生锁的操作是 alter table tab01 add index idx_id01(id) ）
id=4在请求对表对象的一个metadata锁（产生锁的操作是 select * from tab01 ）
---session1
结束事务
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+----------+------------------+
| 2 | root | localhost | myown | Query | 0 | starting | show processlist |
| 3 | root | localhost | myown | Sleep | 885 | | NULL |
| 4 | root | localhost | myown | Sleep | 879 | | NULL |
+----+------+-----------+-------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
哈哈 锁解除
情况2
---session1
#手动开启一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#执行select操作
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 04:29:39 |
+------+------+----------+
---session2
mysql> mysql> alter table tab01 add index idx_id02(id), algorithm=inplace,lock=none;
hang住
--session3
mysql> mysql> select * from tab01;
hang住
mysql> show processlist;
+----+------+-----------+-------+---------+------+---------------------------------+-----------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+---------------------------------+-----------------------------------------------------------------------+
| 2 | root | localhost | myown | Query | 0 | starting | show processlist |
| 3 | root | localhost | myown | Query | 17 | Waiting for table metadata lock | alter table tab01 add index idx_id02(id), algorithm=inplace,lock=none |
| 4 | root | localhost | myown | Query | 11 | Waiting for table metadata lock | select * from tab01 |
+----+------+-----------+-------+---------+------+---------------------------------+-----------------------------------------------------------------------+
情况3
#手动开启一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#确认当前数据
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 22:12:01 |
+------+------+----------+
1 row in set (0.00 sec)
#修改数据id=1的n1为AAA
mysql> update tab01 set n1='AAA' where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#确认数据
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | AAA | 22:12:01 |
+------+------+----------+
1 row in set (0.00 sec)
哈哈 数据已变更
---session2
#手动开启一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 22:12:01 |
+------+------+----------+
1 row in set (0.00 sec)
奇怪，一条数据居然有两个值
这就是所谓的MVCC，多版本并发控制
---session1
mysql> rollback;
Query OK, 0 rows affected (0.27 sec)
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 22:12:01 |
+------+------+----------+
1 row in set (0.00 sec)
---session2
mysql> select * from tab01;
+------+------+----------+
| id | n1 | n2 |
+------+------+----------+
| 1 | aaa | 22:12:01 |
+------+------+----------+
会话1 结束事务后 两个session看到的数据恢复一致。哈哈
创建时间：2021-12-22 10:57
넶浏览量：0
