※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）
【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2
目录：
3、CONCURRENCY AND LOCKING——并发和锁定
5、TRANSACTION ISOLATION LEVELS——事务隔离级别
6、READ UNCOMMITTED ISOLATION LEVEL——读未提交隔离级别
7、READ COMMITTED ISOLATION LEVEL——读已提交隔离级别
8、REPEATABLE READ ISOLATION LEVEL——可重复读隔离级别
9、SERIALIZABLE ISOLATION LEVEL——序列化隔离级别
第十一章：事务
1、TRANSACTIONS——事务
事务：单个动作单元的一组SQL语句
所有语句都应成功完成，否则事务会运行失败（譬如银行转款，转出和收入双方都要完成）
数据库的交易：在需要对数据库进行多次更改的情况下使用事务，并且希望所有这些更改作为一个单元一起成功或失败
事务的属性（ACID Properties）
Atomicity（原子性）
事务像原子一样牢不可破
每个事务都是一个工作单位，不管它含多少语句，要么语句都成功执行且事务被提交，要么事务被退回去所有更改被撤销
Consistency（一致性）
通过使用事务，数据库将始终保持一致的状态，不会出现有订单没有项目的情况
Isolation（隔离性质）
事务相互隔离，当有同样的数据被更改时各自收到保护，所以他们不会互相干扰
如果多个事务想更新相同的数据，受影响的行为会被锁定
因此一次只能一个事务可以更新行，其他事务必须等那个事务完成
Durability（持久性）
一旦事务被提交，事务产生的更改是永久的
如果停电或者系统崩溃，也不会丢失更改内容
2、CREATING TRANSACTION——创建事务
使用COMMIT语句关闭事务
当MySQL看到这个指令，会把所有的更改都写入数据库，如果其中一个更改失败，会自动撤销之前的更改
模拟场景让语句失败
Ctrl+Enter：可以逐行执行脚本
执行3、5行，不执行第8行，直接和服务器断开连接
模拟客户与服务器断开的场景
此时插入失败，任务也被撤回
有时想进行一些错误检查，并手动退回了事务，这种情况下使用ROLLBACK语句
MySQL会装好我们写在事务里的每一条语句，如果语句没有返回错误，就会提交
当我们有INSERT、UPDATE、DELETE语句时，MySQL会先把它们装在事务里，然后自动提交
它由一个自动提交的系统管控
返回autocommit这个变量，自动提交，被默认设置为开（如果无错误就提交）
3、CONCURRENCY AND LOCKING——并发和锁定
实际中会存在两个及更多的用户同时访问相同的数据库的情况
并发：当一个用户修改其他用户正在检索或修改的数据时，并发可能会成为一个问题
❗默认情况下，MySQL怎么处理并发问题
模拟两位用户视图同时更新给定客户的积分情况
①打开两个操作台，同时写在各自的query
②逐行执行
第一个query执行到UPDATE customers结束，随后第二个query也执行到UPDATE customers
显示更行正在运行，因为当我们执行第一个更新时，MySQL放了一个锁到我们更新的customer行上，如果另一个事务尝试更新同一行，它必须等第一个事务完成，要么提交要么返回，
③再执行一遍第二个query的3、4行，指针加载中
到第一个query执行COMMIT，随后回到第二个query同样执行COMMIT
原积分
更新后
每笔交易都增加了10积分，如果一个事务视图修改一行或多行，它给这些行上了锁，防止其他事务修改这些行，直到第一个事务完成，被提交或者被退回
MySQL默认状态下的锁定行为，多数不必担心并发问题；在特殊情况下，默认行为不足以满足应用里的特定场景，可以修改默认行为
4、CONCURRENCY PROBLEMS——并发问题
Lost Updates（丢失更新）：
当两个事务尝试更新相同的数据并且没有上锁时，就会发生较晚提交的事务会覆盖较早事务做的更改的情况
假设两个事务都试图更新同一个客户，A想增加points，B想更新state
如果B较晚提交，那么事务A所做的更新将会丢失
❗如何防止这种情况发生——锁
默认情况下MySQL会使用锁定机制，防止两个事务同时更新同样的数据，会一个一个按顺序运行，这样两个更新都能完成
Dirty Reads（脏读）：
当一个事务取读了尚未被提交的数据
例如A把客户的积分从10改到20，再提交之前，B已经取读了这名客户，每点积分给予1刀的折扣，这样就会给这名客户20刀的折扣
如果A在B完成之前退回了，B会拿到本就不存在的数据，这个客户从来就没有20积分过，也从来没有提交至数据库过，然而B却给了客户20刀的折扣
综上B取读了未提交的数据，因此数据是脏的
需要为事务建立隔离级别，这样事务修改的数据不会立马被其他事务读取，除非它提交了更新
标准的SQL定义的事务隔离级别（1/4）
READ COMMITTED（读已提交）：
事务只能取读已提交的数据，避免脏读
如果基于某个事务做出商业决定，那么我们的决定至少是基于真是已提交的数据，数据后面也可以变，但和事务没有关系了
Non_repeating Reads（不可重复读）：
当在事务中添加更多隔离时，可以保证事务只能读取已提交的数据
❗如果在事务过程中，读取了某个数据两次，并得到了不同的结果怎么办
A看到客户有10积分，根据这个数值做出商业决策
在A完成事务前，B把这个顾客的积分更新为0
回到A再次取读这个客户的积分，也许因为它在子查询里
现在这个事务中，我们已经读了两遍积分，而且每次都看到了不同数值，这就是不可重复读/不一致性
可以说在任何时候，我们应该根据最新信息做决定，在商务场景中不必担心这点，
在事务开始的那一刻，这个客户有10 积分，所以应该给10刀折扣
如果积分在事务过程中发生变化，我们也无法看到改变，我们能看到的应该是初始快照
如果想要这样，就需要增加事务隔离级别，将它与其他事务隔离，确保数据更改对事务不可见
标准的SQL定义的事务隔离级别（2/4）
REPEATABLE READ（可重复读）：在这个级别上，我们读取的数据是可重复和一致的，就算有其他事务更改了数据，我们会看到首次读取就创建的快照
Phantom Reads（幻读）
A事务要查询积分超过10分的所有客户，可能想给特别折扣码；
B为另一位客户更新了积分，且还没有被查询返回
这个客户现在是有资格使用这个折扣码的，当查询刀折中客户表时，并没有看到这位客户
所以A事务完成后，仍有一个符合条件的客户没收到折扣码
（Phantom是幽灵的意思，突然出现的数据无法在查询中看到，因为是在执行查询后才添加、更新或删除的）
要看想解决的商业问题具体是什么样的，以及把该客户包含在事务中到底有多重要
我们可以之后再执行A事务，这位客户也可获得折扣码
但如果所有符合条件的客户都包括在我们的事务中是至关重要的，就必须确保没有任何其他事务正在运行，并会影响我们用以查找符合条件客户的查询
标准的SQL定义的事务隔离级别（3/4）
SERIALIZABLE（序列化）：能保证当有别的事务在更新数据时，我们的事务能知晓变动
如果有其他事务修改了可能影响查询结果的数据，我们的事务必须等它们完成，这样事务就会按序列化执行
这是最高隔离级别，为我们的操作提供了最大的确定性
但拥有的用户和并发事务越多，就要等越久，系统也会变慢，所以会损害性能和可扩展性。因此，应该只在真的有必要防止幻读的情况下才保留此项
5、TRANSACTION ISOLATION LEVELS——事务隔离级别
并发问题：
Lost Updates（丢失更新）：
当两个事务更新同一行的数据并且没有上锁时，最后提交的事务会覆盖先前事务做的更改
Dirty Reads（脏读）：
取读了尚未被提交的数据
Non_repeating Reads（不可重复读）：
在事务中读取了相同的数据两次，但是得到了不同的结果，因为另一个事务在两次读取之间更新了数据
Phantom Reads（幻读）：
当我们查询缺失了一行或者多行，因为有另一个事务正在修改数据，我们没有意识到事务的修改
标准事务隔离级别：
READ UNCOMMITTED（读未提交）：
基本无法解决这些问题，因为事务未彼此隔离，它们可以读取彼此未提交的更改
READ COMMITTED（读已提交）：
只能取读已提交的数据，避免脏读
REPEATABLE READ（可重复读）：
不同的读取会返回同样的结果，就算有其他事务更改了数据
SERIALIZABLE（序列化）：
要是数据在其间变动了，事务会待定，以获取最新数据
（会加重服务器负担，因为在存储和CPU方面都需要额外资源，来管理需要等待的事务；这里会用到更多隔离事务的锁，存在性能和可扩展性问题）
综上：
🔺越低的隔离级别会越容易并发，会有更多用户可以在同时接触到同一数据，但我们需要用以隔离事务的锁也更少了
🔺越高的隔离级别限制了并发，意味着更少的并发问题，会以降低性能和可扩展性为代价，需要更多的锁和资源
最快的隔离级别是读未提交，不设置任何锁，忽略其他事务设置的锁，因此可能遇到所有并发问题
MySQL中，默认的事务隔离级别是可重复读取，在大多数场景下都有效，比可序化更快并且阻止了除幻读外的大多数并发问题
READ UNCOMMITTED（读未提交）：、READ COMMITTED（读已提交）：可以在不需要精确的一致性的批量报告或者数据不怎么更新的情况下使用它们
❗如何设置事务隔离级别
①首先查看当前隔离级别
SHOW VARIABLES：查看MySQL使用的系统变量
②更改隔离级别
SET TRANSACTION ISOLATION LEVEL：这将为下一个事务设置隔离级别，
SESSION：可以为当前会话或连接所有以后的事务设定隔离级别
接着写上会话或者连接所有未来的事务都会是SERIALIZABLE这个隔离级别
GLOBAL：还可以为所有会话中的所有新事物设置全局隔离级别
如果是应用程序开发人员，应用程序某处有一个可以连接到数据库的函数或方法得以用来执行某一事务，可能的对象关系映射或者直接连接MySQL
无论哪种，在执行事务之前，只需修改那个会话或者连接的隔离级别，然后再运行事务
连接到MySQL→更改隔离级别→执行事务→断开连接
这样数据库中的其他事务就不会受到影响
6、READ UNCOMMITTED ISOLATION LEVEL——读未提交隔离级别
①打开两个会话，模拟两个客户
在会话一中输入
在这个隔离级别，我们可以读取为提交的数据，所以存在脏读
在会话二中输入
②执行脚本前，先查看顾客积分
③逐行执行
在会话一中逐行执行，只执行第一行
（此处没有START TRANSACTION语句，因为执行的每个语句MySQL会在事务中打包语句，并自动提交，我们没有修改任何数据，但这个选择语句仍然在事务中执行了）
在会话二中逐行执行，执行1、2行
此时还未提交修改，但如果回到会话一，因为把隔离级别设置为读取未提交
会话一中的选择语句会读取未提交的数据
逐行执行SELECT points，显示积分为20
先前查看这位顾客应该有2293积分
问题：在会话一事务里读取到的积分为20，你可能会根据这个数值做商业决策
但如果在会话二中，处于某种原因提交语句从没执行怎么办，可能服务器崩了或者事务显示退回了
③执行一下退回
在会话一中，我们使用了一个从未在数据库中存在值——脏读
总结：读未提交是最低等的隔离级别，在这一级别会遇到所有并发问题
7、READ COMMITTED ISOLATION LEVEL——读已提交隔离级别
①把隔离级别改成读已提交，只读取已经提交的数据，不会再出现脏读
②执行前再查看顾客表的数据
③逐行执行
在会话一执行第1行来设置隔离级别
回到会话二，逐行执行1、3，但还未提交任何内容
如果回到会话一并读取积分，不该看得到未提交的数据
回到会话二，执行提交，此时顾客积分应该是20
重新读取积分
所以在这个隔离级别不存在脏读，但还有其他问题，会存在不可重复读
很可能在一个事务中会两次读取同一内容，但每次都得到不同的值
④在会话一设置两个选择语句
执行第一行设置隔离级别，这只会对下一个事务起作用
这个新事务不会有相同的隔离级别，而是默认的隔离级别，也就是可重复读
⑤再次逐行执行
先逐行执行1-3行，显示积分20
会话二，更新积分为30
执行会话二1、3、5行
回到会话一，执行第4行，得到了不同值
在这一隔离级别有不可重复或不一致，解决这个问题需要提高事务的隔离级别
8、REPEATABLE READ ISOLATION LEVEL——可重复读隔离级别
①修改隔离级别并执行
②逐行执行
先逐行执行1-3行，显示积分30
会话二，更新积分为40
执行会话二1、3、5行
回到会话一，执行第四行，得到一样的值
读取又可重复又一致
以上是MySQL的默认隔离级别，这个级别还有一个问题——幻读
③修改坐标位于VA的顾客
也许想给这些顾客一些特别优惠
先行提交
④开始新事务
修改会话二
显然顾客1并不在VA，模拟一个场景是一位客户想要读取位于VA的顾客
同时另一个客户正在更新数据，让顾客1能被包括在这个客户1（会话一）执行过的查询中
逐行执行1、2行，暂未提交内容
现在再次执行还是只能看到顾客2
此时提交会话二，就有两名位于VA的顾客
但可能会漏掉一个customer_id = 1
再执行会话二，依然只有顾客2
因为可重复读的情况下，取读会保持一致性
所以必须提交会话二的内容，才会看到两名位于VA的顾客
9、SERIALIZABLE ISOLATION LEVEL——序列化隔离级别
①将事务隔离级别更改为序列化
②逐行执行
会话一，执行1、2行，
修改会话二，执行1、2行，还未提交的状态
当第一个客户（会话一）试图读取VA的顾客的时，另一个客户（会话二）正在更新顾客3，这个顾客应该被算进查询里，否则将出现幻读
此事务正在等待另一个事务完成
当会话二确认提交
会话一就有3名在VA的顾客了
综上：通过序列化隔离级别解决了所有并发问题，无幻读、无丢失更新或者脏读
只有在想防止幻读的情况下使用这一隔离级别，而不是系统中的每一个事务
默认的隔离级别可重复读对大多数场景都有效
10、DEADLOCKS——死锁
数据库中的一个经典问题
死锁：当不同事务均因握住了别的事务需要的“锁”而无法完成的情况，所以两个事务都一直在等待对方，并永远没法释放锁
①依旧打开两个会话模拟
②会话一中更新customers表和orders表
③会话二中跟换customers表和orders表顺序
④逐行执行
在会话一中，执行第3行customers时，会更新顾客1，这个事务将锁定此记录，所以其他事务
将无法更新此记录，必须等待完成（与使用什么隔离级别无关）
那么会话二执行完第2行orders时，准备执行第3行customers，就无法完成，必须等会话一完成才可以
而会话一准备执行第4行orders时，已经被会话二锁定，也必须等它结束
这就是死锁问题
一个应用程序开发人员，应该以这样的方式编写应用程序：如果事务因为死锁而被退回，可以重新恢复，或者告诉用户，也可以减小死锁发生的可能性
🔺如果经常再两个事务中检测到死锁，查看两者代码，这些事务可能是存储过程的一部分
看事务里的语句顺序，如果这些事务以相反的顺序更新记录，就很可能出现死锁
为了减少死锁，在更新多条记录的时候可以遵照相同的顺序
🔺尽量简化事务，缩小事务运行时长，这样它们就不太可能与其他事务冲突
如果事务要基于非常大的表运行，可能需要很长时间运行，就会有冲突的风险
（那么是不是可以把那些事务安排在非高峰时段运行，由此避开大量活跃用，后半夜或者凌晨更新）
————TBC
169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
