mysql事务隔离级别

最新推荐文章于 2024-09-09 18:00:00 发布

非洲_bear

最新推荐文章于 2024-09-09 18:00:00 发布

阅读量524

点赞数

分类专栏：杂记文章标签： mysql 数据库事务

本文链接：https://blog.csdn.net/zhou_java_hui/article/details/53311883

版权

杂记专栏收录该内容

16 篇文章 0 订阅

订阅专栏

概念

什么是事务

一个程序执行单元，包含一系列操作，要么全部成功，要么全部失败，这就是事务

事务的特性

原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）：持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事务隔离级别

为了解决并发数据操作保证不同事务的正确执行提出了四种隔离级别

未授权读取：也称为读未提交（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

授权读取：也称为读提交（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

实例

可重复读

打开两个cmd，连接mysql数据库，修改隔离级别

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

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

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

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

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

插入

两个cmd同时开启事务，一个进行查询，另一个进行插入语句并且commit，第一个再一次查询，结果不变，插入操作支持可重复读

第一个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from custom;
Empty set (0.02 sec)

mysql> select * from custom;
Empty set (0.00 sec)

第二个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into custom(address) values('a');
Query OK, 1 row affected (0.00 sec)

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

修改

两个cmd同时开启事务，一个进行查询，另一个进行修改并且commit，第一个再一次查询，结果不变，修改操作支持可重复读

第一个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from custom;
+----+---------+---------+-------+-------+------+
| id | address | cusName | email | phone | sex  |
+----+---------+---------+-------+-------+------+
|  1 | a       | NULL    | NULL  | NULL  | NULL |
+----+---------+---------+-------+-------+------+
1 row in set (0.00 sec)

mysql> select * from custom;
+----+---------+---------+-------+-------+------+
| id | address | cusName | email | phone | sex  |
+----+---------+---------+-------+-------+------+
|  1 | a       | NULL    | NULL  | NULL  | NULL |
+----+---------+---------+-------+-------+------+
1 row in set (0.00 sec)

第二个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update custom set cusname='a';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from custom;
+----+---------+---------+-------+-------+------+
| id | address | cusName | email | phone | sex  |
+----+---------+---------+-------+-------+------+
|  1 | a       | a       | NULL  | NULL  | NULL |
+----+---------+---------+-------+-------+------+
1 row in set (0.00 sec)

删除

两个cmd同时开启事务，一个进行查询，另一个进行删除并且commit，第一个再一次查询，结果不变，delete操作支持可重复读
注意，如果是truncate，则会等待其它事务执行完后才能执行，具体原因不明确

第一个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from custom;
+----+---------+---------+-------+-------+------+
| id | address | cusName | email | phone | sex  |
+----+---------+---------+-------+-------+------+
|  1 | a       | NULL    | NULL  | NULL  | NULL |
+----+---------+---------+-------+-------+------+
1 row in set (0.00 sec)

第二个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from custom where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

查询新增修改查询

两个cmd同时开启事务，一个进行查询，另一个进行新增并且commit，第一个再一次查询，结果不变，然后执行更新另一个新增的数据，再一次查询，能够查询到另一个新增的数据，原因不明确

第一个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from custom;
Empty set (0.02 sec)

mysql> select * from custom;
Empty set (0.02 sec)

mysql> update custom set cusname='a' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from custom;
+----+---------+---------+-------+-------+------+
| id | address | cusName | email | phone | sex  |
+----+---------+---------+-------+-------+------+
|  2 | a       | a       | NULL  | NULL  | NULL |
+----+---------+---------+-------+-------+------+
1 row in set (0.00 sec)

第二个cmd
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into custom(address) values('a');
Query OK, 1 row affected (0.00 sec)

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

串行

打开两个cmd，连接mysql数据库，修改隔离级别

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

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

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

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE           |
+------------------------+
1 row in set (0.00 sec)

插入/修改

一个进行查询，另一个进行插入，插入会等待锁，第一个查询commit以后，插入操作才能继续
总之，如果第一个进行全表查询，则第二个cmd进行任何修改操作都需要等第一个提交后才能进行，但是如果只查询特定的行，则第二个cmd可修改其他行而无需等待，可以新增无需等待

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

mysql> select * from custom;
+----+---------+---------+-------+-------+------+
| id | address | cusName | email | phone | sex  |
+----+---------+---------+-------+-------+------+
|  2 | a       | a       | NULL  | NULL  | NULL |
+----+---------+---------+-------+-------+------+
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 custom;
+----+---------+---------+-------+-------+------+
| id | address | cusName | email | phone | sex  |
+----+---------+---------+-------+-------+------+
|  2 | a       | a       | NULL  | NULL  | NULL |
+----+---------+---------+-------+-------+------+
1 row in set (0.00 sec)

mysql> insert into custom(address) values('b');
Query OK, 1 row affected (5.89 sec)

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