事务隔离机制
读未提交(read uncommitted): 可以读取到未提交事务做的修改
事务A:
~#: set session transaction isolation level read uncommitted;
~#: start transaction;
~#: select * from <table_name>;事务B:
~#: start transaction;
~#: insert into <table_name> (name, sex, birthday) values ('老王', 0, '2021-03-04');事务A:
~#: select * from table;
(此时事务B未提交,但是事务A可以查看到事务B新增的数据,产生“脏读”现象)事务B:
~#: rollback;事务A:
~#: select * from table;
(查看到原数据)
读已提交(read committed): 只能读取已提交事务的数据
事务A:
~#: set session transaction isolation level read committed;
~#: start transaction;
~#: select * from table;事务B:
~#: start transaction;
~#: update table set name = '小刘' where id = 1;事务A:
~#: select * from table;
(事务B未提交,此时查不到事务B修改后的数据,解决了“脏读”的问题)事务B:
~#: commit;事务A:
~#: select * from table;
(事务B已提交,事务A未提交;此时事务A可以查到事务B修改后的数据,但此时事务A查询出的数据与事务A第一次查询出的数据不一致,产生“不可重复读”的问题)
可重复读(repeatable read): 同一事务内读取的内容数据不发生改变
事务A:
~#: set session transaction isolation level repeatable read;
~#: start transaction;
~#: select * from table;事务B:
~#: start transaction;
~#: update table set name = '小鸡子' where id = 1;事务A:
~#: select * from table;
(此时事务B未提交,事务A读取不到事务B修改的数据,解决“脏读”问题)事务B:
~#: insert table (name, sex) values ('老张', 1);
~#: select * from table;事务B:
~#: commit;事务A:
~#: select * from table;
(此时事务B已提交,事务A读取不到事务B修改的数据,解决“不可重复读”问题;事务A读取不到事务B新增的数据,产生“幻读”问题);事务A:
~#: commit;
~#: select * from table;
(此时事务A已提交,可以读取到事务B修改和新增的数据)
可串行化(serializable): 事务之间按顺序执行
事务A:
~#: set session transaction isolation level serializable;
~#: start transaction;
~#: select * from table;事务B:
~#: start transaction;
~#: update table set name = '小王' where id = 1;
(此时事务A未提交,事务B的更新操作需要等待,直到事务A提交,事务B才能完成更新,解决“脏读”、“不可重复读”、“幻读”问题)事务A:
~#: commit;事务B:
(更新完成)