数据库第十三次作业 事务管理【带源码】

目录

​​一、新建数据库​​

​​二、事务的提交​​

​​ 1.事务的隔离级别​​

​​1.1脏读 ​​

​​1.2演示脏读 ​​

​​1.3设置b账户中事务的隔离级别 ​​

​​1.4.验证是否出现脏读 ​​

​​2.不可重复性 ​​

​​2.1演示不可重复性 ​​

​​2.2设置b账户中事务的隔离级别 ​​

​​2.3验证是否出现不可重复读 ​​

​​3.幻读 ​​

​​ 3.1设置b账户中事务的隔离级别 ​​

​​3.2演示幻读 ​​

​​3.3重新设置账户的隔离级别 ​​

​​3.4验证是否出现幻读 ​​

​​4.可串行化 ​​

​​4.1设置b账户中事务的隔离级别  ​​

​​4.2演示可串行化 ​​

​​4.3提交事务 ​​

一、新建数据库

create database chapter06;

use chapter06;

create table account(

    -> id int primary key auto_increment,

    -> name varchar(40),

    -> money float

-> );

insert into account(name,money) values

    -> ('a',1000),

-> ('b',1000);

二、事务的提交

start transaction;

update account set money=money-100 where name='a';

-> update account set money=money+100 where name='b';

commit;/

update account set money=money+100 where name='a';

-> update account set money=money-100 where name='b';

select * from account;

update account set money=money+100 where name='a';

    -> update account set money=money-100 where name='b';

-> commit/

start transaction/

update account set money=money-100 where name='a';

-> update account set money=money+100 where name='b';

rollback;/

 1.事务的隔离级别

1.1脏读 

set session transaction isolation level read uncommitted;

select @@transaction_isolation;

1.2演示脏读 

start transaction;

    -> update account set money=money-100 where name='a';

    -> update account set money=money+100 where name='b';

-> /

1.3设置b账户中事务的隔离级别 

set session transaction isolation level read committed;

1.4.验证是否出现脏读 

start transaction;

    -> update account set money=money-100 where name='a';

    -> update account set money=money+100 where name='b';

-> /

2.不可重复性 

2.1演示不可重复性 

start transaction;

select * from account;

update account set money=money-100 where name='a';

2.2设置b账户中事务的隔离级别 

set session transaction isolation level repeatable read;

2.3验证是否出现不可重复读 

update account set money=money-100 where name='a';/

3.幻读 

 3.1设置b账户中事务的隔离级别 

set session transaction isolation level read committed;

3.2演示幻读 

insert into account(name,money) values

-> ('c',1000);

3.3重新设置账户的隔离级别 

set session transaction isolation level repeatable read;

3.4验证是否出现幻读 

insert into account(name,money) values('d',1000);

4.可串行化 

4.1设置b账户中事务的隔离级别  

set session transaction isolation level serializable;

4.2演示可串行化 

insert into account(name,money) values('e',1000);

4.3提交事务