事务具有ACID四种特性。
但是Isolation并发可能引起如下问题:
1.脏读
允许读取到未提交的脏数据。
2.不可重复读
如果你在时间点T1读取了一些记录,在T2时再想重新读取一次同样的这些记录时,这些记录可能已经被改变、或者消失不见。
3.幻读
解决了不重复读,保证了同一个事务里,查询的结果都是事务开始时的状态(一致性)。但是,如果另一个事务同时提交了新数据,本事务再更新时,就会“惊奇的”发现了这些新数据,貌似之前读到的数据是“鬼影”一样的幻觉。
由ANSI/ISO定义的SQL-92标准定义的四种隔离级别
1.Read Uncommitted
2.Read Committed
3.Repeatable Read
4.Serializable
隔离解别 | 脏读 | 不可重复读 | 幻读 |
Read Uncommitted | Y | Y | Y |
Read Committed | N | Y | Y |
Repeatable(default) | N | N | Y |
Serializable | N | N | N |
下面用Mysql数据库做一些小实验
Mysql 版本号
- mysql> select version();
- +------------+
- | version() |
- +------------+
- | 5.1.52-log |
- +------------+
- 1 row in set (0.00 sec)
mysql> select version(); +------------+ | version() | +------------+ | 5.1.52-log | +------------+ 1 row in set (0.00 sec)
查看InnoDB存储引擎 系统级的隔离级别 和 会话级的隔离级别
- mysql> select @@global.tx_isolation,@@tx_isolation;
- +-----------------------+-----------------+
- | @@global.tx_isolation | @@tx_isolation |
- +-----------------------+-----------------+
- | REPEATABLE-READ | REPEATABLE-READ |
- +-----------------------+-----------------+
- 1 row in set (0.00 sec)
mysql> select @@global.tx_isolation,@@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
更改会话级的隔离级别
- Session 1:
- mysql> set session tx_isolation='read-uncommitted';
- Query OK, 0 rows affected (0.00 sec)
- mysql> select @@global.tx_isolation,@@tx_isolation;
- +-----------------------+------------------+
- | @@global.tx_isolation | @@tx_isolation |
- +-----------------------+------------------+
- | REPEATABLE-READ | READ-UNCOMMITTED |
- +-----------------------+------------------+
- 1 row in set (0.00 sec)
- Session 2:
- mysql> select @@global.tx_isolation, @@tx_isolation;
- +-----------------------+-----------------+
- | @@global.tx_isolation | @@tx_isolation |
- +-----------------------+-----------------+
- | REPEATABLE-READ | REPEATABLE-READ |
- +-----------------------+-----------------+
- 1 row in set (0.00 sec)
Session 1: mysql> set session tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation,@@tx_isolation; +-----------------------+------------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+------------------+ | REPEATABLE-READ | READ-UNCOMMITTED | +-----------------------+------------------+ 1 row in set (0.00 sec) Session 2: mysql> select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
更改系统级的隔离级别
- Session 1:
- mysql> set global tx_isolation='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)
- Session 2:
- mysql> select @@global.tx_isolation, @@tx_isolation;
- +-----------------------+-----------------+
- | @@global.tx_isolation | @@tx_isolation |
- +-----------------------+-----------------+
- | READ-UNCOMMITTED | REPEATABLE-READ |
- +-----------------------+-----------------+
- 1 row in set (0.00 sec)
Session 1: mysql> set global tx_isolation='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) Session 2: mysql> select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | READ-UNCOMMITTED | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
关闭SQL语句的自动提交
- mysql> set autocommit=off;
- Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=off; Query OK, 0 rows affected (0.00 sec)
查看SQL语句自动提交是否关闭
- mysql> show variables like 'autocommit';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | autocommit | OFF |
- +---------------+-------+
- 1 row in set (0.00 sec)
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec)
建立实验表
- mysql> create table tao (col1 tinyint unsigned, col2 varchar(20), primary key(col1));
- Query OK, 0 rows affected (0.08 sec)
- mysql> show create table tao \G;
- *************************** 1. row ***************************
- Table: tao
- Create Table: CREATE TABLE `tao` (
- `col1` tinyint(3) unsigned NOT NULL DEFAULT '0',
- `col2` varchar(20) DEFAULT NULL,
- PRIMARY KEY (`col1`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
mysql> create table tao (col1 tinyint unsigned, col2 varchar(20), primary key(col1)); Query OK, 0 rows affected (0.08 sec) mysql> show create table tao \G; *************************** 1. row *************************** Table: tao Create Table: CREATE TABLE `tao` ( `col1` tinyint(3) unsigned NOT NULL DEFAULT '0', `col2` varchar(20) DEFAULT NULL, PRIMARY KEY (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
演示脏读
更改隔离级别为Read Committed后,不存在脏读的问题。
- mysql> set global tx_isolation='read-committed';
- Query OK, 0 rows affected (0.00 sec)
- mysql> set session tx_isolation='read-committed';
- Query OK, 0 rows affected (0.00 sec)
mysql> set global tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) mysql> set session tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec)
演示不可重复读
更改隔离级别为Repeatable Read后,不存在不可重复读的问题。
- mysql> set global tx_isolation='repeatable-read';
- Query OK, 0 rows affected (0.00 sec)
- mysql> set session tx_isolation='repeatable-read';
- Query OK, 0 rows affected (0.00 sec)
mysql> set global tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> set session tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)
幻读
更改隔离级别为完全串行化 Serializable 后,不存在幻读的问题。
- mysql> set global tx_isolation='serializable';
- Query OK, 0 rows affected (0.00 sec)
- mysql> set session tx_isolation='serializable';
- Query OK, 0 rows affected (0.00 sec)
mysql> set global tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) mysql> set session tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec)
在这种情况下,只允许一个事务在执行,其它事务必须等待这个事务执行完后才能执行。没有并发,只是单纯的串行。
相关推荐
Mysql事务隔离级别.docx
事务隔离级别 课程目标 了解 —— 事务隔离级别的概念; 理解 —— 事务隔离的四种级别; 掌握 —— 事务隔离级别的设置; 事务隔离级别 事务隔离:每一个事务都有一个所谓的隔离级,它定义了用户彼此之间隔离和交互...
【Java面试】请你简单说一下Mysql的事务隔离级别.doc
MySQL事务隔离级别详解.docx
Mysql的四种隔离级别 Mysql的四种隔离级别 Mysql的四种隔离级别
│ 4_InnoDB事务隔离级别详解.mp4 │ 5_InnoDB死锁发生原理和规避.mp4 │ 6_MySQL字符集和排序规则.mp4 │ 作业.docx │ 锁等待分析.txt │ ├─新版MySQL DBA综合实战班 第08天 │ 1_课堂作业讲解.mp4 │ 2_MySQL...
查询:默认事务隔离级别 mysql> select @@tx_isolation;当前会话的默认事务隔离级别 mysql> select @@session.tx_isolation;当前会话的默认事务隔离级别 mysql> select @@global.tx_isolation;全局的事务隔离级别
mysql数据库事务隔离级别借鉴.pdf
MySQL数据库隔离级别
06-VIP-深入理解Mysql事务隔离级别与锁机制.pdf
NULL 博文链接:https://cuishuangjia.iteye.com/blog/964885
# MySQL支持4种事务隔离级别,他们分别是: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. # 如没有指定,MySQL默认采用的是REPEATABLE-READ,ORACLE默认的是READ-COMMITTED log_bin = mysql...
MySQL事务隔离级别详解,这个面试经常会面到,必会呀,哈哈
MySQL数据库的四种隔离级别介绍.docx
MySQL的四种事务隔离级别 1、脏读 2、不可重复读 3、幻读
来自一线大厂的MySQL lock 隔离级别的相关介绍,很赞,强烈推荐
52 MySQL是如何支持4种事务隔离级别的?Spring事务注解是如何设置的?l.pdf
56 MySQL最牛的RR隔离级别,是如何基于ReadView机制实现的?l.pdf
详解Mysql事务隔离级别与锁机制.doc