1.5 SQL基础 - 数据库事务

1.5 SQL基础 - TCL 语言 - 数据库事务

TCL 语言 事务控制语言（Transaction control language）

数据库事务

事务
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行；
事务
事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一
旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

案例
转账记录要么全部成功、要么全部失败

转账
张三丰	1000
郭襄	1000

update 表 set 张三丰的余额=500 where name ='张三丰'；
update 表 set 郭襄的余额=1500 where name ='郭襄'；

存储引擎

1、概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
2、通过show engines；来查看mysql支持的存储引擎。
3、 在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务

含义

通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态

特点

事务的ACID(acid)属性

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
   案例中执行转账之前，两个人的余额之和是2000，执行转账之后，余额之和还是2000；

3. 隔离性（Isolation）
   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

   原子性：要么都执行，要么都回滚
   一致性：保证数据的状态操作前和操作后保持一致
   隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
   持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

事务的分类

• 隐式事务，没有明显的开启和结束事务的标志
  比如 insert、update、delete语句本身就是一个事务

show variables like 'autocommit';

• 显式事务，具有明显的开启和结束事务的标志
  前提：先设置自动提交功能为禁用

set autocommit=0;    # 只针对当前事务有效
show variables like 'autocommit';

步骤

相关步骤：

1、开启事务
2、编写事务的一组逻辑操作单元（多条sql语句）
3、提交事务或回滚事务

1、开启事务，取消自动提交事务的功能

		set autocommit=0;
		start transaction;   #可选的

2、编写事务的一组逻辑操作单元（多条sql语句）(select、insert、update、delete)
语句1；
语句2；
3、提交事务或回滚事务

		commit；  #  提交事务

		rollback;  #回滚事务

使用到的关键字

set autocommit=0;
start transaction;
commit;
rollback;

savepoint  节点名，设置保存点
commit to 断点
rollback to 断点

事务的隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题：

• 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.
  之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

• 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.
  之后, T1再次读取同一个字段, 值就不同了.

• 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插
  入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

    数据库事务的隔离性:
     数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题. 
     一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱
  

事务并发问题如何发生？

当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些？

脏读：一个事务读取到了另外一个事务未提交的数据
不可重复读：同一个事务中，多次读取到的数据不一致
幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据

如何避免事务的并发问题？

通过设置事务的隔离级别
					脏读		幻读			不可重复读
READ UNCOMMITTED  	 √			√					√
READ COMMITTED   	 ×			√					√
REPEATABLE READ   	 ×			×					√
SERIALIZABLE		 ×  		×			        ×	

muysql 中默认第三个隔离级别：  REPEATABLE READ
Oracle 中默认第二个隔离级别：READ COMMITTED

查看隔离级别

select @@transaction_isolation;

设置隔离级别：

global 设置数据库全局的隔离级别；

set session|global  transaction isolation level 隔离级别名;   

set session|global  transaction isolation level READ UNCOMMITTED;
set session|global  transaction isolation level READ COMMITTED;
set session|global  transaction isolation level REPEATABLE READ;
set session|global  transaction isolation level SERIALIZABLE;

案例1、事务

drop table if exists account ;
create table account(
		id int primary key auto_increment,
		username varchar(20),
		balance double
);
insert into account(username,balance ) values('张无忌',1000),('赵敏',1000);
select * from account;

提交事务

# 开启事务
set autocommit=0;
		# start transaction;  #可选
# 编写一组事务的语句
update account set balance =500 where username ='张无忌';
update account set balance =1500 where username ='赵敏';
# 提交事务
commit;
select * from  account;

回滚事务

# 开启事务
set autocommit=0;
		# start transaction;  #可选
# 编写一组事务的语句
update account set balance =1000 where username ='张无忌';
update account set balance =1000 where username ='赵敏';
# 回滚事务
rollback;
select * from  account;

案例2、隔离视图

通过命令行方式演示
1、重启服务
以管理员方式打开 cmd , 输入

net stop mysql80

再重启

net start mysql80

2、再重新打开 CMD ，不用管理员打开

mysql 【-h主机名 -P端口号 】-u用户名 -p密码

3、查看隔离级别

 select @@transaction_isolation;
-----------------------------------------
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

4、设置隔离级别

set session transaction isolation level READ UNCOMMITTED;

select @@transaction_isolation;
------------------------------------------------
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

5、打开表格

 use test;
 select * from account;
 ----------------------------
 +----+----------+---------+
| id | username | balance |
+----+----------+---------+
|  1 | 张无忌   |    1000 |
|  2 | 赵敏     |    1000 |
+----+----------+---------+
2 rows in set (0.00 sec)

6、开启事务、

# 开启事务
set autocommit=0;
		# start transaction;  #可选
# 编写一组事务的语句
update account set balance =500 where username ='张无忌';
update account set balance =1500 where username ='赵敏';

7、换个命令行打开表格

 use test;
 select * from account;

案例3、演示 savepoint 的使用

客户端

insert into account values(25,'张无忌',1000),(29,'赵敏',1000);

set autocommit=0;
delete from account where id=25;
savepoint a;    #设置保存点
delete from account where id=29;
rollback to a;  #回滚到保存点
select * from account;   #结果29号没被删除