1.以默认方式启动事务1,修改事务隔离级别,查看transaction_isolation
START TRANSACTION;
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
show transaction_isolation;
2.以读写方式启动事务2,创建新表,修改事务为只读事务,查看transaction_read_only,并向表中插入记录
show transaction_isolation;
START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
create table test(c1 char(10));
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
show transaction_read_only;
insert into test values ('job');
3.启动事务3,对表进行增删改查,并用到创建savepoint,回滚savepoint和删除savepoint
show transaction_isolation;
CREATE TABLE t1(a int,b char(10));
START TRANSACTION;
INSERT INTO t1 VALUES (1,'job');
INSERT INTO t1 VALUES (2,'lizhang');
SAVEPOINT my_savepoint;
delete from t1 where a=1;
ROLLBACK TO SAVEPOINT my_savepoint;
update t1 set b='wang' where a=1;
RELEASE SAVEPOINT my_savepoint;
update t1 set b='wu' where a=2;
COMMIT;
SELECT * FROM t1;
4.清理数据
truncate table t1;
drop table t1;