(7)MySQL进阶篇SQL优化(InnoDB锁-事务隔离级别 )

时间:2021-10-26 22:41:51

1.概述

在我们在学习InnoDB锁知识点之前,我觉得有必要让大家了解它的背景知识,因为这样才能让我们更系统地学习好它。InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。行级锁与表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题。

2.事务(Transaction)及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
●原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
●一致性(Consistent):在事务开始和结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的,数据都必须保持一致状态,以保持数据的完整性。例如A向B银行转帐,不可能A扣了钱,而B收不到钱。
●隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。隔离性有4个隔离级别:未提交读、已提交读、可重复读和可序列化。
●持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

3.并发事务处理带来的问题

相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况:
●更新丢失(Lost Update):事务A,B都更新同一数据,由于每个事务都不知道其他事务的存在,所以事务A修改完原始数据后,查询看到的数据有可能是事务B修改后的数据,反之亦然。如果一个事务完成并提交之前,另一个事务不能访问同一数据,则可避免此问题。
●脏读(Dirty Reads):事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据。
●不可重复读(Non-Repeatable Reads):事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。
●幻读(Phantom Reads):系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
注意:不可重复读和幻读其实很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表。

4.事务隔离级别(重点)

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式,基本上可分为以下两种:
●一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。
●另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度来看,好象是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制(MultiVersion Concurrency Control,简称MVCC或MCC),也经常称为多版本数据库。
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用程序对读一致性和事务隔离程度的要求也是不同的,比如许多应用程序对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。为了解决“隔离”与“并发”的矛盾,ISO/ANSI SQL92定义了4种事务隔离级别:

事务隔离级别

脏读

不可重复读

幻读

未提交读(Read uncommitted)

已提交读(Read committed)

可重复读(Repeatable read)

可序列化(Serializable)

看到表格中4种事务隔离级别特性,相信大家起初都跟我一样一头雾水,不知道如何下手,后面我通过度娘搜索相关资料了解一下,用订单表(orders)通过以下示例跟大家一一解释。

4.1未提交读(Read uncommitted)

●两个事务,一个事务未提交的数据,另一个事务可以读取到,这里读取到的数据叫做“脏数据”。
●这种隔离级别最低,这种级别一般是在理论上存在,实际应用中数据库隔离级别一般都高于该级别。
MySQL默认的事务隔离级别为可重复读(Repeatable read),输入以下命令即可知道:

-- 查看事务隔离级别模式
SHOW VARIABLES LIKE 'transaction_isolation%';
-- 5.0版本
SELECT @@tx_isolation
-- 8.0版本
SELECT @@transaction_isolation

(7)MySQL进阶篇SQL优化(InnoDB锁-事务隔离级别 )
示例:

session_1

session_2

(1)将当前会话事务隔离级别修改为未提交读并查询订单金额。

(1)将当前会话事务隔离级别修改为未提交读并更新订单金额。

-- 将默认事务隔离级别修改为未提交读

MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

-- 将默认事务隔离级别修改为未提交读

MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 更新订单金额

MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

(2)session_2会话事务未提交,当前会话就可以查询到session_2已更新好的订单金额了,这就是脏数据。

(2)查询订单金额。

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 95000.000000 |

+--------------+

1 row in set (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 95000.000000 |

+--------------+

1 row in set (0.00 sec)

(3)当前会话事务因某些原因撤销。

-- 回滚

MySQL [(none)]> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

(3)当前会话再更新一次订单金额。

(4)查询订单初始金额。

-- 更新订单金额

MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

(4)查询订单金额。

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 95000.000000 |

+--------------+

1 row in set (0.00 sec)

(5)当前会话更新订单金额后,本来金额应为90000才对,结果却是95000,从这就可以验证,当前会话未更新之前第二次查询到的订单金额95000就是脏数据了。

 

注意:这里需要注意一点是,修改事务隔离级别是可以设置为全局跟当前会话范围的:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
事务隔离级别的作用范围分为两种:
●全局级:对所有的会话有效。
●会话级:只对当前的会话有效。
所以上述示例修改当前会话事务隔离级别模式并不是全局生效的,而是只针对当前会话生效。例如session_1修改事务隔离级别为未提交读模式,并不会对session_2、session_3...会话事务生效,而当前会话结束后,下次开启新的会话时默认事务隔离级别还是可重复读模式,除非修改为全局范围。

4.2已提交读(Read committed)

●Oracle默认隔离级别。
●两个事务,数据只有一个事务提交了后,另一个事物才能读取到,即:对方事物提交之后的数据,当前事物才能读取到。
●已提交读隔离级别高于未提交读。这种级别可以避免“脏数据”,但是会导致“不可重复读取”,存在幻读问题。
示例:

session_1

session_2

(1)将当前会话事务隔离级别修改为已提交读并查询订单金额。

(1)将当前会话事务隔离级别修改为已提交读并更新订单金额。

-- 将默认事务隔离级别修改为已提交读

MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

-- 将默认事务隔离级别修改为已提交读

MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 更新订单金额

MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

(2)session_2会话事务未提交,当前会话查询到的订单金额并没有改变,在该模式下解决了之前存在脏数据的问题了。

(2)查询订单金额。

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 95000.000000 |

+--------------+

1 row in set (0.00 sec)

(3)当前会话提交事务。

-- 提交事务

MySQL [(none)]> COMMIT;

Query OK, 0 rows affected (0.01 sec)

(3)再次查询订单金额。

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 95000.000000 |

+--------------+

1 row in set (0.00 sec)

4.3可重复读(Repeatable read)

●MySQL默认隔离级别。
●两个事务,一个事务提交之后的数据,另一个事务读取不到。
●这种隔离级别高于读已提交,可以避免“不可重复读取”,但是会导致“幻像读”。
示例:

session_1

session_2

(1)将当前会话事务隔离级别修改为可重复读并查询订单金额。

(1)将当前会话事务隔离级别修改为可重复读并更新订单金额。

-- 将默认事务隔离级别修改为可重复读MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

-- 将默认事务隔离级别修改为可重复读MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 更新订单金额

MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

(2)session_2会话事务未提交,当前会话查询到的订单金额并没有改变,在该模式下也解决了脏数据的问题了。

(2)查询订单金额。

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 95000.000000 |

+--------------+

1 row in set (0.00 sec)

(3)当前会话提交事务。

-- 提交事务

MySQL [(none)]> COMMIT;

Query OK, 0 rows affected (0.00 sec)

(3)查询订单金额,三次查询数据结果都一致,没有出现不可重读的情况。之所以数据的一致性得到保证,原因是:在可重复读的隔离级别下,MySQL采用的是MVCC机制(不了解的可以点击上文链接了解下),select操作不会更新版本号,是快照读(历史版本)。

(4)查询订单金额。

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 95000.000000 |

+--------------+

1 row in set (0.00 sec)

(4)在当前会话再更新订单金额。

-- 更新订单金额

MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

(5)查询订单金额,明明只减5000金额,订单金额却变成90000(产生幻读),原因是:在可重复读的隔离级别下,MySQL采用的是MVCC机制,insert、update和delete会更新版本号,是当前读(当前版本)。

(5)尝试再更新订单金额,会提示失败,这说明可重复读隔离级别下MySQL已经不出现幻读的情况了。

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+--------------+

| OrderAmount |

+--------------+

| 90000.000000 |

+--------------+

1 row in set (0.00 sec)

-- 更新订单金额

MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4.4可序列化(Serializable)

Serializable完全串行化的读,每次读都需要获得表级共享锁,读写操作相互互斥,这样可以更好的解决数据一致性的问题,但是同样会大大的降低数据库的实际吞吐性能。所以该隔离级别因为并发性比较低、损耗太大,一般很少在开发中使用。
●MySQL中事务隔离级别为Serializable时会锁表,因此不可能出现脏读数据、不可重复读、幻读的情况。
●两个事务,当一个事务操作数据库时,另一个事务只能排队等待。
●这种级别可以避免“幻像读”,每一次读取的都是数据库中真实存在数据,多个事务之间串行,而不并发。
●这种隔离级别很少使用,吞吐量太低,用户体验差。
示例:

session_1

session_2

(1)将当前会话事务隔离级别修改为可序列化。

(1)将当前会话事务隔离级别修改为可序列化并查询订单金额。

-- 将默认事务隔离级别修改为可序列化

MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 将默认事务隔离级别修改为可序列化

MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Query OK, 0 rows affected (0.00 sec)

-- 开启事务

MySQL [(none)]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

-- 查询订单金额

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

+---------------+

| OrderAmount  |

+---------------+

| 100000.000000 |

+---------------+

1 row in set (0.00 sec)

(2)插入一条订单数据(其实已锁住订单表了)。

-- 插入订单数据

MySQL [(none)]> INSERT INTO goods.orders(OrderAmount) VALUES (5000);

Query OK, 1 row affected (0.00 sec)

(2)查询订单数据,会报错,那是因为订单表已被锁了。

MySQL [(none)]> SELECT OrderAmount FROM goods.orders;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

参考文献:
深入浅出MySQL大全
数据库事务(以MySQL8.0.11为例)