行锁场景
一个 session 开启事务更新不提交，另外一个session 更新同一行记录会阻塞，更新不同记录不会阻塞。
下图是我两个客户端开启事务，另外事务等待的一个过程。
总结：
MyISAM 在执行查询语句 SELECT 之前，会自动给涉及到的所有表加读锁，在执行 update 、insert 、delete 操作会自动给涉及到的表加写锁。
InnoDB 在执行查询语句 SELECT 时，因为有 MVCC 机制不会加锁。但是 update、insert 、delete 操作会加行锁。
总之，读锁阻塞写，但是不会阻塞读， 而写锁则会吧读和写都阻塞。
行锁和事务隔离级别分析
-- 创建表 SQL create table `account` ( `id` int(11) not null auto_increment, `name` varchar(20) default null, `balance` int(11) default null, primary key(`id`) ) engine = InnoDB default charset = utf8; -- 插入数据 insert into `account` (`id`, `name`, `balance`) values ('1' , 'zhangsan', 100); insert into `account` (`id`, `name`, `balance`) values ('2' , 'lisi', 200); insert into `account` (`id`, `name`, `balance`) values ('3' , 'wangwu', 300); insert into `account` (`id`, `name`, `balance`) values ('4' , 'zhaoliu', 400);
读未提交
- 打开一个客户端 A， 并且设置当前事务模式为 read uncommitted (未提交读)， 查询表 account 的初始值：
--客户端 A mysql> set tx_isolation ='read-uncommitted'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 120 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | zhaoliu | 400 | +----+----------+---------+ 4 rows in set (0.00 sec)
- 在客户端 A 的事务提交以前，打开另外一个客户端 B , 对张三的余额进行修改。
-- 客户端 B mysql> set tx_isolation ='read-uncommitted'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 100 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | zhaoliu | 400 | +----+----------+---------+ 4 rows in set (0.00 sec)
- 这时候， 虽然客户端 B 没有提交，但是客户端 A 已经可以查询到 B 未提交的数据此现象就是发生了
脏读
-- 客户端 A mysql> set tx_isolation ='read-uncommitted'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 120 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | zhaoliu | 400 | +----+----------+---------+ 4 rows in set (0.00 sec) mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 100 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | zhaoliu | 400 | +----+----------+---------+ 4 rows in set (0.00 sec)
- 一但，客户端的事务因为某种原因可能会滚，所有的操作都会被测小，那么客户端 A查询到的数据就是脏数据
-- 客户端 B mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 120 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | zhaoliu | 400 | +----+----------+---------+ 4 rows in set (0.00 sec)
- 在客户端 A执行更新语句
update account set balance = balance - 50 where id =1;id = 1 的数据变成了，70 , 不是。50， 之数据不一致， 在应用程序中如果我们用。100 - 50 = 50。 如果不知道其他的程序回滚，想要解决这个问题可以采用读已提交的隔离级别
-- 客户端A mysql> update account set balance = balance - 50 where id =1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 70 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | zhaoliu | 400 | +----+----------+---------+ 4 rows in set (0.00 sec)
读已提交
- 客户端 A 查询 account 表，然后进行数据修改提交。
set tx_isolation ='read-committed';
-- 客户端 A mysql> set tx_isolation ='read-committed'; Query OK, 0 rows affected, 1 warning (0.00 sec) -- 开启事务 mysql> begin; Query OK, 0 rows affected (0.00 sec) -- 更新 id = 1 的账户余额为 700 mysql> update account set balance = 700 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- 事务提交 mysql> commit; Query OK, 0 rows affected (0.00 sec)
- 客户端 B 先查询 A 客户端提交前后的数据，事务内部相同查询语句在不同的时刻读取出来的结果不一致，不符合隔离性。出现了
不可重复读的问题
set tx_isolation ='read-committed';
-- 客户端 B -- 开启事务 mysql> begin; Query OK, 0 rows affected (0.00 sec) -- 客户端 A 提交之前查询 mysql> select * from account; +----+-----------+---------+ | id | name | balance | +----+-----------+---------+ | 1 | zhangsan | 70 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | huayi | 400 | | 6 | wangmazi | 600 | | 8 | zhaoer | 800 | | 9 | wangmazi9 | 600 | +----+-----------+---------+ 7 rows in set (0.00 sec) -- 客户端 A 提交后查询 mysql> select * from account; +----+-----------+---------+ | id | name | balance | +----+-----------+---------+ | 1 | zhangsan | 700 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | huayi | 400 | | 6 | wangmazi | 600 | | 8 | zhaoer | 800 | | 9 | wangmazi9 | 600 | +----+-----------+---------+ 7 rows in set (0.00 sec)
可重复读
- 客户端 A 开启事务，客户端 B 也是开启事务，并且插入一条数据
-- 客户端 B set tx_isolation = 'REPEATABLE-READ'; mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 70 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | huayi | 400 | | 6 | wangmazi | 600 | | 8 | zhaoer | 800 | +----+----------+---------+ 6 rows in set (0.00 sec)
- 客户端 A 对 B 新增的数据进行修改，然后在查询就可以查询到了。所以存在
幻读, 违背了隔离性。
-- 客户端 A -- 客户端提交后查询 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 70 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | huayi | 400 | | 6 | wangmazi | 600 | | 8 | zhaoer | 800 | +----+----------+---------+ -- 更新数据 9 mysql> update account set balance = 9000 where id = 9; Query OK, 1 row affected (7.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- 重新查询可见， 违背隔离性 mysql> select * from account; +----+-----------+---------+ | id | name | balance | +----+-----------+---------+ | 1 | zhangsan | 70 | | 2 | lisi | 200 | | 3 | wangwu | 300 | | 4 | huayi | 400 | | 6 | wangmazi | 600 | | 8 | zhaoer | 800 | | 9 | wangmazi9 | 9000 | +----+-----------+---------+ 7 rows in set (0.00 sec)
可重复读是 MySQL 的默认隔离级别
