Mysql_TCL事务控制

#TCL 事务控制

 

    Transaction Control Language 事务控制语言

        事务有一个单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句时相互依赖的。

    并发事务

    1、事务的并发问题是如何发生的？

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

    2、并发问题都那些？

    脏读：一个事务读取了其它事务还没有提交的数据，读到的是其它事务“更新”的数据

    不可重复读：一个事务多次读取，结果不一样

    幻读：一个事务读取了其它事务还没有提交的数据，只是读到的是 其它事务“插入”的数据

 

    

 

    事务：

        一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

 

        案例：转账

        张三向李四进行转账500元

            张三   1000

            李四   1000

            涉及到两条SQL语句

        update 表 set 张三的账户=500 where name='张三';

        如果转账时出现意外 事务就会报异常 回到最初时 这两条语句是一个执行单元需要两条语句都执行完毕

        update 表 set 李四的账户=1500 where name='李四';

 

    事务的特性：

    ACID

        原子性：一个事务不可再分割，要么都执行要么都不执行

        一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致的状态

        隔离型：一个事务的执行不受其它事务的干扰

        持久性：一个事务一旦提交，则会永久的改变数据库的数据

 

    事务的创建

        隐士事务：事务没有明显的开启和结束的标记

        比如insert、update、delete语句

        如：delete from 表 where id =1;比如执行一条删除语句 会自动提交事务 如果我想两条语句位一个单元就需要关闭自动提交事务

 

        显示事务：事务具有明显的开启和结束的标记

        前提：必须先设置自动提交功能位禁用状态

            set autocommit = 0;

            步骤1：开启事务

            set autocommit=0;

            statr transaction;可选的

            步骤2：编写事务的sql语句(select insert update delete)

            语句1;

            语句2;

            .... ....

            步骤3：结束事务

            commit;提交事务

            rollback;回滚事务

            savepoint 节点名;设置保存点

 

    事物的隔离级别：

            read uncommitted：出现脏读、幻读、不可重复读

            read committed：避免脏读，出现脏读和不可重读读

            repeatale read：避免脏读、不可重复读，出现幻读

            serializable：避免脏读、幻读、不可重复读，但效率底下

 

            mysql中默认 第三个隔离级别 repeatable read

            oracle中默认 第二个隔离级别 read committed

        查看隔离级别

            select @@tx_isolation;

        设置隔离级别

            set session(当前)|global(全局) transaction isolation level 隔离级别;

 

        开启事务的语句;

        update 表 set 张三的账户=500 where name='张三';

        update 表 set 李四的账户=1500 where name='李四';

        结束事务的语句;

 

自己理解事务 set autocommit=0;将自动提交事务关闭 如果不关闭自动提交事务 一条update执行完就自动提交事务

我需要两条甚至多条增删改查语句，就需要关闭自动提交事务。

就好比一个账户与另一个人转账：一条是减去账户的余额sql语句，对应一条时账户增余额语句，如果第一账户转出的语句执行完，出现意外，事务就直到作用，需要两条语句都执行才能完成。

所以事务是一个语句组执行单元

 

    #1.查看数据引擎 InnoDB支持事务

        SHOW ENGINES;

    #2.查看自动事务提交 autocommit

        SHOW VARIABLES LIKE 'autocommit';

         

 

    #3.关闭自动提交事务  只是在当前的回话中off了

        SET autocommit = 0;

        

 

演示事务案例

        DROP TABLE IF EXISTS account;

        CREATE TABLE IF NOT EXISTS account(

            id INT PRIMARY KEY AUTO_INCREMENT,

            username VARCHAR(20),

            balance DOUBLE

        );

        插入两个账户 余额分别为1000元

        INSERT INTO account(username,balance) VALUE('张无忌',1000),('赵敏',1000);

        

 

#演示事务的使用步骤

        #开启事务

            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;

 

        进行事务回滚    两条sql语句将两个账户修改为1000元 将执行的事务回滚 原来的两个账户余额分别位500，1500 所以没有变化

        如果是回滚 插入语句时驻留在内存中 没有提交的磁盘当中

        

 

事务的隔离级别

        

        

 

    查看前数据库的隔离级别  默认的安全隔离

        select @@tx_isolation;

        

    set names gbk;设置字符集

第一个隔离级别 read uncommitted 读未提交的事务

    设置隔离级别为最低级别  set session transaction isolation level read uncommitted; 设置事务不受约束

      设置隔离级别为最低级别  才能看到脏读

    

 

    再进行隔离级别的查看 为read-uncommitted

    

 

    事务一. t1 开启事务同时更新一条语句  将张无忌改为了join 

    

 

此时没有没有提交事务 查询数据

    

 

此时打开第二个窗口 也要设置事务的隔离级别最低 read uncommitted

 

事务二。t2 开启事务

当事务一，提交执行 rollback回滚事务时 就会看到脏读和幻读

 

第二个隔离级别 read committed 可以避免脏读 但不可避免重复读

    事务一 t1 read committed 读已提交的事务 

    

事务开启 为数据更新将张无忌 改为join

 

 

事务二t2 也改为读已提交的事务

 

事务一t1 修改张无忌为john中 未提交事务 事务二t2中select查询的还是张无忌 所以避免了脏读 

但不避免了重复读 在多次查询中出现不一样 出现不可重复读 事务二t2未提交

 

第三个隔离事务级别 repeatale-read  能解决脏读 不可重复读 但不能解决 幻读

同时都设置为

事务一与事务二 开启事务

事务一t1  将john更改为tom 但事务没有提交 

事务二t2  执行第一次查询 为join 此时t1为未提交事务

事务一t1 提交事务commit  事务二再次进行查询未出现t1修改tom  两个事务进行隔离 互不干涉

事务二 t2提交事务 在隐式提交事务 select *from account;

 

隔离事务级别 repeatale-read 不能解决 幻读 如：

事务一 t1 查询有两行数据  update account set username='kkk'; 按说只有两条数据修改

 

事务二 t2 insert into account value(null,'俊杰',1000); 插入一条数据时 同时t2提交 表中就会出现三条数据 

事务一  再进行更新操作时 就会出现幻读的现象 因为t2插入数据以后提交事务 t1在进行更新就会出现操作了3条数据

 

隔离事务级别 最高级别 Serializable;  串行化性能 效率底下

事务一与事务二 设置为serializable 最高隔离级别

 

t1开启事务 执行操作 t2此时也开启事务 要进行插入操作时出现阻塞  说明t1开启事务t1持有锁 t2需要等待t1释放锁

 

#2.delete和truncate在事务使用时的区别

    #演示delete delete不支持回滚的

        SET autocommit=0;

        START TRANSACTION;

        DELETE FROM account;

        ROLLBACK;

    删除account表 rollback进行回滚

 

    #演示truncate truncate不支持回滚的

        #在回滚时truncate 磁盘提交文件 truncate不支持回滚的

        SET autocommit=0;

        START TRANSACTION;

        TRUNCATE TABLE account;

        ROLLBACK;

 

#3.演示savepoint的使用

    SET autocommit=0;

    START TRANSACTION;

    DELETE FROM account WHERE id=1;

    SAVEPOINT a;#设置保存点

    DELETE FROM account WHERE id=3;

    ROLLBACK TO a;#回滚到保存点

原来查询的  开启事务 执行两条删除语句 回滚到a节点  DELETE FROM account WHERE id=3;被回滚 id=1数据被删除 id=3没有删除