MySQL 事务与隔离级别演示

事务

​ 1.1、什么是事务?

​ 一个事务是一个网站的业务逻辑单元，不可再分。

​ 比如：银行账户转账，从A账户向B账户转账10000元，需要执行的两条update语句

​ update t_act set balance = balance-10000 where actno = ‘act-001’；

​ update t_act set balance = balance+10000 where actno = ‘act-002’；

​ 以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

​ 要完成以上要求，就要使用数据库里面的 “事务” 机制。

​ 1.2、与事务有关的语句只有：DML语句。（insert delete update）

​ 因为他们是与数据操作有关的。

​ 1.3、假设所有的业务都能使用一条DML语句搞定，就不需要事务机制了。

​ 但是在实际情况中，通常一个业务需要多条DML语句共同联合完成。

​ 1.4、事务原理

​ 1.5、事务的特性

​ 事务包括四大特性：ACID

​ A：原子性：事务是最小的工作单元，不可再分。

​ C：一致性：事务必须保证多条DML语句同时成功，或者同时失败。

​ I：隔离性：事务A与事务B之间具有隔离。

​ D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

​ 1.6、关于事务之间的隔离性

​ 事务隔离性存在隔离级别，理论上隔离级别包括4个。

​ 第一级别：读未提交（read uncommitted）

​ 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

​ 读未提交存在脏读（Dirty Read）现象：表示读到了脏数据。

​ 第二级别：读已提交 （read committed）

​ 对方事务提交之后的数据我方可以读取到。

​ 读已提交存在的问题是：不可重复读。

​ 解决了：脏读现象

​ 第三级别：可重复读（repeatable read）

​ 这种隔离级别解决了：不可重复读问题。

​ 这种个隔离级别存在的问题是：读取的数据是幻想

​ 第四级别：序列化读/串行化读

​ 解决了所有问题，

​ 效率低，需要事务排队。

​ Oracle数据库默认的隔离级别是：读已提交。

​ mysql数据库默认的隔离级别是：可重复读。

​ 1.7、演示事务

​ mysql事务默认情况下是自动提交的。

​ （什么是自动提交，只要执行任意一条DML语句则提交一次）

​ 怎么关闭自动提交？ start transaction；

​ 演示：mysql是自动提交的

/*准备表*/
drop table if exists t_user;
create table t_user(
	id int primary key auto_increment,
    username varchar(255)
);
insert into t_user(username) values('zs');
rollback;

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
1 row in set (0.03 sec)

mysql> rollback; /*回滚无法生效*/
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
1 row in set (0.00 sec)

​ 演示：start transaction 关闭了自动提交

mysql> start transaction; /*标志着一个事务的开始*/
Query OK, 0 rows affected (0.00 sec)

insert into t_user (username) values('wangwu');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
1 row in set (0.00 sec)
/*以上可以看出回滚成功*/

​ 演示：完整的事务机制

start transaction;/*开启事务*/
insert into t_user (username) values ('wangwu') ; 
update t_user set username = 'xiaogou' where id =1 ;
commit; /*提交*/

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | xiaogou  |
|  3 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

如果想使用保存点的话可以使用： savepoint name1；（一个保存点，类似断点）

这样的话，在未commit之前，就可以使用 rollback name1；回滚到保存点。

2、使用两个事务演示以上的隔离级别

​ 第一：演示read uncommitted

​ 设置事务的隔离级别：set global transaction isolation level read uncommitted;

​ 查看事务的全局隔离级别：select @@global.transaction_isolation;

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

mysql> use csdb
Database changed

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

/*这时在窗口2中给user表添加数据*/
insert into t_user (username) values('SMITH');
mysql> insert into t_user (username) values('SMITH');
Query OK, 1 row affected (0.04 sec)

/*这时在窗口1中对数据进行读取 ，是可以读取到了的，
**但这时候的两个事务都还没有提交自己的事务，但却都可以读取到对方的数据
*/
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | xiaogou  |
|  3 | wangwu   |
|  4 | SMITH    |
+----+----------+
3 rows in set (0.00 sec)

​ 第二：演示read committed

设置事务的隔离级别：set global transaction isolation level read committed;
查看事务的全局隔离级别：select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.00 sec)
/*退出登录*/

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | xiaogou  |
|  3 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

/*此时在窗口2中增加一条数据*/
insert into t_user(username ) values('SMIT');

/*这时候在窗口1中查找对应数据是查找不到的，要等到窗口2提交*/
/*窗口2提交*/
commit;

/*然后在窗口1中查找，就可以显示出来*/
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | xiaogou  |
|  3 | wangwu   |
|  5 | SMIT     |
+----+----------+
3 rows in set (0.00 sec)
/*这就是读已提交，但同样是不符合事务特性。*/

​ 第三：演示 repeatable read

设置事务的隔离级别：set global transaction isolation level repeatable read;
查看事务的全局隔离级别：select @@global.transaction_isolation;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

/*这时候在窗口2中删除表中所有的数据,并且提交*/
delete from t_user;
commit;

/*然后在窗口1中查询数据，还是可以查到，但是数据表中已经没有数据了*/
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  9 | SMIT     |
| 10 | xiaogou  |
| 11 | xiaozhu  |
+----+----------+
3 rows in set (0.00 sec)

​ 第四：演示 serializable

设置事务的隔离级别：set global transaction isolation level serializable;
查看事务的全局隔离级别：select @@global.transaction_isolation;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)
mysql> use csdb
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

/*在窗口1中增加数据*/
insert into t_user(username) values('小狗'),('hy');

/*在窗口2中查询数据后，窗口会卡住，直到另一个commit之后才会继续执行*/
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 12 | 小狗      |
| 13 | hy       |
+----+----------+
2 rows in set (0.00 sec)

这种数据很安全，但是带来的问题就是效率太低，用户体验不好。