mysql8 事务级别查看数据库事务级别 mysql

转载

网络安全卫士 2024-07-21 20:50:55

文章标签 mysql8 事务级别查看 mysql 事务隔离级别数据 文章分类 MySQL 数据库

说起关系型数据库，总是绕不开事务(transaction)一说，本文旨在简单叙述mysql的事务概念，并通过实际的sql数据来解释事务隔离级别。下面所有测试引擎均使用innodb。

1、什么是事务？

事务(transaction)是对数据库库操作的一个序列，当一个事务被提交给数据库后，应该要保证事务里的所有执行都成功，或者都失败。

2、事务的基本特性

一般我们是的事务的基本特性，就是指事务的ACID，
　　 1)、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做。
　　 2)、一致性（Consistency）：指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
　　 3)、隔离性（Isolation）：数据库库不同的事务之间彼此没有任何干扰。
4)、持久性（Durability）：事务提交后，事务对数据库的所有更新将被持久化，不能回滚。

在具体解释事务前我们要先了解下数据库缓冲池的概念还有MVCC。

mysql8 事务级别查看数据库事务级别 mysql_mysql8 事务级别查看

数据库缓冲池就是内存中的一块区域。通过内存的高速来弥补硬盘的速度对数据库的应影响。数据库读取的时候，会将读到的数据存放在缓冲池中。
mysql的基本数据查询流程如下在发起sql请求的时候，首先会在缓存中去查新缓存结果，这里的缓存只要查询有一个字节不同，就不会匹配缓存结果。
如果没有命中缓存，就会走如下流程。解析sql发起查询请求，查询到结果返回到缓存中去，并返回给客户端。

MVCC(Multi-Version Concurrency Control)多版本的并发控制协议,mysql的innodb引擎实现就是基于mvcc的。与之相对应的是LBCC(Lock-Based Concurrent Control)基于锁的并发控制。mvcc的主要优点在于读不加锁，读写不冲突。
在一个mvcc控制的系统中，读可以分为，快照读和当前读。

快照读： select * from wcs where xxxx; 这类以的简单查询，不加锁
当前读： 1>增删改语句以及
2>select * from xxx where xxx for update
3>select * from xxx where xxx lock in share mode;
当前读会读取最新的记录版本。并且会加锁，保证在事务中读取后，不允许被其他事务修改。
分为S锁(共享锁)(上述示例:3)和X锁(排他锁)(上述示例:1,2)，
这里不做mvcc的具体介绍，只为了引入快照读和当前读，以便后面的事务并发控制。

3、事务并发的问题

1）、脏读（dirty read），指的是一个事务读取了另一个事务修改未提交的数据
2）、不可重复读（unrepeatable read），和脏读有一定的关联，一个事务读取了一个数据，再次读取的时候读到了被另一个事务修改过的数据。
3）、幻读（phantom problem），一个事务读取到了另一个事务提交的新增或删除的结果。这和不可重复读有点类似。侧重点在新增删除
脏读很容易了解，在事务隔离级别最低的情况下会发生这个情况，
幻读和不可重复读比较奇怪。
假设有一个表User，有age,name,sex字段
事务A，查询sex=’男’的人，查出10个人
事务B,新增一个sex=’男’的人。并提交事务
事务A，继续操作这个时候，修改sex=’男’的人的age=15岁，发现修改结果为11个人。产生了幻读。

mysql事务隔离级别：

事务隔离级别脏读不可重复读幻读
读未提交（read-uncommitted）是是是
不可重复读（read-committed）否是是
可重复读（repeatable-read）否否是
串行化（serializable）否否否
Mysql innodb默认事务隔离级别是repeatable-read
顺带一提，oracle的默认事务隔离级别是read committed.

为什么serializable的事务隔离级别这么高，mysql的默认不是它呢。因为serializable是事务隔离级别最高的一种，因为它有mvcc控制调整到LBCC控制，对于他来说都是当前读，并对数据加锁。在该隔离级别下，读写冲突，并发性能下降。

4、具体示例

(1).查看当前会话mysql服务的事务隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

(2).查看当前会话和全局事务隔离级别

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

(3).修改当前会话mysql事务隔离级别

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

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

(4).修改全局事务隔离级别

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

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

(5).事务并发问题示例
1.脏读
首先我们打开两个链接，分别修改事务隔离级别为read-uncommitted，

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

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

事务一，查询一张表，

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

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

mysql> update wcs set status = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

这个时候这个事务并没有提交。
再开启另外一个事务，查询这张表

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

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

查询到了第一个事务未提交的数据，属于脏读
第一个事务，rollback后，在查询

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

2.不可重复度
为了显示和脏读的区别，这里我们不在使用read uncommitted事务隔离级别，用read-committed隔离级别
打开两个连接分别修改事务隔离级别：

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

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

事务一，开启事务后查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
###步骤一：
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

###步骤三
mysql> update wcs set status = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

###步骤五
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务二，快开启事务后：

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

###步骤二
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.01 sec)

###步骤四
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.01 sec)


###步骤六
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

mysql>

1>事务一，先查询表，得到一个结果status为1
2>事务二，查询表，也得到了status为1
3>事务一，更新表，将status修改为2
4>事务二，查询表，得到表为1(该事务隔离级别，未发生脏读)
5>事务一，提交事务，
6>事务二，查询表，得到status为2（未重复读）

3.幻读
先打开两个链接，分别修改事务隔离级别为repeatable-read

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

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

事务一

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

###步骤一
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)
###步骤三
mysql> insert into wcs values('WCS2','0.0.0.1',30000,1);
Query OK, 1 row affected (0.01 sec)

###步骤五
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
| WCS2    | 0.0.0.1   | 30000 | 1      |
+---------+-----------+-------+--------+
2 rows in set (0.00 sec)

事务二

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

###步骤二
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

####步骤四
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

####步骤六
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

###步骤七
mysql> update wcs set status = 2 where status = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

1>事务一，查询表，结果为一行数据WCS1，status为1
2>事务二，查询表，结果为一行数据WCS1，status 为1
3>事务一，插入一行数据WCS2，status为1
4>事务二，查询数据,一行数据WCS1，status为1（该事务隔离级别，未发生脏读）
5>事务一，提交事务，查询结果两行数据，WCS1，WCS2。status为1
6>事务二，查询数据,一行数据WCS1，status为1（该事务隔离级别，重复读）
7>事务二，更新数据status为1的，查询为1，更新matched为2，changed为2，发生了幻读。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。