数据库第十三次作业-事务管理

（一）事物的概念

1.创建一个名为chapter06的数据库，并创建account表，插入数据，查询account表中的数据

create database chaptet06;

 use chaptet06;

 create table account(
    -> id int primary key auto_increment,
    -> name varchar(40),
    -> money float
    -> );

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

mysql> insert into account(name,money) values('b',1000);

 
 2.转账

start transaction;

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

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

mysql> commit;

 select*from account;

 （二）事务的提交

 start transaction;

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

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

 select*from account;

 1.退出数据库然后重新登陆，并查询数据库中各账户的余额信息

 2.使用commit语句来提交事务

 start transaction;

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

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

commit;

3.退出数据库然后重新登陆，并使用select语句查询数据库中各账户的余额信息

（三） 事物的回滚

1.

 start transaction;

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

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

select*from account;

2. 

rollback;

mysql> select*from account;

(四）事务的隔离级别 

1.

 set session transaction isolation level read uncommitted;

 

select @@transaction_isolation; 

 2.演示脏读 

 

 

 start transaction;

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

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

 

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

 set session transaction isolation level read committed;

4.验证是否出现脏读  

 

 start transaction;

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

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

 

 

2. 1）

start transaction;

select*from account;

 

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

 select*from account;

 

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

 

set session transaction isolation level repeatable read;

3）验证是否出现不可重复读 

 

 start transaction;

select*from account;

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

 

 

3  1）设置b账户中事务的隔离级别 

 

 set session transaction isolation level read committed;

2）演示幻读  

 ‘ start transaction;

select*from account;

 

insert into account(name,money) values
    -> ('c',1000);

 

3）重新设置账户的隔离级别  

 

 set session transaction isolation level repeatable read;

4）验证是否出现幻读  

 

 

 

 

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

 

 

4.可串行化  

 

 

1.演示可串行化  

 

 

 

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

 2.提交事务