day05-05-锁和隔离级别
10.2.4 锁
实现了事务之间的隔离功能,InnoDB中 实现的是行级锁.
row-level lock
gap
next-lock 下键锁
10.2.5 隔离级别 (默认是RR)
隔离级别，主要是控制读的隔离性
作用：主要是提供I（隔离性）的特性，另外对于C（一致性）的特性也有保证
查看默认隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.01 sec)
mysql>
select @@transaction_isolation;
这里的“读”是什么意思？
这里的“读”不是SQL层数据行的select，而是存储引擎的读取，是page的读取。言外之意，所有的DML语句都会涉及到page的读取
隔离级别，是指在存储引擎层，对数据页获取的一个动作。
RU （read-uncommitted） : 读未提交,会出现 可脏读,不可重复读，幻读问题
事务未还未提交，在内存的数据页就能被读取到，脏读。
（举例：比如丈夫在取款机只是查询一下余额，结果老婆收到银行短信，账户扣款2000块）
A 事务正在修改 update set id=3， 此时没有commit
B 事务 select t1 查询返回结果，查寻到了A事务还未提交的 ID=3修改数据。
这就是脏读
RC （read-committed） : 读已提交,不可重复读，可能出现幻读,可以防止脏读.
事务必须提交完成，才能读取到已提交结果。当A进程频繁修改提交一条数据行时，B进程获取到的结果总是保持最新。如果一条查询持续时间很长，那么得到结果无法保证是在发起查询时间点的结果，这种现象叫，不可重复读，也会出现幻读。（金融行业不允许这种现象出现）
RR （REPEATABLE-READ） : 可重复读，也叫一致性非锁定读,有可能出现幻读
指的是在同一个事务中，连续几次快照读，读取的记录应该是一样的
可以利用undo的快照技术+GAP(间隙锁)+NextLock(下键锁)，防止"幻读"现象 出现
幻读：在一个事务窗口中，更新操作，出现了别的插入数据的行
RR通过MVCC（多版本并发控制）基本解决了不可重复读，但是有可能会出现幻读现象，可以通过GAP（间隙锁）+next-lock（下键锁）避免
Record lock
单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。
Gap lock
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。gap lock的机制主要是解决可重复读模式下的幻读问题
Next-Key Locks
在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。
所谓Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。
SR （SERIALIZABLE) : 可串行化,可以防止死锁,但是不利于事务并发，并发性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
MVCC（多版本并发控制） ---> undo 快照
select @@transaction_isolation;
临时修改
set transaction_isolation=read-uncommitted
永久修改则是修改my.cnf
[mysqld]
# RU mode:
transaction_isolation = read-uncommitted
# RC mode:
transaction_isolation = read-committed
# RR mode:
transaction_isolation = REPEATABLE-READ
RU 会出现脏读 ,
RC 会出现不可重复读 ,也会出现幻读.
RR 通过MVCC基础解决了不可重复读,但是有可能会出现幻读现象
在RR模式下,GAP和Next-lock进行避免幻读现象,必须索引支持
GAP 和 nextlock 是索引锁，不是行锁
演示一下幻读：
# 两个用户登录同一个mysql，同一个库
root@localhost 05:28:26->select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
root@localhost 05:28:37->use simon_test;
Database changed
root@localhost 05:29:05->
root@localhost 05:29:42->
root@localhost 05:29:43->select @@tx_isolation; #查看隔离级别 为RC模式
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
root@localhost 05:29:51->show tables; #查看表
+----------------------+
| Tables_in_simon_test |
+----------------------+
| course |
| sc |
| student |
| t1 |
| t100w |
| t3 |
| teacher |
+----------------------+
7 rows in set (0.00 sec)
root@localhost 05:29:56->
root@localhost 05:53:00->select * from t3;
+----+------+--------+
| id | name | telnum |
+----+------+--------+
| 1 | aa | 123456 |
| 2 | bb | 654321 |
| 3 | cc | 998877 |
| 4 | dd | 445566 |
| 5 | ee | 223344 |
+----+------+--------+
5 rows in set (0.00 sec)
root@localhost 05:53:04->
第二个用户：
simon@10.0.50.61 05:28:07->select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
simon@10.0.50.61 05:29:23->use simon_test;
Database changed
simon@10.0.50.61 05:29:27->
simon@10.0.50.61 05:29:31->
simon@10.0.50.61 05:29:32->select @@tx_isolation; #查看隔离级别 为RC模式
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
simon@10.0.50.61 05:29:37->show tables; #查看表
+----------------------+
| Tables_in_simon_test |
+----------------------+
| course |
| sc |
| student |
| t1 |
| t100w |
| t3 |
| teacher |
+----------------------+
7 rows in set (0.00 sec)
simon@10.0.50.61 05:30:02->
simon@10.0.50.61 05:52:55->
simon@10.0.50.61 05:52:55->select * from t3;
+----+------+--------+
| id | name | telnum |
+----+------+--------+
| 1 | aa | 123456 |
| 2 | bb | 654321 |
| 3 | cc | 998877 |
| 4 | dd | 445566 |
| 5 | ee | 223344 |
+----+------+--------+
5 rows in set (0.00 sec)
simon@10.0.50.61 05:53:10->
# 当前mysql不是自动提交模式
simon@10.0.50.61 05:59:09->show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
simon@10.0.50.61 05:59:41->
两用户在同一库，同样的表，隔离模式都是RC
接下来开始：
simon 用户，要把t3表中，id > 2的 name，全部改成 xx。也就是说id3，id4，id5 对应的name全部会改成xx
simon@10.0.50.61 05:59:46->update t3 set name='xx' where id>2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
simon@10.0.50.61 06:00:51->
此时，simon用户更新业务还未完成commit提交，也就是更新动作还没结束，正在更新。
恰巧root用户正在插入其他数据，并且完成提交。
root@localhost 06:00:07->
root@localhost 06:00:07->insert into t3 (name,telnum) values('root','44332211');
Query OK, 1 row affected (0.00 sec)
root@localhost 06:01:12->commit;
Query OK, 0 rows affected (0.01 sec)
root@localhost 06:01:15->
接着，假设simon用户更新业务完成，进行commit提交，然后查询修改结果
simon@10.0.50.61 06:02:51->commit;
Query OK, 0 rows affected (0.00 sec)
simon@10.0.50.61 06:03:24->select * from t3 where id > 2;
+----+------+----------+
| id | name | telnum |
+----+------+----------+
| 3 | xx | 998877 |
| 4 | xx | 445566 |
| 5 | xx | 223344 |
| 6 | root | 44332211 |
+----+------+----------+
4 rows in set (0.00 sec)
simon@10.0.50.61 06:03:32->
查询结果时发现，id3-5对应的name列已全部修改为xx，但刚才root用户新增id6 没有修改。
这样的情况，就是幻影读。
还有一种情况：
simon用户更新数据，未进行commit提交
simon@10.0.50.61 06:43:04->select * from t3;
+----+------+----------+
| id | name | telnum |
+----+------+----------+
| 1 | aa | 123456 |
| 2 | bb | 654321 |
| 3 | xx | 998877 |
| 4 | xx | 445566 |
| 5 | xx | 223344 |
| 6 | root | 44332211 |
| 9 | hh | 001100 |
| 10 | ss | 002200 |
+----+------+----------+
8 rows in set (0.00 sec)
simon@10.0.50.61 06:45:47->update t3 set name='gg' where id>3;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
simon@10.0.50.61 06:46:00->
此时，root用户插入数据，并且提交成功。
root@localhost 06:46:40->insert into t3 values(7,'ww','1100'),(8,'oo','1001');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost 06:47:09->commit;
Query OK, 0 rows affected (0.00 sec)
root@localhost 06:47:11->
这时候，simon用户完成commit提交，然后并查看更新结果。
simon@10.0.50.61 06:47:17->select * from t3;
+----+------+----------+
| id | name | telnum |
+----+------+----------+
| 1 | aa | 123456 |
| 2 | bb | 654321 |
| 3 | xx | 998877 |
| 4 | gg | 445566 |
| 5 | gg | 223344 |
| 6 | gg | 44332211 |
| 7 | ww | 1100 |
| 8 | oo | 1001 |
| 9 | gg | 001100 |
| 10 | gg | 002200 |
+----+------+----------+
10 rows in set (0.00 sec)
simon@10.0.50.61 06:47:22->
查看结果发现，root新增两条id=7，id=8的数据，name没有被更新为“gg“，这现象就是幻影读
上述实例发现，幻读最有可能发生数据插入时操作
解决方法：
首先不允许在id>10的数据行插入，这就需要用NextLock下键锁防止插入。
接着不允许在id>6 and id <9之间，不允许插入，这就需要用GAP间隙锁防止插入。
使用这两种锁，需要在更新列上建立索引
隔离模式使用RR（可重复读）+GAP（间隙锁）+NextLock（下键锁）
接下来演示一下 RR（可重复读）+GAP（间隙锁）+NextLock（下键锁）
root@localhost 07:16:38->select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
root@localhost 07:16:46->select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
root@localhost 07:16:53->
root@localhost 07:17:33->show tables;
+----------------------+
| Tables_in_simon_test |
+----------------------+
| course |
| sc |
| student |
| t1 |
| t100w |
| t3 |
| t4 |
| teacher |
+----------------------+
8 rows in set (0.00 sec)
root@localhost 07:17:43->show create table t4\G
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@localhost 07:17:54->
simon@10.0.50.61 07:13:15->select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
simon@10.0.50.61 07:18:37->select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
simon@10.0.50.61 07:18:45->show tables;
+----------------------+
| Tables_in_simon_test |
+----------------------+
| course |
| sc |
| student |
| t1 |
| t100w |
| t3 |
| t4 |
| teacher |
+----------------------+
8 rows in set (0.00 sec)
simon@10.0.50.61 07:18:51->show create table t4\G
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
simon@10.0.50.61 07:18:58->
root@localhost 07:21:20->insert into t4 values(1,'a'),(2,'b'),(3,'c'),(7,'x'),(10,'y');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
root@localhost 07:23:12->
root@localhost 07:23:21->select * from t4;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 7 | x |
| 10 | y |
+------+------+
5 rows in set (0.00 sec)
root@localhost 07:23:27->alter table t4 add index idx_id(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost 07:24:33->
simon@10.0.50.61 07:25:16->select * from t4;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 7 | x |
| 10 | y |
+------+------+
5 rows in set (0.01 sec)
simon@10.0.50.61 07:25:19->
两侧用户确认数据一致，simon用户进行数据更新，未进行commit提交
simon@10.0.50.61 07:26:14->update t4 set name='simon' where id>2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
simon@10.0.50.61 07:26:49->
此时，root用户再次进行数据行（id=5,id=6）插入时，就夯在这里不动。
这里就是GAP间隙锁在产生作用，锁定id3-7的数据行区间，不允许插入数据行。
root@localhost 07:25:34->insert into t4 values(5,'t'),(6,'q');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost 07:29:38->
#直到报错，锁等待超时
# 那么插入 8-9 区间，也不能插入
root@localhost 07:29:38->insert into t4 values(8,'t'),(9,'q');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost 07:33:20->
root@localhost 07:33:20->insert into t4 values(11,'t'),(12,'q');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost 07:35:20->
# 插入id>10的， 11，12 也不能插入
# 这是NextLock锁在进行下键范围锁定
# 继续插入 13，14
root@localhost 07:35:20->insert into t4 values(13,'t')(14,'q');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost 07:37:31->
# 还是锁等待超时。
此时，simon用户完成commit提交操作。
simon@10.0.50.61 07:38:40->
simon@10.0.50.61 07:38:41->
simon@10.0.50.61 07:38:42->commit;
Query OK, 0 rows affected (0.00 sec)
simon@10.0.50.61 07:38:43->
simon@10.0.50.61 07:38:45->
simon@10.0.50.61 07:38:45->select * from t4;
+------+-------+
| id | name |
+------+-------+
| 1 | a |
| 2 | b |
| 3 | simon |
| 7 | simon |
| 10 | simon |
+------+-------+
5 rows in set (0.00 sec)
simon@10.0.50.61 07:39:06->
root用户才能进行正常的插入操作。
root@localhost 07:39:34->insert into t4 values(13,'t'),(14,'q');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost 07:39:44->
浙公网安备 33010602011771号