serializable
实现方式:
2阶段加锁:读加锁,写加锁
这种加锁会导致 mvcc 失效,因为 select * from xx也会加锁,select * from xx for update
RR
会话1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from a; +------+ | a | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
会话2:
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into a select 3; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.04 sec)
会话1:
mysql> select * from a; +------+ | a | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> select * from a for update; +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
serializable级别:
会话1:
mysql> begin; //事务未开始 Query OK, 0 rows affected (0.00 sec)
会话2:
mysql> begin; Query OK, 0 rows affected (0.05 sec) mysql> select * from a; //事务开始 //非一致性查询
会话1:
insert into a select 10; 等待事务2执行完毕 会话1事务才开始,第一条SQL语句,串行执行,非一致性查询也阻塞了会话1,串行级别 MVCC失效