mysql事务隔离级别怎么设置_Mysql事务隔离级别

最新推荐文章于 2024-06-23 21:40:41 发布

weixin_39959335

最新推荐文章于 2024-06-23 21:40:41 发布

阅读量394

点赞数

文章标签： mysql事务隔离级别怎么设置

本文链接：https://blog.csdn.net/weixin_39959335/article/details/113985725

版权

本文详细介绍了数据库事务的ACID特性，并重点讲解了事务的四种隔离级别：未提交读、提交读、可重复读和串行化。通过MySQL的InnoDB存储引擎的实例，展示了不同隔离级别下可能出现的脏读、不可重复读和幻读现象，强调了可重复读级别下幻读的处理。最后，总结了各隔离级别的特点和适用场景。

摘要由CSDN通过智能技术生成

一、事务的四大特性(简称ACID)

数据库如果支持事务的操作，那么就具备以下四个特性：

1、原子性(Atomicity)

事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。

2、一致性(Consistency)

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

3、隔离性(Isolation)

一个事务的执行不能被其他事务干扰。

4、持续性/永久性(Durability)

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

二、事务的四种隔离级别

按照SQL:1992 事务隔离级别，InnoDB默认是可重复读的(REPEATABLE READ)。MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别。你可以在命令行用--transaction-isolation选项，或在选项文件里，为所有连接设置默认隔离级别。

例如，你可以在my.inf文件的[mysqld]节里类似如下设置该选项：

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意：默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。任何客户端都能自由改变会话隔离级别(甚至在事务的中间)，或者为下一个事务设置隔离级别。

你可以用下列语句查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

隔离级别

脏读(Dirty Read)

不可重复读(NonRepeatable Read)

幻读(Phantom Read)

未提交读(Read uncommitted)

可能

已提交读(Read committed)

不可能

可能

可重复读(Repeatable read)

不可能

可能

可串行化(Serializable )

不可能

1.未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据2.提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)3.可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读4.串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

Read Uncommitted这种级别，数据库一般都不会用，而且任何操作都不会加锁，这里就不讨论了。

1.)Mysql事务隔离级别之未提交读

mysql> setsession transaction isolation level read uncommitted; //设置未提交读

Query OK,0 rows affected (0.00sec)

mysql> select@@session.tx_isolation;+------------------------+

| @@session.tx_isolation |

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

| READ-UNCOMMITTED |

+------------------------+mysql>CREATE TABLE `test` (-> `id` varchar(64) NOT NULL COMMENT '主键ID',-> `name` varchar(64) NOT NULL COMMENT '名称'

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test';

Query OK,0 rows affected (0.03sec)

mysql>start transaction; //开启事务

Query OK,0 rows affected (0.00sec)

mysql> INSERT INTO test (id, name) VALUES ('c2f25a90-fd66-420d-9003-3b0f1d16c0b7', '123'); //插入数据，但未提交事务

Query OK,1 row affected (0.01sec)

mysql>

打开另一个session看下，能不能查到数据

示例如下，也能查到数据，说明出现了脏读：就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据

mysql> setsession transaction isolation level read uncommitted;

Query OK,0 rows affected (0.00sec)

mysql> select * fromtest;+--------------------------------------+------+

| id | name |

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

| c2f25a90-fd66-420d-9003-3b0f1d16c0b7 | 123 | //读取的是另一事务未提交的数据

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

1 row in set (0.00sec)

mysql>

2.)Mysql事务隔离级别之提交读

1.设置隔离级别为提交读，并查询数据

sesson1

mysql> setsession transaction isolation level read committed;

Query OK,0 rows affected (0.00sec)

mysql> select@@session.tx_isolation;+------------------------+

| @@session.tx_isolation |

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

| READ-COMMITTED |

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

1 row in set (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select * fromtest; //数据是空的

Emptyset (0.01 sec)

2)在session2开启另外一个事务，插入数据，事务不提交

start transaction;

Query OK,0 rows affected (0.00sec)

mysql> INSERT INTO test (id, name) VALUES ('c2f25a90-fd66-420d-9003-3b0f1d16c0b7', '123');

Query OK,1 row affected (0.00 sec)

再次在session1中再查一次数据

mysql> select * fromtest; //还是空的。

Emptyset (0.00 sec)

3)在sesson2的事务提交，并在session1中再查一次数据，发现已经可以查到数据了

mysql>commit;

Query OK,0 rows affected (0.01 sec)

mysql> select * fromtest;+--------------------------------------+------+

| id | name |

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

| c2f25a90-fd66-420d-9003-3b0f1d16c0b7 | 123 |

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

1 row in set (0.01 sec)

说明提交读出现了不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

3.)Mysql事务隔离级别之可重复读

1.在session1中设置事务隔离级别为可重复读，并开启事务，进行查询test表数据，发现只有三条数据

mysql> setsession transaction isolation level Repeatable read;

Query OK,0 rows affected (0.00sec)

mysql> select@@session.tx_isolation;+------------------------+

| @@session.tx_isolation |

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

| REPEATABLE-READ |

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

1 row in set (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select * fromtest;+--------------------------------------+--------+

| id | name |

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

| c2f25a90-fd66-420d-9003-3b0f1d16c0b7 | 123 |

| asdasd | 321 |

| 可重复读测试 | lklklk |

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

3 rows in set (0.00 sec)

2.在另一个session中开启一个事务，进行数据的插入，并提交事务

mysql> select@@session.tx_isolation;+------------------------+

| @@session.tx_isolation |

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

| REPEATABLE-READ |

+------------------------+mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> INSERT INTO test (id, name) VALUES ('可重复读测试123', 'lklklk');

Query OK,1 row affected (0.01sec)

mysql>commit ;

Query OK,0 rows affected (0.00sec)

3.在session1中再查询test数据，发现还是三条数据

mysql> select * fromtest;+--------------------------------------+--------+

| id | name |

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

| c2f25a90-fd66-420d-9003-3b0f1d16c0b7 | 123 |

| asdasd | 321 |

| 可重复读测试 | lklklk |

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

3 rows in set (0.00 sec)

可重复读，就是在一个事务查询中，若另一个事务修改数据，并不会影响到这个查询的事务查询的数据；也就是说在一个查询事务中，从开启事务到提交事务之间的数据都是不受其他事务影响的

三、未提交读、提交读和可重复读都会出现幻读

幻读：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql>CREATE TABLE `t_bitfly` (

`id` bigint(20) NOT NULL default '0',

`value` varchar(32) defaultNULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB

mysql> select@@global.tx_isolation, @@tx_isolation;+-----------------------+-----------------+

| @@global.tx_isolation | @@tx_isolation |

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

| REPEATABLE-READ | REPEATABLE-READ |

+-----------------------+-----------------+实验一：

t Session A Session B|

|START TRANSACTION; START TRANSACTION;|

| SELECT *FROM t_bitfly;| empty set

|INSERT INTO t_bitfly| VALUES (1, 'a');|

| SELECT *FROM t_bitfly;| empty set

|COMMIT;|

| SELECT *FROM t_bitfly;| empty set

| INSERT INTO t_bitfly VALUES (1, 'a');| ERROR 1062 (23000):| Duplicate entry '1' for key 1v (shit, 刚刚明明告诉我没有这条记录的)

如此就出现了幻读，以为表里没有数据，其实数据已经存在了，傻乎乎的提交后，才发现数据冲突了。

实验二：

t Session A Session B|

|START TRANSACTION; START TRANSACTION;|

| SELECT *FROM t_bitfly;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | a |

| +------+-------+

|INSERT INTO t_bitfly| VALUES (2, 'b');|

| SELECT *FROM t_bitfly;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | a |

| +------+-------+

|COMMIT;|

| SELECT *FROM t_bitfly;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | a |

| +------+-------+

| UPDATE t_bitfly SET value='z';| Rows matched: 2 Changed: 2 Warnings: 0

|(怎么多出来一行)|

| SELECT *FROM t_bitfly;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | z |

| | 2 | z |

| +------+-------+

本事务中第一次读取出一行，做了一次更新后，另一个事务里提交的数据就出现了。也可以看做是一种幻读。

当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

再看一个实验，要注意，表t_bitfly里的id为主键字段。

实验三：

t Session A Session B|

|START TRANSACTION; START TRANSACTION;|

| SELECT *FROM t_bitfly| WHERE id<=1

|FOR UPDATE;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | a |

| +------+-------+

|INSERT INTO t_bitfly| VALUES (2, 'b');| Query OK, 1row affected|

| SELECT *FROM t_bitfly;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | a |

| +------+-------+

| SELECT *FROM t_bitfly;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | a |

| +------+-------+

|COMMIT;|

| SELECT *FROM t_bitfly;| +------+-------+

| | id | value |

| +------+-------+

| | 1 | a |

| +------+-------+

可以看到，用id<=1加的锁，只锁住了id<=1的范围，可以成功添加id为2的记录，添加id为0的记录时就会等待锁的释放。

实验四：一致性读和提交读

t Session A Session B|

|START TRANSACTION; START TRANSACTION;|

| SELECT *FROM t_bitfly;| +----+-------+

| | id | value |

| +----+-------+

| | 1 | a |

| +----+-------+

|INSERT INTO t_bitfly| VALUES (2, 'b');|COMMIT;|

| SELECT *FROM t_bitfly;| +----+-------+

| | id | value |

| +----+-------+

| | 1 | a |

| +----+-------+

| SELECT *FROM t_bitfly LOCK IN SHARE MODE;| +----+-------+

| | id | value |

| +----+-------+

| | 1 | a |

| | 2 | b |

| +----+-------+

| SELECT *FROM t_bitfly FOR UPDATE;| +----+-------+

| | id | value |

| +----+-------+

| | 1 | a |

| | 2 | b |

| +----+-------+

| SELECT *FROM t_bitfly;| +----+-------+

| | id | value |

| +----+-------+

| | 1 | a |

| +----+-------+

如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。

本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据(提交读)。

MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

总结:

四个级别逐渐增强，每个级别解决一个问题。事务级别越高,性能越差,大多数环境read committed 可以用.记住4个隔离级别的特点(上面的例子);

weixin_39959335

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql事务隔离级别怎么设置_Mysql事务隔离级别

一、事务的四大特性(简称ACID)数据库如果支持事务的操作，那么就具备以下四个特性：1、原子性(Atomicity)事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。2、一致性(Consistency)事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。3、隔离性(Isolation)一个事务的执行不能被其他事务干扰。4、持续性/永久性(...
复制链接

扫一扫