mysql事物

前言：通过本文你将了解到事物隔离级别带来的问题，innodb是怎么解决幻读，mysql事物特性，并配有案例说明。

 

事务是一组访问并可能更新数据库内容的SQL组成的执行单元，是恢复和并发控制的基本单位。

支持事物：Innodb、NDBCluster、TokuDB

不支持事物：Myisam、MEMORY/HEAP

 

事务具备4个基本属性：

Atomic,同一个事物里，要么都提交，要么都回滚。

Consistency,在事物开始之前和事物结束以后，数据库的完整性约束没有被破坏。

Isolation,并发事物间的数据是彼此隔离的

Durabiliy,事物提交后，所有结果必须持久化

 

在mysql中怎样开始或回滚一个事务呢？

1、显示开始，START TRANSACTION WITH CONSISTENT SNAPSHOT;  BEGIN; 

2、隐士开始，BEGIN / START TRANSACTION / SET AUTOCOMMIT = 1 / 其他非事务语句（DDL/DCL）会导致之前没提交的事务提交。

3、显示回滚，ROLLBACK

4、隐士回滚，连接断开 / 超时

 

在修改事务隔离级别时需要注意set global  xxx;这时只针对新连接生效，当前连接不生效（如果有连接池的话就麻烦了，连接池都是长连接）

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

mysql> show session variables like "tx_isolation";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

在修改事物隔离级别时不加global和session，下一个事物才开始生效。set session xxx；直接生效。（mysql中默认是RR隔离级别）

 

如果没有事物控制的话，那么并发读写数据库会有什么隐患？ 

脏读：一个事物按相同的查询条件读取以前检索过的数据，却发现其他事物更新后达到了满足其查询条件的旧数据（此时它还未提交），这种现象称为“脏读”

不可重复读：一个事物按相同的查询条件读取以前检索过的数据，却发现其他事物更新后达到了满足其查询条件的旧数据（此时它已被提交），这种现象称为“不可重复读”

幻读：一个事物按相同的查询条件读取以前检索过的数据，却发现其他事物插入了满足其查询条件的新数据（此时它已被提交），这种现象称为“幻读”

 

脏读，在一个事物里面读取到其他事物未提交的数据：

不可重复读，在一个事物里面读取到其他事物已经提交的修改的数据：

幻读，在一个事物里面读取到其他事物已经提交的新增的数据：

mysql的4种隔离级别：

1. read uncommitted隔离级别最低，允许脏读，允许事物查看其它事物所进行的未提交更改

2.read committed允许幻读，允许事物查看其它事物所进行的已提交更改

3.repeatable read消除了脏读、不可重复读、幻读保证了事物一致性（innodb特定条件下可能发生幻读，后面会讲到）

4.serializable串行化读，每次读都需要获得表级别共享锁，读写间互相都会阻塞

 

例如有t1表：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t1;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  0 |    0 |    0 |
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
+----+------+------+
4 rows in set (0.00 sec)

读未提交，read uncommitted，RU

读已提交，read committed,RC

RC下的幻读：（此处加for update 表示即便是 RC隔离级别，但是可以读取当前数据的最新版本）

可重复读，repeatable read，RR

在RR隔离级别下，没有幻读。

innodb在RR级别下引入了gap lock来解决幻读，例如：select * from t1 where c2 =2 for update;这样会将索引记录上面1-3（不包括1和3）这个范围的gap全锁定来避免幻读：

加for update 两个作用1：表明要读这行数据的当前最新数据，2：把这行数据加上排它锁。

串行，serializable

可通过innodb_lock_waits查看发生锁等待信息：

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+------------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id      | blocking_trx_id | blocking_lock_id |
+-------------------+------------------------+-----------------+------------------+
| 421424334461664   | 421424334461664:31:3:4 | 4910            | 4910:31:3:4      |
+-------------------+------------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

 

最后我们再说下 InnoDB semi-consitent read,innodb的半一致性读，在innodb特殊实现的机制，主要目的是提升并发效率。update语句如果读到一行已经加锁的记录，此时InnoDB返回该记录最新版本，再次判断此版本是否满足update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)  #这种情况只发生在RC或 RR+innodb_locks_susafe_for_binlog=1环境下，所以说 RR+innodb_locks_susafe_for_binlog=1时存在幻读可能。

可以这样理解：假设这个表有10万行数据，对一个表进行更新，没有索引，正常情况下会把所有数据全部锁定，但是另一个会话想要对这个表update 更新，这个update 可能只影响两行数据，这时候这个update可以被执行，不受全表锁限制，仅仅只针对update生效，其他SQL不会生效。