You are here
马哥 34_02 _MySQL系列之十——MySQL事务和隔离级别 有大用
多事务同时执行,会并发,效率高,(事务会进行cpu和IO操作,IO操作时,cpu空闲,cpu可干其它的事,执行别的事务)
多事务同时执行:彼此之间互相不影响的方式进行并发
事务之间交互
通过数据集
ACID:
原子性（atomicity);事务所引起的数据库操作,要么都完成,要么都不执行
一致性（consistency);A转钱给B,他们的总钱是一致的,A(3000)500给B(2000)要在隔离状态下进行
1):A:3000-->2500(转出500,B未来得及加)
2):A+B:2500+2000=4500,,数据就不对了
隔离性（isolation );
事务调度:事务之间影响最小
MVCC:多版本并发控制(基于快照,时间戳来实现)
持久性（durability）; 一旦事务成功完成,系统必须保证任何故障都不会引起事务表示出不一致性
1)事务提交之前每一个sql语句就己经写出数据至持久性存储中,撤销时,也是从持久设备上撤销(涉及大量IO操作),它不是连续存储,是随机io,速度非常慢 数据文件是随机IO 应该是不采用这种方式
2)结合事务日志提交,事务日志虽然也在磁盘上,但是事务日志管理便捷性大于数据文件 应该是采用这种方式
事务日志:顺序IO,连续写到磁盘块上,速度快 (先写到日志里面,后台进程再通过别的机制将事务日志中已经提交的数据同步到数据文件中去)
事务的状态:
1)活动的事务: active
2)部分提交的: 部分写到数据磁盘上,部分正在执行;最后一条语句执行后?正在写的过程当中?
3)失败的: 事务正常提交了,但是未能完成
4)终止的: 没提交,执行了一半就提前结束了
5)提交的: 提交的,完成了,而且成功了
某事务 只能在5种状态的一种
事务一旦提交,就无法再撤销,要想回到以前的状态,只能通过补偿事务来完成
事务:并发执行
1,提高吞吐量和资源利用率
2,减少等待时间
事务调度:
可恢复调度:两个事务在彼此间调度的时候,任何两个事务的交叉执行,都不会导致另外一个事务的状态改变,,,回滚的时候,可能对另外一个事务有影响
无级联调度: 为了防止避免回滚的时候有影响,可以使用无级联调度
隔离级别
READ UNCOMMITTED: # 应该是读别人未提交
READ COMMITTED: # 大多数的rbms默认级别 应该是读别人已提交
REPEATABLE READ: # mysql 默认级别 # 别人未提交的,已提交的都读不到,只有自己在事务提交后才看到
SERIALIZABLE:
并发控制依赖的技术手段:
锁
时间戳:必须要记录每个事务的启动时间或执行时间
多版本和快照隔离
饿死:锁饥饿(想得到锁,但是一直得不到锁)
死锁:提前检查死锁,并且使得其中一个事务(操作)回退,以释放其所持有的锁,使得另外一个事务可以正常执行,,,至少在这种场景下,至少保证一个事务的正常执行
能够启动事务的:SQL语句,ODBC启动事务的指令
启动事务的命令语句:
start transaction # 启动事务 或者 begin
SQL语句
SQL语句
commit:提交
rollback:回滚,撤销
如果没有明确启动事务:
autocommit
autocommit: 能实现自动提交,每一个操作都直接提交了
mysql为了提高系统的性能,io操作越少越好,mysql为了保证事务的安全性持久性,每一次提交,都会产生一次io操作
没有使用 begin transaction,意味着,每一个语句都会提交一次,大量io操作,性能非常低
建议:在事务式引擎上,明确使用事务,并且关闭自动提交(mysql服务器优化的一种策略)
在autocommit为0的情况下,如果不明确使用事务,所有的sql语句都被看作一个事务
保存点: 事务保存点 ( 事务100个操作,执行到80个时,发现第75个操作错了), 我们可以每10个语句保存一次,将来撤的时候,可以撤回到指定的保存点的
保存点: savepoint sid # sid就是savepoint id
回滚至保存点: rollback to sid
下图是事务中的状态转换
来一次事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 15 | stu1 | F | 23 |
| 16 | stu2 | F | 23 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
17 rows in set (0.00 sec)
mysql> delete from tutors where tname like 'stu%';
Query OK, 2 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; # 没了stu开头的
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql> rollback; #回滚事务 回滚其实也是事务的结束
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;# stu开头的又都在了
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 15 | stu1 | F | 23 |
| 16 | stu2 | F | 23 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
17 rows in set (0.00 sec)
mysql>
再来一次事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors;
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 15 | stu1 | F | 23 |
| 16 | stu2 | F | 23 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
17 rows in set (0.00 sec)
mysql> delete from tutors where tname like 'stu%'; #删除 stu 开头的学生
Query OK, 2 rows affected (0.00 sec)
mysql>
mysql> commit; #提交,,此时事务结束了,,,,事务持久化了
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tutors; #stu开头的没了
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql>
mysql> rollback ; #此时(commit 后)回滚不起作用了
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #此时,永久性看不到stu开头的老师了
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LiHuchong | M | 22 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql>
mysql> select @@autocommit; #自动提交
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql>
mysql> delete from tutors where tname='Xuzhu';
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from tutors; # Xuzhu 这一行没有了
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 14 | LiHuchong | M | 22 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
mysql>
mysql> set autocommit=0; #关闭自动提交
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql>
mysql> delete from tutors where tname='HuFei';
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> rollback; #(此时没提交)可以回滚的
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors; #可看到 HuFei
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 14 | LiHuchong | M | 22 |
| 18 | jerry | M | 50 |
+-----+--------------+--------+------+
14 rows in set (0.01 sec)
mysql>
mysql> help savepoint
Name: 'SAVEPOINT'
Description:
Syntax:
SAVEPOINT identifier #定义保存点
ROLLBACK [WORK] TO [SAVEPOINT] identifier #回滚到某保存点
RELEASE SAVEPOINT identifier #释放掉某保存点
InnoDB supports the SQL statements SAVEPOINT, ROLLBACK TO SAVEPOINT,
RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK.
URL: http://dev.mysql.com/doc/refman/5.5/en/savepoint.html
mysql>
mysql> start transaction; # 显式启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delete from tutors where tid=18;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint ab; # 设置保存点
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delete from tutors where tid=14;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint ac; #再一次设置保存点
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delete from tutors where tid=12;
Query OK, 1 row affected (0.01 sec)
mysql> savepoint ad; #再再一次设置保存点
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #删掉的3个都没了
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
+-----+--------------+--------+------+
11 rows in set (0.00 sec)
mysql>
mysql> rollback to ac; #回滚保存点 回到保存点时的状态
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors; # tid=12的恢复了
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
# 1) 开第一个putty 看事务之间的影响
mysql> set tx_isolation='READ-UNCOMMITTED'; 事务隔离级别设为 read uncommitted
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> start transaction; #交叉启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors;
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql> update tutors set age=50 where tid=12; #把 tid=12的年龄改为50
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tutors; # tid=12的年龄变成了50
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 50 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> rollback; #这边回滚了
Query OK, 0 rows affected (0.01 sec)
mysql>
1) 开第二个putty
mysql> select @@tx_isolation; #事务隔离级别也设为 read uncommitted
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> start transaction; #交叉启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; # tid=12的年龄,此时也变成了50,因为它是读未提交的级别
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 50 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> select * from tutors; #第一个putty回滚之后,这边看到的是 tid=12 的年龄是最初值 31 ;;;自己这边未做任何操作,数据变化,所以说是幻影读
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> commit; #这边提交吧
Query OK, 0 rows affected (0.00 sec)
mysql>
2) 第一个putty
mysql> SET tx_isolation='READ-COMMITTED'; #改事实级别为读提交
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #tid=12 年龄为31
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> update tutors set age=50 where tid=12; # tid=12改为50
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from tutors; # tid=12 年龄变成50
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 50 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> commit; #提交了
Query OK, 0 rows affected (0.00 sec)
mysql>
2) 第二个putty
mysql> set tx_isolation='READ-COMMITTED'; # 改事务隔离级别也为 读提交
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; # 此时tid=12,值为31,,,由于级别是读提交,所以,别人提交时,才读到新值50
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> select * from tutors; # 别人提交后,我们读到了tid=12的年龄新值为 50 # 两次select 值不一样,仍然会产生幻影问题
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 50 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
3) 第一个putty
mysql> set tx_isolation='REPEATABLE-READ'; #事务隔离级别设为 REPEATABLE-READ
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start transaction; #启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> update tutors set age=80 where tid=12; # tid=12的年龄改为 80
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from tutors; #tid=12的年龄变成了80
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 80 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
3) 第二个putty
mysql> set tx_isolation='REPEATABLE-READ'; #事务隔离级别也设为 REPEATABLE-READ
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start transaction; #也启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #tid=12 年龄仍为50,因为它 ( REPEATABLE-READ )比 read-commit 级别更高,
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 50 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> commit; # 提交,数据变永久了 tid=12 的年龄为80
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors; #显示 tid=12 的年龄为80
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 80 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> select * from tutors; #为什么年龄变成了80,应该是50的啊,因为级别是 REPEATABLE-READ
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 80 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
重新来一遍 REPEATABLE-READ ,因为上面的 REPEATABLE-READ 有问题
4) 第一个putty
mysql> commit; #先提交
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select @@tx_isolation; # 级别为可重读
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> start transaction; #开启事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; # tid=12,年龄原值是80
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 80 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> update tutors set age=60 where tid=12; #把tid=12的年龄改为60
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tutors; #年龄变成了60
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 60 |
+-----+--------------+--------+------+
12 rows in set (0.01 sec)
mysql>
mysql> commit; #提交
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tutors; # tid=12时,年龄持久化成60
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 60 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
4) 第二个putty
mysql> commit; #也先提交
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select @@tx_isolation; #级别为可重读
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> start transaction; #也开启事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #第一个putty提交前,tid=12时,年龄是80,是原值
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 80 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> select * from tutors; 第一个putty提交后,tid=12时,年龄是80,也是原值, 因为级别 是 REPEATABLE-READ
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 80 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
mysql> commit; # 我这边第二个putty自己提交事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #此时才看到更改后的值 tid=12的年龄为60
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 60 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
5)第一个putty
mysql> set tx_isolation='SERIALIZABLE'; #级别为串行
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start transaction; #启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> update tutors set age=70 where tid=12; # 把tid=12的年龄改成70
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; #提交事务
Query OK, 0 rows affected (0.00 sec)
mysql>
5)第二个putty
mysql> set tx_isolation='SERIALIZABLE'; #级别同样为串行
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start transaction; #也启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #我第二个putty停在那里不动了
#只有当第一个putty提交后,看到了最终值为70
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 70 |
+-----+--------------+--------+------+
12 rows in set (39.99 sec)
我的 SERIALIZABLE 与马哥的效果不一样
马哥的 SERIALIZABLE 效果与 repeatable-read 效果是一样的
我的SERIALIZABLE 效果 是第二个putty select 的时候停在那里不动了
马哥说 SERIALIZABLE 两个putty.修改同一个数据,可能效果更明显
马哥在 两个putty修改同一个数据时,一个putty,必须另一个putty 提交后才能进行,否则就停在那里不动了
REPEATABLE-READ 级别表示自己的事务提交前看到的是同一个值,自己的事务提交后看到的是最终值
可重读仍然会产生幻影的问题,自己的事务提交前看到的与提交后看到的可能不一样
SERIALIZABLE:串行的,可以开启两个事务,但必须要取得最终结果,另一个事务才看到改变的值
马哥再执行一遍 SERIALIZABLE
6)第一个putty
mysql> start transaction; #启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> update tutors set age=25 where tid=12; #tid=12 年龄改为25
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from tutors; #自己看,年龄是25
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 25 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)
mysql>
6)第二个putty
mysql> start transaction; #也启动事务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; # 第二个putty停在那里不动了 (这次马哥效果与我一样)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> start transaction; #再启动事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors; # # 仍然第二个putty停在那里不动了
# 第二个putty停在那里不动了 (这次马哥效果与我一样) #看不到效果,因为第一个putty改了之后,没有提交,只有第一个putty提交的时候,才可以看到效果
涉及到第一个putty修改的内容,只有第一个putty提交后才可以看到
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 有点类似于排他锁,为了避免死锁,这是mysql内在的死锁检查机制
重新启动事务,仍然第二个putty停在那里不动了 只有第一个putty提交以后(或者回滚撤销)才能够看到
(这一次马哥演示SERIALIZABLE 是正常的情况,而马哥上次演示 SERIALIZABLE 的时候,可能有点问题)
串行化后事务之间的隔离效果特别好,但是并发太不行了;;;事务隔离级别越高,安全性越好,但是并发能力越差,,
对安全性要求不高,比如不是银行,证券,可以尽可能降低事务隔离级别,,,降低级别,性能提高很明显