数据库事务隔离级别

在并发的场景中,为了保证数据的一致性我们会在数据库中使用事务。然而在强一致性与性能上则需要根据具体业务来取舍,所以一般数据库提供了四种事务隔离级别:

  1. 读未提交(Read Uncommitted)
  2. 读提交(Read Committed)
  3. 可重复读(Repeatable Read)
  4. 序列化(Serializable)

由于日常工作中使用事务比较频繁,遂在此作一下总结

在了解这四种事务隔离级别之前,需要了解如下概念:

更新丢失(Lost Update): 两个事务同时修改一行数据,其中一个事务的更新被另外一个事务的更新覆盖了

脏读(Dirty Reads): 一个事务在修改(未提交)数据时,被另一个事务读到了修改后的数据

不可重复读(Non-repeatable Reads): 一个事务先读取了某个数据,然后另外一个事务修改(并提交)了数据后,这个事务再一次读取之前的数据时,得到的是不一样的值

幻读(Phantom Reads): 一个事务预先读取到或者未读取到某条数据,然后另外一个事务删除或新增这条数据(并提交)后,这个事务再一次读取之前的数据时未读到或读到数据。

以上概念对应在四种隔离级别中:

读未提交(Read Uncommitted) 在该级别下的事务允许脏读,但不允许更新丢失:当一个事务开始修改某个值时,其他的事务可以读这个值,但是无法修改这个值。

以mysql为例,将事务隔离级别设置为Read Uncommitted

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

测试表格式:

mysql> desc user;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255)     | NO   |     | NULL    |                |
| balance | int(10) unsigned | NO   |     | 0       |                |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

测试数据:

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |     100 |
+----+--------+---------+
1 row in set (0.01 sec)

打开一个新的会话:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | test | Sleep   |   41 |       | NULL             |
|  2 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

在会话1中使用事务修改数据,但不提交:

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

mysql> update user set balance = balance - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在会话2中查询(脏读):

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      95 |
+----+--------+---------+
1 row in set (0.00 sec)

在会话2中修改(不会更新丢失):

mysql> update user set balance = balance + 5 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

因为会话1对该条数据的操作还未结束,所以无法得到写锁。

读提交(Read Committed) 在该级别下允许不可重复读,但不允许脏读。

回滚之前会话1中的操作,设置事务隔离级别为读提交:

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

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |     100 |
+----+--------+---------+
1 row in set (0.00 sec)

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

在会话1中使用事务修改数据,但不提交:

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

mysql> update user set balance = balance - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在会话2中修改事务隔离级别为读提交并读取数据(不允许脏读):

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

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |     100 |
+----+--------+---------+
1 row in set (0.00 sec)

回滚会话1,新建事务读取内容:

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

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

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |     100 |
+----+--------+---------+
1 row in set (0.00 sec)

在会话2中修改这个数据:

mysql> update user set balance = balance - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在会话1中再次读取(不可重复读):

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      95 |
+----+--------+---------+
1 row in set (0.00 sec)

读取到了与之前不一样的值。

可重复读(Repeatable Read) 禁止不可重复读,但可能出现幻读。

回滚会话1,设置隔离级别为可重复读:

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

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

在会话1的事务中读取数据:

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

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      95 |
+----+--------+---------+
1 row in set (0.00 sec)

在会话2中修改事务隔离级别,修改此数据:

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

mysql> update user set balance = balance - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      90 |
+----+--------+---------+
1 row in set (0.00 sec)

在会话1中查询(可重复读):

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      95 |
+----+--------+---------+
1 row in set (0.00 sec)

在会话2中新增一条数据:

mysql> insert into user (name, balance) values ('root', 100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      90 |
|  2 | root   |     100 |
+----+--------+---------+
2 rows in set (0.00 sec)

在会话1中查询(幻读):

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      95 |
+----+--------+---------+
1 row in set (0.00 sec)

序列化(Serializable) 在此级别下,修改数据的操作必须是位于事务队列的最前端(所以个人觉得叫做队列化更合适),事务化级别最高,但是带来的资源竞争也更多。

在会话1中设置事务级别并打开会话查询数据:

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

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

mysql> select * from user;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | eslizn |      90 |
|  2 | root   |     100 |
+----+--------+---------+
2 rows in set (0.00 sec)

在会话2中删除一条数据:

mysql> delete from user where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

因为无法获得锁而操作失败,故不会造成幻读。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏我的博客

centos搭建svn使用mysql管理认证

1、安装 yum install subversion 安装ssl,mysql认证模块等(如果使用http或者svn访问就不用ssl了) yum install...

30650
来自专栏ytkah

如何用SQL命令行工具删除dedecms指定id文章

  用dedecms采集时标题字段设置错了,出现了注释符号<!---->,导致后台的文章列表出现错误,也无法直接从列表中删除,可以远程登录数据库去操作,这个相对...

28860
来自专栏解Bug之路

MySql之自动同步表结构

在开发过程中,由于频繁的修改数据库的字段,导致rd和qa环境的数据库表经常不一致。 而由于这些修改数据库的操作可能由多个rd操作,很难一次性收集全。人手工去和...

13110
来自专栏python读书笔记

python 数据分析基础 day10-sqlite3一、使用逻辑二、创建数据库及表三、插入记录四、更新记录五、获取记录

今天是读《python数据分析基础》的第10天,今天的笔记内容是利用sqlite3模块对数据库文件进行操作。这个模块的笔记主要分为五个板块:sqlite3的使用...

31760
来自专栏撸码那些事

【眼见为实】自己动手实践理解 READ COMMITTED && MVCC

【眼见为实】自己动手实践理解READ UNCOMMITED && SERIALIZABLE

46770
来自专栏撸码那些事

【眼见为实】自己动手实践理解数据库READ COMMITTED &amp;&amp; MVCC

14630
来自专栏自由而无用的灵魂的碎碎念

Oracle:创建db_link

global_name也就是数据库的全局数据库名,可已使用select * from global_name;查询:

9920
来自专栏存储技术

MySQL加锁范围分析

最近,遇到了一个关于mysql 加锁的问题,将当时的情形简化如下,有一个index_test表,表结构如下所示:

64260
来自专栏数据和云

深入内核:Oracle数据库里SELECT操作Hang解析

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

400100
来自专栏WindCoder

通过Mysql数据库批量修改WordPress的URL地址

更换个域名,文章的地址有时不会跟着改变,之前遇到过一次,今天又遇到了,就暂且记录一个以备日后使用,由于网上资源很多,就不在写明原创作者了O(∩_∩)O~(主要是...

42920

扫码关注云+社区

领取腾讯云代金券