MySQL之事务&隔离级别&死锁

深藏阁楼爱情的钟 2022-08-03 05:11 245阅读 0赞

事务是并发控制的基本单位。它是一个涉及到大量CPU和I/O操作的操作序列，这些操作作为一个处理单元来对待，要么都执行，要么都不执行，它是一个不可分割的工作单位。一般来说能够启动事务的操作是一组SQL语句，或者是ODBC当中启动事务的指令。 例如，银行转账工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。  
针对上面的描述可以看出，事务的提出主要是为了解决并发情况下保持数据一致性的问题。  
（Transaction） 事务的标准定义： 指作为单个逻辑工作单元执行的一系列操作，而这些逻辑工作单元需要具有原子性，一致性，隔离性和持久性四个属性，统称为ACID特性。  
Atomicity（原子性）：事务中包含的操作被看做一个逻辑单元，这个逻辑单元中的操作要么都完成，要么都失败。  
事务的各元素不可分是一个完整操作。  
Consistency（一致性）：事物完成时数据必须是一致的，也就是说和事物开始之前，数据存储中的数据处于一致状态，保证数据的无损。  
Isolation（隔离性）：事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。  
同时，并行事务的修改必须与其他并行事务的修改相互独立，不应该以任何方式依赖于或影响其他事务。  
Durability（持久性）：事务结束后,事务处理的结果必须能够得到固化。系统必须保证任何故障都不会引起事务表示的不一致性。  
允许事务并发执行的原因：每一个事务可能都会涉及到一定的CPU操作和I/O操作，如果在进行I/O操作时，CPU处于空闲状态，  
这时让CPU去执行其他事务，可以提高吞吐量和资源利用率，减少等待时间。  
隔离性靠服务器内部的事务调度实现，例如MVCC（Multi-Version Concurrency Control）基于快照时间戳来实现多版本并发控制。  
持久性实现机制：  
1.事务提交之前就已经写出数据持久性存储，效率极低  
2.结合事务日志完成：数据文件产生随机IO速度慢性能差，而事务日志产生顺序IO速度很快，先将数据操作提交到事务日志中，再由后台进程通过其他机制将事务日志中已经提交的数据同步到数据文件中  
事务调度：可恢复调度  
无级联调度  
隔离级别：READ UNCOMMITTED读未提交  
READ COMMITTED读提交  
REPEATABLE READ可重读  
SERIALIZABLE序列化（串行）  
事务的语句  
　开始事物：START TRANSACTION  
　提交事物：COMMIT  
　回滚事务：ROLLBACK 即撤销操作  
MySQL为了保证事务的持久性，默认开启了自动提交，即每执行一句SQL操作都会直接提交，产生一次I/O操作，会导致性能极低  
![这里写图片描述][20150731180951872]  
一种优化策略是明确使用事务，并关闭自动提交  
![这里写图片描述][20150731181016583]  
以下逐级演示四个隔离级别对事务的影响，启动两个mysql会话，每一次测试将两个mysql会话的级别调整为相同级别，并交叉启动start transaction，会话1进行相关更新操作，会话2查询会话1更新的表，比较结果的不同  
读未提交  
![这里写图片描述][20150731181107751]  
会话1  
![这里写图片描述][20150731181005034]  
会话2  
![这里写图片描述][20150731181030447]  
读提交  
![这里写图片描述][20150731181101959]  
会话1  
![这里写图片描述][20150731181111615]  
会话2  
![这里写图片描述][20150731181137043]  
可重读  
![这里写图片描述][20150731181203953]  
会话1  
![这里写图片描述][20150731181353688]  
会话2  
![这里写图片描述][20150731181433188]  
可串行  
会话1  
![这里写图片描述][20150731181520237]  
会话2  
![这里写图片描述][20150731181534917]  
直到会话1提交或回滚，会话2才能进行正常查询涉及到会话1事务修改的内容  
![这里写图片描述][20150731181502418]  
![这里写图片描述][20150731181510514]  
由此可发现一旦启用可串行隔离级别，事务间的隔离效果非常显著，会话2绝对查询不到会话1未提交的修改，也不会产生两次查询结果不一样的幻影。事务隔离级别越高，事务安全性越好，但事务的并发能越差，如果不是在类似银行证券对事务安全性严格要求的生产环境中，可以尽可能降低事务隔离级别以提高性能。  
并发控制依赖的技术手段：  
锁  
时间戳  
多版本和快照隔离  
事务的状态：active  
部分提交  
失败  
终止  
提交  
![这里写图片描述][20150731181541121]  
事务的保存点  
用户在事务（transaction）内可以声明被称为保存点（savepoint）的标记。保存点将一个大事务划分为较小的片断。用户可以使用保存点（savepoint）在事务（transaction）内的任意位置作标记。之后用户在对事务进行回滚操作（rolling back）时，就可以选择从当前执行位置回滚到事务内的任意一个保存点。例如用户可以在一系列复杂的更新（update）操作之间插入保存点，如果执行过程中一个语句出现错误，用户可以回滚到错误之前的某个保存点，而不必重新提交所有的语句。  
![这里写图片描述][20150731181616736]  
![这里写图片描述][20150731181624770]  
![这里写图片描述][20150731181632133]  
死锁: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都分配不到必需的资源因而无法继续运行.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.  
死锁是一种条件，不仅仅是在关系数据库管理系统 (RDBMS) 中发生，在任何多用户系统中都可以发生的。当两个用户（或会话）具有不同对象的锁，并且每个用户需要另一个对象的锁时，就会出现死锁。每个用户都等待另一个用户释放他的锁。使用分布式事务时，也可能发生死锁。  
举个形象的例子，就像3个人（A、B、C）在玩3个球（1、2、3），规则很简单：每个人都必须先拿到自己左手边的球，才能拿自己右边的球，两手都有球之后，才能把球都放下。  
![这里写图片描述][20150731181849387]  
如果三个人刚好都只拿到左手边的球，都等着那右手边的球，但是因为谁都不能放手，那么这三个人（线程）都将陷入无尽的等待中了，即每个线程都在等待被其他线程占用并堵塞了的资源，这就是传说中的“死锁”。  
计算机系统中,如果系统的资源分配策略不当，更常见的可能是程序员写的程序有错误等，则会导致进程因竞争资源不当而产生死锁的现象。锁有多种实现方式，比如意向锁，共享－排他锁，锁表，树形协议，时间戳协议等等。锁还有多种粒度，比如表锁，行锁，页锁，（MySQL服务器仅支持表级锁，行锁需要由存储引擎完成）也可以在记录上加锁。  
产生死锁的原因主要是：  
a.因为系统资源不足  
b.进程运行推进的顺序不合适  
c.资源分配不当等  
如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。  
产生死锁的四个必要条件：  
a.互斥条件：一个资源每次只能被一个进程使用。  
b.请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。  
c.不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。  
d.循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。  
这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。  
死锁的解除与预防：  
理解了死锁的原因，尤其是产生死锁的四个必要条件，就可以最大可能地避免、预防和解除死锁。所以在系统设计、进程调度等方面要避免让这四个必要条件成立，确定资源的合理分配算法，避免进程永久占据系统资源。此外也要防止进程在处于等待状态的情况下占用资源,在系统运行过程中，对进程发出的每一个系统能够满足的资源申请进行动态检查，并根据检查结果决定是否分配资源，若分配后系统可能发生死锁，则不予分配，否则予以分配 。因此，对资源的分配要给予合理的规划。  
将死锁减至最少：  
虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销  
下列方法有助于最大限度地降低死锁：  
a.按同一顺序访问对象  
如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。例如，如果两个并发事务获得 A 表上的锁，然后获得 B表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在 A 表上。第一个事务提交或回滚后，第二个事务继续进行。不发生死锁。将存储过程用于所有的数据修改可以标准化访问对象的顺序。  
b.避免事务中的用户交互  
避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，例如答复应用程序请求参数的提示。如果事务正在等待用户输入，而用户去吃午餐了或者甚至回家过周末了，则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。  
c.保持事务简短并在一个批处理中  
在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。  
保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。  
d.使用低隔离级别  
确定事务是否能在更低的隔离级别上运行。执行读提交允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（READ  
COMMITTED）而不使用较高的隔离级别（SERIABLIZABLE）可以缩短持有共享锁的时间，从而降低了锁定争夺。  
e.使用绑定连接  
使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。次级连接所获得的任何锁可以象由主连接获得的锁那样持有，反之亦然，因此不会相互阻塞。

[20150731180951872]: /images/20220731/8e4f5e67a57c444bbb828e385c4f304d.png
[20150731181016583]: /images/20220731/9f49b6d8c04649dd89087314830be4ae.png
[20150731181107751]: /images/20220731/fccb80d3ca0e4a35a26835f726f68df9.png
[20150731181005034]: /images/20220731/925f06e7f62b461a8eb9495e0684fbe4.png
[20150731181030447]: /images/20220731/f9876d060ac541fb92952dc02cc6b678.png
[20150731181101959]: /images/20220731/576f89e2a7c74bf4bb30103227ac0288.png
[20150731181111615]: /images/20220731/414a8fe1d5124dd8b6fa0279ef72199f.png
[20150731181137043]: /images/20220731/b8c77a4c1f2148d09ef0d93221946cb2.png
[20150731181203953]: /images/20220731/75cc670810a7412189db567ec6d42c37.png
[20150731181353688]: /images/20220731/6c36e34ad8ef4e86bb4d306e92a6dee9.png
[20150731181433188]: /images/20220731/8874e686842b4880ae53a980b446fb8e.png
[20150731181520237]: /images/20220731/43c73df7ce104845831cd1d3bf2ea0cb.png
[20150731181534917]: /images/20220731/e34514e13ce24157ab626e8a240c5814.png
[20150731181502418]: /images/20220731/e7872e1ee96d4a09a0fd6db1ece6f7a4.png
[20150731181510514]: /images/20220731/46dfb974d59e4dd3936848eeeb1a2b1d.png
[20150731181541121]: /images/20220731/656df736f04a4696b5dbba711fb19160.png
[20150731181616736]: /images/20220731/ec26d7ec469d48a49b0f52c222b14f00.png
[20150731181624770]: /images/20220731/a9e65f8b254f4d64bc068a745259a2a6.png
[20150731181632133]: /images/20220731/6a783051846c436dbc15e4f8cd1ee195.png
[20150731181849387]: /images/20220731/6ad77438807743a8b56fec3bcf071cea.png