Mysql笔记（6）【事务】

目录

十六、事务 Transaction

1）事务是什么（5点）

2）关于银行转账业务

3）概述

4）事务的隔离级别

5）查看隔离级别

6）设置服务器缺省隔离级别

7）设置隔离级别作用的范围

8）隔离级别与一致性问题的关系

9）并发事务与隔离级别示例

---

十六、事务 Transaction

1）事务是什么（5点）

        1.一个最小的不可再分的工作单元；

        2.通常一个事务对应一个完整的业务；(例：银行转账业务)

        3.一个完整的业务需要批量的DML(insert、update、delete)语句共同完成；

        4.事务只和DML语句有关系，或者说只有DML语句才有事务；

        5.DML语句的多少，取决于业务的逻辑，业务逻辑不同DML语句个数不同；

2）关于银行转账业务

1.银行转账业务：

        是一个完整的业务，最小的单元，不可再分，也就是说银行转账业务是一个完整的事务

2.示例：账户转账

        a）t_act账户表：actno，balance

actno	balance
act-001	50000.0
act-002	10000.0

        b）act-001转10000.0给act-002，操作如下：

                Update t_act set balance=40000.0 where actno='act-001';

                Update t_act set balance=20000.0 where actno='act-002';

3.分析：

                a）以上两条DML语句必须同时成功或者同时失败，因为它为最小业务单元，不可拆分；

                b）当第一条DML语句执行成功之后，并不能将底层数据库中的数据修改，只是将操作记录一下，这个记录是在内存中完成的；

                c）当第二条DML语句执行成功之后，才可以修改数据库中的数据；

                d）若第二条DML语句执行失败，将清空所有的历史操作记录；

结论：要完成以上功能，必须借助事务transaction；

3）概述

事务可以保证多个操作原子性，要么全成功，要么全失败。

对于数据库来说，事务保证批量的DML要么全成功，要么全失败。

1.事务的四个特性（ACID）：

        a）原子性(Atomicity)

                事务是最小的单元，不可再分；

        b）一致性(Consistency)

                事务要求所有的DML语句操作，要么同时成功，要么同时失败；

        c）隔离性(Isolation)

                事务允许多个用户对同一个数据进行并发访问，一个事务不会影响其它事务的运行；

        d）持久性(Durability)

                事务完成之后，该事务对数据库所做的更改持久的保存，不能回滚；

2.事务中的一些概念：

        a）开启事务：start transaction

        b）结束事务：end transaction

        c）提交事务：commit transaction

        d）回滚事务：rollback transaction

        3.和事务有关的两条SQL语句【TCL】：

        a）commit：提交

        b）rollback：回滚

3.事务开启和结束的标志是什么？

        a）开启的标志

                任何一条DML语句执行，标志事务的开启；

        b）结束的标志

                提交(commit)：成功的结束，将所有的DML语句操作记录和底层硬盘文件中的数据进行同步；

                回滚(rollback)：失败的结束，将所有的DML语句操作记录清空；

4.重点：

        a）在事务进行过程中，未结束之前，DML语句不会修改底层数据库文件的数据；

        b）只是将历史操作记录一下，在内存中完成记录；

        c）只有在事务结束后，而且成功的结束才会修改底层数据库文件中的数据；

        6.MySQL事务的提交和回滚的演示

        1.MySQL默认事务：查看事务状态：show variables like '%commit%';

        a）在MySQL数据库管理系统中，默认情况下，事务是自动提交的；

        也就是说，只要执行一条DML语句，就开启事务，并且提交事务；

        2.第一种：关闭MySQL事务自动提交

        a）事务成功用法：start transaction;commit

第一步：start transaction;	手动开启事务
第二步：DML语句…	执行批量DML语句
第三步：commit;	手动提交事务【事务成功结束】

        演示例子：在t_user表中插入数据：事务成功提交

        准备：窗口①-创建t_user表：id为自增主键，name varchar(32)

        Drop table if exists t_user;（删除数据表t_user）

        Create table t_user(id int(10) primary key auto_increment,name varchar(32));（创建t_user表）

        Desc t_user;（查看t_user是否创建）

        Insert into t_user(name) values('jack');（添加数据）

        Insert into t_user(name) values('lucy');（添加数据）

        Insert into t_user(name) values('lily');（添加数据）

        Select * from t_user;（查看t_user表数据）

第一步：窗口①，查询原数据

        Select * from t_user;（查看t_user表数据）

第二步：窗口①，开启事务 start transaction，插入数据，查询数据

        Start transaction;（开启事务）

        Insert into t_user(name) values('yuan');（添加数据）

        Insert into t_user(name) values('guo');（添加数据）

        Select * from t_user;（查看表t_user数据）

第三步：窗口②，查看t_user表数据

        Select * from t_user;(查看t_user表数据)

第四步：窗口①，手动提交事务commit;

        Commit;（提交）

第五步：窗口②，查询t_user表；

        Select * from t_user;

3.第二种：关闭MySQL事务自动提交：只对当前会话有效

1.两种关闭自动提交事务

a）set autocommit=off

b）set session autocommit=off

2.两种打开自动提交事务

a）set autocommit=on

b）set session autocommit=on

注：以上打开或关闭事务只对当前窗口有效；

3.查询事务状态：show variables like '%commit%';

4）事务的隔离级别

1.事务四大特性ACID之一：隔离性（Isolation）

2.隔离性有四个隔离级别：

        a）read uncommitted---读未提交

        b）read committed-----读已提交

        c）repeatable read----可重复读

        d）serializable-------串行化

3.read uncommitted 读未提交（级别最低）

        3.1事务A和事务B，事务A未提交的数据，事务B可以读取；

        3.2这里读取到的数据可以叫做“ 脏数据”或“脏读 dirty read”；

        3.3读未提交隔离级别最低，这种级别一般只在理论上存在，数据库默认隔离级别一般都高于该隔离级别；

4.read committed 读已提交

        4.1事务A和事务B，事务A提交的数据，事务B才可读取到；

        4.2该隔离级别可以避免脏读数据；

        4.3该隔离级别能够导致“不可重复读取”；

5.repeatable read 可重复读（Oracle，MySQL默认）

        5.1事务A和事务B，事务A提交之后的数据，事务B读取不到；

        5.2事务B是可重复读到数据的;

        5.3这种隔离级别可以避免“脏读和不可重复读”，达到“重复读取”；

        5.4虽然可以达到“可重复读”效果，但是会导致“幻象读”；

        6.serializable 串行化

6.1事务A和事务B，事务A在操作数据库表中的数据的时候，事务B只能排队等待；

        6.2这种隔离级别很少用，吞吐量太低，用户体验不好；

        6.3这种隔离级别避免“幻想读”，每一次读取都是数据库表中真实的记录；

        6.4事务A和事务B，不能并发；

5）查看隔离级别

1.查看当前会话隔离级别：

        Select @@tx_isolation;

        Select @@session.tx_isolation;

2.查看当前全局隔离级别：

        Select @@global.tx_isolation;

6）设置服务器缺省隔离级别

1.第一种：修改my.ini配置文件

        在my.ini文件中的[mysqld]下面添加：

        transaction-isolation = READ-COMMITTED

a)隔离级别可选项为：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

2.第二种：通过命令设置事务隔离级别

a）set transaction isolation level 隔离级别名称;

b）isolation可选项：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

7）设置隔离级别作用的范围

1.事务隔离级别的作用范围分为两种：会话级、全局级

        a）会话级（session）：只对当前会话有效

        b）全局级（global）：对所有会话有效

2.使用方法如下：

        a）会话级：

        Set transaction isolation level 隔离级别名称;

        Set session transaction isolation level 隔离级别名称;

        b）全局级：

        Set global transaction isolation level 隔离级别名称;

8）隔离级别与一致性问题的关系

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable	×	×	√
serializable	×	×	×

9）并发事务与隔离级别示例

1.读未提交 read uncommitted

会话一	会话二
Set global transaction isolation level read uncommitted;
退出会话窗口
Use bjpowernode; Drop table if exists t_user; Create table t_user( No int(10) primary key auto_increment; Name varchar(32); )
	Use bjpowernode;
Start transaction;
	Start transaction;
Insert into t_user(name) values('a');
	Select * from t_user;

2.读已提交 read committed

会话一	会话二
Set global transaction isolation level read uncommitted;
退出会话窗口
Use bjpowernode; Drop table if exists t_user; Create table t_user( No int(10) primary key auto_increment; Name varchar(32); )
	Use bjpowernode;
Start transaction;
	Start transaction;
Insert into t_user(name) values('b');
	Select * from t_user;
Commit;
	Select * from t_user;

3.可重复读 repeatable read

会话一	会话二
Set global transaction isolation level repeatable read；
退出会话窗口
Use bjpowernode; Drop table if exists t_user; Create table t_user( No int(10) primary key auto_increment; Name varchar(32); )
	Use bjpowernode;
Start transaction;
	Start transaction;
Select * from t_user;
	Select * from t_user;
Insert into t_user(name) values('c'); commit；
	Select * from t_user;

4.串行化 serializable

会话一	会话二
Set global transaction isolation level serializable;
退出会话窗口
Use bjpowernode; Drop table if exists t_user; Create table t_user( No int(10) primary key auto_increment; Name varchar(32); )
	Use bjpowernode;
Start transaction;
	Start transaction;
Select * from t_user;
	Select * from t_user;
Insert into t_user(name) values('c');
	Select * from t_user;
Commit;