mysql手册02_事务

柔情只为你懂 2022-11-26 12:56 91阅读 0赞

**mysql手册02\_事务**

--------------------

mysql中，事务是一个最小的不可分割的工作单元，事务能够保持一个业务的完整性。

比如银行转账：

update user set money=money-100 where name='a';
    update user set money=money+100 where name='b';

必须保证以上sql语句同时成功或同时失败。

mysql的**自动提交**是默认打开的，此时sql语句的执行不能**回滚(rollback)**

mysql> select @@autocommit;
    +--------------+
    | @@autocommit |
    +--------------+
    |            1 |
    +--------------+

设置关闭自动提交
    set autocommit=0;
    
    插入数据并手动提交(一旦提交就不可回滚)：：
    insert into user4 values(1,'a',1000);
    commit;
    
    插入数据并回滚：
    insert into user4 values(2,'b',1000);
    rollback;

在自动提交开启的状态下，可通过**begin**或**start transaction**开启事务回滚：

begin;
    update user set money=money-100 where name='a';
    update user set money=money+100 where name='b';
    rollback;
    
    start transaction;
    update user set money=money-100 where name='a';
    update user set money=money+100 where name='b';
    rollback;

**事务的四大特征ACID：**

Atomicity 原子性：事务是一个最小的不可分割的工作单元
    Consistency 一致性：必须保证同一事务的sql语句同时成功或同时失败，保证业务的完整性。
    Isolation 隔离性：事务A和事务B之间是具有隔离性的。
    Durability 持久性：事务一旦结束（commit,rollback）就不可返回。

**事务的隔离性：**

read uncommitted	读未提交的
    read committed	读已提交的
    repeatable read	可重复读(mysql的默认隔离级别)
    serializable	串行化

**查看数据库的隔离级别：**

mysql 8.0 系统级别
    mysql> select @@global.transaction_isolation;
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | REPEATABLE-READ                |
    +--------------------------------+
    
    mysql 8.0 会话级别
    mysql> select @@transaction_isolation;
    +-------------------------+
    | @@transaction_isolation |
    +-------------------------+
    | REPEATABLE-READ         |
    +-------------------------+

**修改隔离级别为 READ-UNCOMMITTED：**

set global transaction isolation level read uncommitted;
    
    select @@global.transaction_isolation;
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | READ-UNCOMMITTED               |
    +--------------------------------+

**read uncommitted**  
如果有事务A和事务B，A事务对数据进行操作，在操作的过程中，事务A还没有被提交，但是B可以看见A操作的结果。

select * from user4;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |  1000 |
    |  2 | b    |  1000 |
    |  3 | c    |  1000 |
    +----+------+-------+
    
    A事务开始转账：
    start transaction;
    update user4 set money=money-100 where name='a';
    update user4 set money=money+100 where name='b';
    
    B事务开始查看数据库状态，此时A事务还未提交
    select * from user4;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |   900 |
    |  2 | b    |  1100 |
    |  3 | c    |  1000 |
    +----+------+-------+
    
    A事务回滚
    rollback;
    
    B事务再次查看数据库状态
    select * from user4;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |  1000 |
    |  2 | b    |  1000 |
    |  3 | c    |  1000 |
    +----+------+-------+
    
    这种B事务读取到A事务未提交的数据叫作脏读，B事务读取到了脏数据。
    在实际开发过程中不允许出现脏读行为。

**修改隔离级别为 READ-COMMITTED：**

set global transaction isolation level read committed;
    
    select @@global.transaction_isolation;
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | READ-COMMITTED                 |
    +--------------------------------+

**read committed**  
如果有事务A和事务B，A事务对数据进行操作，事务A提交后，B可以看见A操作的结果。

B开启事务开始查账：
    start transaction;
    select * from user4;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |  1000 |
    |  2 | b    |  1000 |
    |  3 | c    |  1000 |
    |  4 | d    |   200 |
    +----+------+-------+
    
    A开启事务，给d充值800元，A提交事务：
    start transaction;
    update user4 set money = 1000 where name='d';
    commit;
    
    B计算账户总额，发现与之前查账的结果不符：
    select sum(money) from user4;
    +------------+
    | sum(money) |
    +------------+
    |       4000 |
    +------------+
    此时事务B出现了不可重复读现象。

**修改隔离级别为 REPEATABLE-READ ：**

set global transaction isolation level repeatable read;
    
    select @@global.transaction_isolation;
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | REPEATABLE-READ                |
    +--------------------------------+

**repeatable read**  
AB同时开启事务，A修改了数据并提交，B事务无法看到A事务修改后提交的数据，解决了不可重复读的问题，但此时B事务在对数据进行更新和修改时可能会出现问题。

A开启事务：
    start transaction;
    B开启事务：
    start transaction;
    
    A添加账户并提交：
    insert into user4 values(5,'e',1000);
    commit;
    
    B查看账户并添加账户：
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |  1000 |
    |  2 | b    |  1000 |
    |  3 | c    |  1000 |
    |  4 | d    |  1000 |
    +----+------+-------+
    insert into user4 values(5,'e',10000);
    
    B事务出现报错：
    ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
    原因：B事务出现了幻读（A事务修改了数据，但B事务不知道）

**修改隔离级别为 SERIALIZABLE ：**

set global transaction isolation level serializable;
    
    select @@global.transaction_isolation;
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | SERIALIZABLE                   |
    +--------------------------------+

**serializable**  
保证了在同一时刻只能有一个事务进行写操作。

A开启事务：
    start transaction;
    B开启事务：
    start transaction;
    
    A添加账户并提交：
    insert into user4 values(6,'f',1000);
    commit;
    
    B查看账户，看到了A提交后的数据：
    select * from user4;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |  1000 |
    |  2 | b    |  1000 |
    |  3 | c    |  1000 |
    |  4 | d    |  1000 |
    |  5 | e    |  1000 |
    |  6 | f    |  1000 |
    +----+------+-------+ 
    
    A开启事务并添加账户，未提交：
    start transaction;
    insert into user4 values(7,'g',1000);
    
    同时B添加账户，此时事务B处于等待（阻塞）状态：
    insert into user4 values(7,'g',1000);
    
    A提交事务：
    commit;
    
    在A提交事务之后，B事务等待状态结束，B报错：
    ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
    
    串行化的问题是：性能特差！

**性能高低（隔离级别越高，出现的问题越少，但性能则越差）：**

READ-UNCOMMITTED > READ-COMMITTED > REPEATABLE-READ > SERIALIZABLE