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

(7)MySQL进阶篇SQL优化(InnoDB锁-事务隔离级别 )1.概述 在我们在学习InnoDB锁知识点之前,我觉得有必要让大家了解它的背景知识,因为这样才能让我们更系统地学习好它。InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION

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

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

2session_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

 

2session_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

2session_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机制,insertupdatedelete会更新版本号,是当前读(当前版本)。

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为例)

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/6216.html

(0)
上一篇 2023-04-14 20:30
下一篇 2023-04-15

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注