MySQL 事务详解

作者: 大富帅 | 来源:发表于2018-02-11 12:43 被阅读62次

事务详解

注意：MyISAM 不支持事务，InnoDB支持事务，所以所有关于事务，隔离级别，排它锁，共享锁， MVCC (当前读 VS 快照读)， select .. for update (排它锁)， select .. lock in share mode(共享锁) 都是针对InnoDB，也是Innodb拥有事务特性的所有描述，也是Innodb比myisam好的地方。

在innodb里面，所有的活动都是运行在事务里面的，如果autocommit=1，每个SQL语句都是一个事务，innodb默认autocommit=1的，意思就是MySQL会在每个语句执行的时候自动提交事务，当然是语句没有报错，如果报错了，那就会自动回滚rollback。
所以在autocommit开启的情况下，每个insert ，update ，delete，都是一个事务，只不过没有显示声明开启事务。

当然在autocommit开的情况下，你又想多个语句凑成一个事务，就要显式的声明事务了，使用begin, 或者start transaction, 事务结尾使用commit, 或者rollback。

如果autocommit=0,关闭了自动提交事务，那么整个对话里的所有语句就处于一个长事务，需要显式的commit或者rollback。

MySQL默认操作模式就是autocommit自动提交模式。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

通过以下命令可以查看当前autocommit模式

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.04 sec)

事务隔离级别详解

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

你可以用下列语句查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

----以上手册中的理论知识;

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

===========================================================================================

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

下面用例子说明各个级别的情况：

① 脏读
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

--------------------- SESSION 1 -------------------------
#设置隔离级别是未提交读 READ UNCOMMITTED
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
# 看到这个session 的隔离级别已经设成了read uncomitted 

mysql> begin;

mysql> select * from goods ;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
+----+--------+-------+

--------------------- SESSION 2 -------------------------
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ  |
+------------------------+

mysql> begin;

mysql> select * from goods where id =1;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |  1000 |
+----+--------+-------+
1 row in set (0.00 sec)

mysql> update goods set stock=20 where id= 1;
# 事务里面更新了id=1的数量是20， 但是还没有commit 事务。我们看下session 1的数据

--------------------- SESSION 1 -------------------------
mysql> select * from goods where id =1;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    20 |
+----+--------+-------+
# session2的修改还没提交事务， session1 就看到外面的修改stock由1000变成了20，这就是脏读。

结论:session 1 在READ-UNCOMMITTED 下读取到session 2 中未提交事务修改的数据.

② 不可重复读
不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

--------------------- SESSION 1 -------------------------
# 设置隔离级别是已提交读 READ COMMITTED 
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
mysql> begin;
mysql> select * from goods where id=1;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
+----+--------+-------+

--------------------- SESSION 2 -------------------------
mysql> begin;
mysql> select * from goods where id=1;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
+----+--------+-------+
mysql> update goods set stock=200 where id=1;
mysql> select * from goods where id=1;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |   200 |
+----+--------+-------+

--------------------- SESSION 1 -------------------------
mysql> select * from goods where id=1;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
+----+--------+-------+
# 这个时候SESSION1 没有读到SESSION 2的未提交读，所以已提交读是没有脏读的情况了！


--------------------- SESSION 2 -------------------------
mysql> commit;
# 提交上面update stock=200的修改

--------------------- SESSION 1 -------------------------
mysql> select * from goods where id=1;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |   200 |
+----+--------+-------+
# 这个时候SESSION 1读到了SESSION 2已经提交的修改， 也就是说SESSION1在事务内，读到同一行的数据，是两个不同状态不同的结果， 这就是不可重复读

结论:session 1 在READ-COMMITTED 下读取到session 2 中已经提交事务修改的数据. 这样session1在同一个事务内不可重复读，因为两次读取同一行的数据已经被改变了。

③ 可重复读
可重复读是指同一个事务里面，读取同一行数据不会因为外部的事务修改了，而改变，也就是同一个事务里面，所有的数据怎么读都是同一个状态的，不受外部影响， 这个也是Innodb默认的事务隔离级别

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+

这个级别的例子就是上面例子2 的不可重复读的修正版，当session2 commit提交事务的时候，修改了商品数量，session 1查询的商品数量还是原来的数量，不会因为session 2提交的修改而改变。

④ 幻读

例子1：隔离级别是可重复读 REPEATABLE READ

--------------------- SESSION 1 -------------------------
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
mysql> begin;

mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  9 | prod19 |  1000 |
| 10 | prod20 |    15 |
+----+--------+-------+
# 一共有三个记录 最大的id是10

--------------------- SESSION 2 -------------------------
mysql> begin;
mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  9 | prod19 |  1000 |
| 10 | prod20 |    15 |
+----+--------+-------+
mysql> insert into goods values ('', 'prod21', 15); 
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  9 | prod19 |  1000 |
| 10 | prod20 |    15 |
| 11 | prod21 |    15 |
+----+--------+-------+
# 添加了一个id=11的记录， 如果此时还没提交commit 事务 session1也做一个添加记录操作

--------------------- SESSION 1 -------------------------

mysql>  insert into goods values ('', 'prod22', 15);
.... 
# 因为session 2还没提交更新， session1执行这个插入会被阻塞，因为表被锁住了， session1不能做任何操作
# 阻塞直到session2commit了或者 报错
# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 锁超时
# 这里的锁的问题我们下面继续讨论，这里先讨论session2提交了的情况。


--------------------- SESSION 2 -------------------------
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
# commit上面的insert 操作

--------------------- SESSION 1 -------------------------
mysql>  insert into goods values (11 , 'prod22', 15);
ERROR 1062 (23000): Duplicate entry '16' for key 'PRIMARY'
# 这里就是幻读了， 刚才查询还没有id=11的记录，怎么插入就告诉我已经存在？ 不是说外部事务的commit不会影响我的事务吗？
# 外部事务的修改，插入数据，在本事务都不会体现和影响。 
# 只是在本事务确实要插入有冲突数据的时候会报错（本来有id11记录，你还要插入id11的记录这能行吗？哪怕你有事务隔绝这个光环）

例子2：隔离级别是可重复读 REPEATABLE READ


--------------------- SESSION 2 -------------------------
mysql> begin;
mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
+----+--------+-------+
mysql> insert into goods values ('', 'prod20', 15); 
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
| 21 | prod20 |    15 |
+----+--------+-------+
mysql> commit;

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

mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
+----+--------+-------+
# session 2提交的插入数据 session 1是看不到的，因为隔离级别是可重复读，不会受外部修改而影响查看数据
mysql> update goods set stock=20 where stock=15;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

#  啊？明明事务看到的stock=15的记录只有1条， 为什么修改会有2rows被修改？ 这就是幻读的体现。

总结：幻读是在可重复读隔离级别下产生的，可重复读级别，虽然外部事务的修改和插入数据不会影响到本事务查看数据，别入外部事务修改和插入了数据，本事务是看不到的。虽然没影响到事务的查看数据，但是，当本事务是需要插入数据和更新数据的时候，就会被外部事务的修改而影响到。比如：外部事务插入了id=11的记录并提交了事务，本事务继续添加id=11的记录会报错。再比如：外部数据插入了N条数量=15的数据并提交，本事务还是看到原来只有一条数量=15的数据，当更新这些数量=15的数据的时候，会突然看到N条数据被更新，这就是幻读。明明看到只有1条，怎么更新就出现N条

select .. for update 锁

锁详解

Select…For Update 语句的语法与select语句相同，只是在select语句的后面加FOR UPDATE [NOWAIT]子句。

该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。

The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction.
You can specify this clause only in a top-level SELECT statement, not in subqueries.

锁例子：（RR隔离级别）

--------------------- SESSION 1 -------------------------
mysql> begin;
mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
|  3 | prod13 |  1000 |
|  4 | prod14 |  1000 |
|  5 | prod15 |  1000 |
+----+--------+-------+
mysql> select * from goods where id <= 3 for update;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
|  3 | prod13 |  1000 |
+----+--------+-------+
# 选定了前三条记录，并且利用select .. for update 锁住了这三条记录。

 --------------------- SESSION 2 -------------------------
mysql> begin;
mysql> update goods set stock = 20 where id=1;
mysql> update goods set stock = 20 where id=2;
mysql> update goods set stock = 20 where id=3;
mysql> update goods set stock = 20 where id=4;
# 更新<=4的数据会一直阻塞，因为这几条数据被锁住了，要等锁住的事务结束才能提交

mysql> update goods set stock = 20 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# >4 的记录更新没有问题，说明没有被锁。

结论： select .. for update 会在事务中锁住被查出来的记录，其他事务修改被锁住的记录会被阻塞到前一个事务结束

例子2：继续上面例子1 的mysql代码（RR隔离级别）

--------------------- SESSION 2 -------------------------
mysql> update goods set stock = 20 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
# 提交事务
mysql> select * from goods;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
|  3 | prod13 |  1000 |
|  4 | prod14 |  1000 |
|  5 | prod15 |    20 |
+----+--------+-------+
# id=5已经被改成了20

--------------------- SESSION 1 -------------------------
mysql> select * from goods ;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
|  3 | prod13 |  1000 |
|  4 | prod14 |  1000 |
|  5 | prod15 |  1000 |
+----+--------+-------+
#这里查看id=5还是1000, 这是正常的，RR级别的可重复读
#但是如果用加锁的读， 就会读到“最新的”“提交”读的结果。
mysql> select * from goods for update;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
|  3 | prod13 |  1000 |
|  4 | prod14 |  1000 |
|  5 | prod15 |    20 |
+----+--------+-------+

mysql> select * from goods lock in share mode;
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  1 | prod11 |    15 |
|  2 | prod12 |  1000 |
|  3 | prod13 |  1000 |
|  4 | prod14 |  1000 |
|  5 | prod15 |    20 |
+----+--------+-------+

结论：如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。
本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据（提交读）。
MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

MySQL 事务详解

事务详解

事务隔离级别详解

select .. for update 锁

锁详解

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

database， mysql