14.3.2 InnoDB Transaction Model InnoDB 事务模型
14.3.2.1 Transaction Isolation Levels 事务隔离级别
14.3.2.2 autocommit, Commit, and Rollback
14.3.2.3 Consistent Nonlocking Reads 一直非堵塞读
14.3.2.4 Locking Reads 锁定读
在 InnoDB 事务模型里, 目的是结合一个多版本数据库具有2阶段锁定的最好的特性的结合
InnoDB 执行锁定在记录行上 ,查询非锁定一致性读 默认,
类似Oracle数据库的风格。
lock information 在InnoDB 是被存储在有效空间的, 不需要锁升级。
通常情况下,多个用户是允许锁定InnoDB 表的每一行,或者任何随机子集的行,不会造成InnoDB 内存耗尽。
14.3.2.1 Transaction Isolation Levels 事务隔离级别
事务隔离 是数据库处理的基础之一, 事务隔离是ACID 中I的缩写。
事务隔离是很好的调整性能和可靠性的设置,一致性,和重复性的结果
当多个事务再做变化和执行查询在同一时间。
InnoDB 提供所有4种事务隔离级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and
SERIALIZABLE. 默认的隔离级别是REPEATABLE READ. (RR)
一个用户可以改变单个session的隔离级别或者所有随后的连接使用SET TRANSACTION statement.
来设置server的默认的隔离级别对于所有的连接,使用--transaction-isolation选项在命令行
或者一个选项文件。
InnoDB 支持每个事务隔离级别使用不同的锁定策略。
你可以强制一个高程度的一致性读 使用默认的 REPEATABLE READ level
对于关键数据的操作, ACID 规则是重要的, 或者你可以放宽一致性规则 使用 READ COMMITTED or even
READ UNCOMMITTED
比如大部分报告 精确的一致性和可重复的结果是不重要的 相比lock的最小化负担。
SERIALIZABLE 强制更严格的规则 相比 REPEATABLE READ
并主要用于特定的场景,比图 XA事务和并发和死锁故障排除。
下面的描述 MySQL 如何支持不同的事务隔离,该列表时从最常用的级别到最少使用的级别:
1. REPEATABLE READ：
这是InnoDB 默认的隔离级别, 对于一致性读,这里有一个重要的不同相比READ COMMITTED isolation level
所有的一致性读在相同的是事务里读取有first read 创建的快照。
这意味着如果你执行几种普通的(非锁定的) SELECT 语句 在相同的事务里,
那些SELECT 语句是一致性的 。
对于lodcking reads（SELECT with FOR UPDATE or LOCK IN SHARE MODE）,
UPDATE,和DELETE 语句,locking 依赖是否语句使用一个唯一索引进行一个唯一的搜索条件,
或者一个range-type 搜索条件。对于一个unique index 进行一个unique 搜索条件,
InnoDB 只锁定index record ,不是在它之前的区间。
对于其他的搜索条件,InnoDB locks 扫描的index range，使用一个gap locks 或者一个Next-key locks
来堵塞其他会话插入到这个区间。
/*****************************示例:
CREATE TABLE `t5` (
`sn` int(11) NOT NULL AUTO_INCREMENT,
`id` int(11) DEFAULT NULL,
`info` varchar(40) DEFAULT NULL,
PRIMARY KEY (`sn`)
)
Sesson 1:
mysql> select * from t5;
+----+------+------+
| sn | id | info |
+----+------+------+
| 1 | 1 | a1 |
| 2 | 5 | a5 |
| 3 | 10 | a10 |
| 4 | 15 | a15 |
| 5 | 20 | a20 |
| 6 | 25 | a25 |
| 7 | 30 | a30 |
| 8 | 35 | a35 |
| 9 | 40 | a40 |
+----+------+------+
9 rows in set (0.00 sec)
创建普通的B树索引:
mysql> create index t5_idx1 on t5(id);
mysql> select * from t5 where id=10 for update;
+----+------+------+
| sn | id | info |
+----+------+------+
| 3 | 10 | a10 |
+----+------+------+
1 row in set (0.00 sec)
Session 2:
mysql> insert into t5(id,info) values(8,'a8');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(9,'a9');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
mysql>
mysql> insert into t5(id,info) values(7,'a7');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(6,'a6');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(5,'a5');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(4,'a4');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5(id,info) values(3,'a3');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5(id,info) values(2,'a2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5(id,info) values(1,'a1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5(id,info) values(10,'a10');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(11,'a11');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(12,'a12');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(13,'a13');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(14,'a14');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t5(id,info) values(15,'a15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5(id,info) values(16,'a16');
Query OK, 1 row affected (0.00 sec)
锁的区间是[5,15)
创建唯一索引:
Session 1:
mysql> show create table t5;
+-------
+----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-------------------------------------------+
| Table | Create Table
|
+-------
+----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-------------------------------------------+
| t5 | CREATE TABLE `t5` (
`sn` int(11) NOT NULL AUTO_INCREMENT,
`id` int(11) DEFAULT NULL,
`info` varchar(40) DEFAULT NULL,
PRIMARY KEY (`sn`),
UNIQUE KEY `t5_idx1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+-------
+----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t5 where id=10 for update;
+----+------+------+
| sn | id | info |
+----+------+------+
| 3 | 10 | a10 |
+----+------+------+
1 row in set (0.00 sec)
Session 2:
mysql> insert into t5(id,info) values(10,'a10');
^[[AERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
只锁ID=10
READ COMMITTED:
一个和Oracle很像的隔离级别尊重一致性读(非堵塞)：
每个一致性读, 甚至在相同的会话里,设置和读取它自己的新鲜的快照。
对于锁定读( SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE 语句,
和DELETE 语句,InnoDB 只锁定index records,不是在它们之前的区间,从而允许新的记录自由的插入紧领着
锁定的记录。
注意:
在MySQL 5.6,当READ COMMITTED 隔离级别被使用, 或者过时的innodb_locks_unsafe_for_binlog
系统变量被启用,那么久没有区间锁除了外键越苏检查和重复key检查,
同时, record locks对于不匹配的记录是被释放在MySQL 已经评估WHERE 条件。
READ UNCOMMITTED
SELECT 语句是以一种非锁定方式进行, 但是一个可能的早期版本的记录可能被使用。
因此, 使用这个隔离级别, 这种读是不一致的,这个也会被称为脏读。
SERIALIZABLE 序列化:
这个级别很像REPEATABLE READ,但是InnoDB 隐式的转换所有的普通的SELECT 语句为 SELECT ... LOCK IN
SHARE MODE
如果自动提交被关闭,如果自动提交开启,
SELECT 是它自己的事务。 因此它是只读和序列化如果作为一个一致(非锁定)读和
不需要block 其他的事务