ci mysql事务,深入研究MySQL(二)、事务与隔离级别

、事务简介

本章介绍关于事务的知识,在简介中,会分5个点去介绍。

在这里推荐一本书,《数据库事务处理的艺术 事务管理与并发控制》

1. 事务概述

事务就是一组插入、删除、更新语句的集合,比如一个事务中有两条插入语句,一条更新语句,这三条语句要么都执行,要么都不执行,是一个不可分割的单位,ACID 四大特性就是事务的基础,在下面会说。

在操作事务中,我们可以通过commit提交事务,就是对数据库做的所有修改成为永久性,还有rollback命令回滚事务,也就是撤销正在进行的所有未提交的修改,回滚操作在我们安装软件的时候可能见过,都是一些大型软件,由于一些原因安装无法继续进行,可能要撤销前面对文件、注册表等的一些修改。

2.特性Atomicity(原子性):将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行,不可能出现一些操作失败,一些操作成功的情况。

Consistency(一致性):指事务将数据库从一种状态转变为另一种一致的的状态,事务开始前和结束后,数据库的完整性约束没有被破坏。

Isolation(隔离性):事务和事务之间是独立的,互不影响,也就是如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离。

Durability(持久性):事务处理结束后,所做的修改会永久保存到数据库中,即便系统发生故障也不会丢失。

3.场景

如要执行多条语句时,要保证这些语句要么都成功,要么都失败的情况下,就可以通过事务来完成,通过begin或者start transaction命令开始,由commit或者rollback结束,中间是若干条DML语句。begin;

insert xxx;

delete xxx;

commit;

4.相关术语脏读 事务A读取了事务B未提交的数据,此时如果B事务发生错误并进行回滚,那么A事务读取到的数据就是脏数据。

不可重复读 事务A读取某条数据,事务B读取并修改了该数据,事务A再次读取该数据会得到了不同的结果。

幻读 事务A中执行一次查询,然后事务B新插入一行记录,这行记录恰好可以满足事务A所使用的查询的条件。然后事务A又使用相同的查询再次对表进行查找,但是此时却看到了事务B刚才插入的新行。这个新行就称为幻读

5.注意事项MyISAM不支持事务,InnoDB支持事务。

默认情况下,MySQL的事务是自动提交模式的,每一条命令都将当做一个只有一条命令的事务来执行。

事务除了commit,rollback会结束外,使用DDL或者DCL语句也会结束。

二、事务示例

1. 创建示例表结构CREATE TABLE `book` (

`bookId` int NOT NULL AUTO_INCREMENT,

`book_author` varchar(100) DEFAULT NULL,

`bookName` varchar(45) DEFAULT NULL,

`bookTypeId` int DEFAULT NULL,

`description` varchar(45) DEFAULT NULL,

`picture` varchar(45) DEFAULT NULL,

`price` decimal(5,2) DEFAULT NULL,

`publisher` varchar(45) DEFAULT NULL,

PRIMARY KEY (`bookId`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

2.执行事务

下面举个简单的例子,开启一个事务,插入两条数据。begin;

INSERT INTO ebook.book (book_author, bookName, bookTypeId, description, picture, price, publisher)VALUES('未知', 'Kafka入门与实战', 1, 'Kafka入门与实战', 'kafka.png', 54, '清华');

INSERT INTO ebook.book (book_author, bookName, bookTypeId, description, picture, price, publisher)VALUES('未知', 'Android软件安全', 2, 'Android软件安全', 'android.png', 65, '清华');

commit;

执行后效果如下:

如果最后是rollback;语句,则放弃这两条插入语句,事务除了显式提交(commit)和回滚(rollback),还有隐式提交,隐式提交可以是DDL语句,或者再次输入begin或start transaction命令,隐式回滚在退出会话、连接超时或者关机等执行。

3.隐式提交

下面是执行DDL语句后隐式提交示例。mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO ebook.book (book_author, bookName, bookTypeId, description, picture, price, publisher)VALUES('未知', '高性能SQL', 2, '高性能SQL', 'sql.png', 56., '清华');

Query OK, 1 row affected (0.00 sec)

Database changed

mysql> create table test(id int);

Query OK, 0 rows affected (0.05 sec)

结束后会自动提交,也就是DDL语句中默认自带一个commit;

4.隐式回滚mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO ebook.book (book_author, bookName, bookTypeId, description, picture, price, publisher)VALUES('未知', 'Vue.js', 2, 'VUE', 'sql.png', 56.

, '清华');

Query OK, 1 row affected (0.00 sec)

mysql> exit

Bye

执行后数据库中并不会增加这条记录。

三、事务隔离级别

如果要提起事务,那就必须要说说事务的隔离级别,事务隔离界别共有四种,未提交读、已提交读、可重复读、可串行,每种级别对事务的处理能力会有不同程度的影响,隔离强度逐渐增强,性能也逐渐变差,采用哪种隔离级别要根据系统需求权衡决定,下面会分别举例说明。

默认的隔离级别是REPEATABLE-READ,可以通过select @@transaction_isolation查看,这是在8.0后的写法,在之前应该使用tx_isolation。mysql> select @@transaction_isolation;

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

| @@transaction_isolation |

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

| REPEATABLE-READ         |

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

1 row in set (0.00 sec)

mysql>

首先准备表结构、数据。CREATE TABLE `test` (

`id` int DEFAULT NULL,

`user_age` int DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1. 未提交读(READ UNCOMMITTED)设置方式

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

这种隔离级别的事务会读到其它未提交事务的数据,此现象也称之为"脏读",下面进行测试,首先开启两个终端,在终端A下执行下面语句。mysql> select @@transaction_isolation;

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

| @@transaction_isolation |

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

| READ-UNCOMMITTED        |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update test set user_age =1 where id =1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql>

在终端B下执行下面语句,可以看到,读取到了终端A未提交的数据,产生了"脏读" ,大部分业务场景都不允许脏读出现,但是此隔离级别下数据库的并发是最好的。mysql> select @@transaction_isolation;

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

| @@transaction_isolation |

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

| READ-UNCOMMITTED        |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

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

| id   | user_age |

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

|    1 |        1 |

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

1 row in set (0.00 sec)

mysql>

2. 已提交读 (READ COMMITTED)设置方式

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

一个事务开始时,只能看见其他已经提交过的事务,也就是一个事务在提交之前,所做的任何修改对其他事务都是不可见的,下面进行测试。

在终端A下执行下面语句。mysql> select @@transaction_isolation;

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

| @@transaction_isolation |

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

| READ-COMMITTED          |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update test set user_age =2 where id =1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql>

在终端B下执行下面语句。mysql> select @@transaction_isolation;

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

| @@transaction_isolation |

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

| READ-COMMITTED          |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

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

| id   | user_age |

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

|    1 |       33 |

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

1 row in set (0.00 sec)

mysql>

可以看到数据还是老的,因为终端A还没提交,如果终端A进行提交后终端B在进行select,那么数据就是新的了。### 回到终端A进行commit;

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

### 回到终端B下在进行select.

mysql> select * from test;

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

| id   | user_age |

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

|    1 |        2 |

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

1 row in set (0.00 sec)

3.可重复读 (REPEATABLE READ)设置方式

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

这个是MySQL默认的隔离级别,在同一个事务里, 直到事务结束前,都可以反复的读取到事务刚开始看到的数据,并一直不会发生变化,避免了脏读、不可重复读、幻读的发生,但是执行一个范围查询时,可能发生幻读。

在终端A下执行下面语句,此时现在只有一条数据。mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

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

| id   | user_age |

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

|    1 |        2 |

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

1 row in set (0.00 sec)

mysql>

在终端B下执行下面语句。mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert test value(2,2);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

mysql>

回到终端A下继续select,数据是没有变换的,把事务结束后重新开启一个,数据就是最新的了。mysql> select  * from test;

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

| id   | user_age |

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

|    1 |        2 |

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

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

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

| id   | user_age |

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

|    1 |        2 |

|    2 |        2 |

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

2 rows in set (0.00 sec)

mysql>

4. 可串行 (SERIALIZABLE)设置方式

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

他会在每个读的数据行上加表级共享锁,在每次写数据时都加表级排它锁,就造成了并发能力下降,会有大量的超时和锁竞争发生,所以在这个隔离级别下事务都是串行顺序执行的,但是从而避免了脏读、不可重读复读和幻读问题。下面进行测试。

在终端A下执行下面命令,上面说了在写入数据的时候会加入排它锁,所以其他事务查写的时候会阻塞。mysql> select @@transaction_isolation;

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

| @@transaction_isolation |

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

| SERIALIZABLE            |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert test value(3,2);

Query OK, 1 row affected (0.00 sec)

mysql>

在终端B下执行下面命令,会发现阻塞住了,直到一定时间过后,会抛出异常,或者直到终端A下执行commit。mysql> select @@transaction_isolation;

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

| @@transaction_isolation |

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

| SERIALIZABLE            |

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

1 row in set (0.00 sec)

mysql> select * from test;

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

mysql> select * from test;

四、事务隔离级别的影响

一图胜千言。

8b1501074f73c16bc7eb235d8ba3f4d6.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值