6.1 文章目的
在mysql中創(chuàng)建數(shù)據(jù)吧恃，依據(jù)該數(shù)據(jù)虾啦，對mysql各個事務隔離級別進行實驗。
6.2 打開mysql命令行痕寓，關閉自動提交
--查看是否是自動提交 1表示開啟傲醉，0表示關閉
select @@autocommit;
--設置關閉
set autocommit = 0;
6.3 數(shù)據(jù)準備
--創(chuàng)建數(shù)據(jù)庫
create database tran;
--切換數(shù)據(jù)庫
use tran;
--準備數(shù)據(jù)
create table psn(id int primary key,name varchar(10)) engine=innodb;
--插入數(shù)據(jù)
insert into psn values(1,'zhangsan');
insert into psn values(2,'lisi');
insert into psn values(3,'wangwu');
commit;
6.4 臟讀測試
開啟兩個mysql連接
窗口A sql操作:
use tran;
--查看隔離級別
show variables like "%isolation%";
--設置隔離級別
set session transaction isolation level read uncommitted;
--set session transaction isolation level read committed;
--set session transaction isolation level repeatable read;
--set session transaction isolation level seariable;
--開啟事務芙盘，查看數(shù)據(jù)
start transaction;
select * from psn;
窗口B sql操作：
use tran;
start transaction;
select * from psn;
update psn set name='xxx' where id =1;
select * from psn;
窗口A sql操作:
select * from psn;
窗口B sql操作：
rollback;
6.4 不可重復讀測試
窗口A：
--設置隔離級別
--set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
--set session transaction isolation level repeatable read;
--set session transaction isolation level seariable;
start transaction;
select * from psn;
窗口B：
start transaction;
select * from psn;
update psn set name ='zhangsan2' where id = 1;
select * from psn;
窗口A：
select * from psn;
窗口B：
commit;
窗口A：
select * from psn;
6.5 幻讀測試
窗口A：
--設置隔離級別
--set session transaction isolation level read uncommitted;
--set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
--set session transaction isolation level seariable;
start transaction;
select * from psn;
窗口B：
start transaction;
select * from psn;
insert into psn values(4,'sisi');
select * from psn;
窗口A：
select * from psn; --看不到數(shù)據(jù)
insert into psn values(4,'sisi'); --插入報錯
