ci mysql 事务_MySQL 之事务

什么是事务？

事务是程序中一系列严密的操作，所有操作执行必须成功完成，否则在每个操作所做的更改将会被撤销，这也是事务的原子性(要么成功，要么失败)。

MySQL的事务是在存储引擎层实现。 MySQL的事务有ACID

A：原子性(atomicity)：一个事务必须被视为一个不可分割的单元。

C：一致性(consistency)：数据库是从一种状态切换到另一种状态。

I：隔离性(isolation)：事务在提交之前，对于其他事务不可见。

D：持久性(durablity)：一旦事务提交，所修改的将永久保存到数据库。

1、事务的基本语法

mysql> use test1;

Database changed

mysql> create table bank

-> (

-> name varchar(25),

-> money float

-> );

mysql> insert into bank values('li','1000'),('zhang','5000');

mysql> begin; # begin开启事务，也可以使用start transaction开启事务

mysql> update bank set money=money -1000 where name='zhang';

mysql> update bank set money=money +1000 where name = 'li';

mysql> select * from bank; # 查看数据

+-------+-------+

| name | money |

+-------+-------+

| li | 2000 |

| zhang | 4000 |

+-------+-------+

2 rows in set (0.00 sec)

mysql> rollback; # 回滚事务

Query OK, 0 rows affected (0.00 sec)

mysql> select * from bank; # 再次查询数据，发现已经便会了原来的值

+-------+-------+

| name | money |

+-------+-------+

| li | 1000 |

| zhang | 5000 |

+-------+-------+

2 rows in set (0.00 sec)

mysql> commit; # 提交事务

Query OK, 0 rows affected (0.01 sec)

mysql> select * from bank; # 查询数据

+-------+-------+

| name | money |

+-------+-------+

| li | 1000 |

| zhang | 5000 |

+-------+-------+

2 rows in set (0.00 sec)

一个事务所涉及到的命令如下：

事务开始：start transaction或begin；

事务提交：commit

回滚：rollback

查看自动提交模式是自动还是手动

mysql> show variables like 'AUTOCOMMIT';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON | # ON 表示自动提交

+---------------+-------+

1 row in set (0.00 sec)

mysql> set AUTOCOMMIT=0; # 关闭自动提交(0为关闭，1为开启)

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'AUTOCOMMIT'; # 查看

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)

2、事务的四种隔离级别

事务在提交之前对其他事务可不可见

read unaommitted(未提交读)

read committed(已提交读)

Repeatable read(可重复读)

seaializable(可串行化)

1)未提交读

事务中修改没有提交对其他事务也是可见的，俗称脏读。

#创建测试表

mysql> create table student

-> (

-> id int not null auto_increment,

-> name varchar(32) not null default '',

-> primary key(id)

-> ) engine=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.00 sec)

接下来需要自行开启两个MySQL会话终端，A和B，并且都执行以下命令设置为未提交读。

mysql> set session tx_isolation='read-uncommitted';

客户端A：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;

Empty set (0.00 sec)

mysql> insert into student(name) values('zhangzhang');

Query OK, 1 row affected (0.00 sec)

#要注意，此时事务未提交

客户端B：

mysql> set session tx_isolation='read-uncommitted'; # 设置为未提交读

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from student; # 可查看到客户端A未提交的事务

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

+----+------------+

1 row in set (0.00 sec)

总结：以上可以看出未提交读隔离级别非常危险，对于一个没有提交事务所做修改对另一个事务是可见状态，出现了脏读！非特殊情况不建议使用此级别

2)已提交读

多数数据库系统默认为此级别(MySQL不是)。已提交读级别为一个事务只能已提交事务所做的修改，也就是解决了未提交读的问题。

客户端A插入数据测试：

mysql> set session tx_isolation='read-committed';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

+----+------------+

1 row in set (0.00 sec)

mysql> insert into student(name) values('zhanger');

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhanger |

+----+------------+

2 rows in set (0.00 sec)

客户端B查看(不会看到客户端A插入的数据)：

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

+----+------------+

1 row in set (0.00 sec)

客户端A进行提交：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

客户端B再次进行查看(就可以看到A插入的数据了)：

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhanger |

+----+------------+

2 rows in set (0.00 sec)

总结：从上面可以看出，提交读没有了未提交读的问题，但是我们可以看到客户端A的一个事务中执行了两次同样的SELECT语句，得到不同的结果，因此已提交读又被称为不可重复读。同样的筛选条件可能得到不同的结果

3)可重复读

可重复读解决了不可重复读的问题，数据库级别没有解决幻读的问题。

以下是客户端A和客户端B同时操作(都设置为可重复读，然后两边都开启一个事务)：

mysql> set session tx_isolation='repeatable-read';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

客户端A：

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhanger |

+----+------------+

2 rows in set (0.00 sec)

mysql> update student set name='zhangsan' where id=3;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhangsan |

+----+------------+

2 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhangsan |

+----+------------+

2 rows in set (0.00 sec)

客户端B：

mysql> select * from student;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhanger |

+----+------------+

2 rows in set (0.00 sec)

mysql> commit; # 提交当前事务

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student; # 可以看到客户端A更新的数据

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhangsan |

+----+------------+

2 rows in set (0.00 sec)

总结：上面可以看出，可重复读两次读取的内容不一样。数据库的幻读问题并没有得到解决。幻读只读锁定里面的数据，不能读锁定外的数据，解决幻读出了mvcc机制Mvcc机制

4)可串行化

是最高隔离级别，强制事务串行执行，执行串行了也就解决问题了，这个只有在对数据一致性要求非常严格并且没有并发的情况下使用。

在客户端A及客户端B进行以下操作(设置为可串行读)：

mysql> set session tx_isolation='serializable';

Query OK, 0 rows affected, 1 warning (0.00 sec

客户端A：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id < 10;

+----+------------+

| id | name |

+----+------------+

| 2 | zhangzhang |

| 3 | zhangsan |

+----+------------+

2 rows in set (0.00 sec)

客户端B：

mysql> insert into student(name) values('zhangqi');

#此时进行插入操作时，会一直卡在这里，然后出现下面的报错信息，除非客户端Acommit提交事务

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

以上几种的隔离级别对比如下：

隔离级别

脏读

不可重复

幻读

加锁读

未提交读

是

是

是

否

提交读

否

是

是

否

可重复读

否

否

是

否

串行读

否

否

否

否