451-MySQL(事务)

朱雀 2022-09-06 14:23 183阅读 0赞

## MySQL事务 ##

**MyISAM不支持事务  
InnoDB支持事务，支持行锁**

**事务概念**  
一个事务是由一条或者多条对数据库操作的SQL语句所组成的一个不可分割的单元，只有当事务中的所有操作都正常执行完了，整个事务才会被提交给数据库；如果有部分事务处理失败，那么事务就要回退到最初的状态，因此，**事务要么全部执行成功，要么全部失败。**

**有的业务需要1条以上的SQL语句共同完成，这些SQL都成功了才算业务成功。**  
类似于银行的转账，商品的入库出库  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
**业务由2条SQL组成。** 没有一部分成功一部分失败这一说。  
**begin开启事务，这2句SQL都成功了，commit提交一个事务。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
**如果其中一句SQL出错了，事务就没有提交，事务就回滚，数据恢复到事务开始前的状态。**

![在这里插入图片描述][94a0e3eb52c04d9ebcd4845bccf41917.png]  
**改成 0 ，手动提交事务，在代码上进行控制，做业务，业务都成功，提交1个事务，有失败，就回滚**  
![在这里插入图片描述][8bc02c6167cf4dd0b5847adab26b67a3.png]

**所以记住事务的几个基本概念，如下：**  
1、事务是一组SQL语句的执行，要么全部成功，要么全部失败，不能出现部分成功，部分失败的结果。保证事务执行的原子操作。  
2、事务的所有SQL语句全部执行成功，才能提交（commit）事务，把结果写回磁盘上。  
3、事务执行过程中，有的SQL出现错误，那么事务必须要回滚（rollback）到最初的状态（事务开始前的状态）。

## ACID特性 ##

**每一个事务必须满足下面的4个特性：**

**事务的原子性（Atomic）：**  
事务是一个不可分割的整体，事务必须具有原子特性，及当数据修改时，要么全执行，要么全不执行，即不允许事务部分的完成。  
**事务的一致性（Consistency）：**  
一个事务执行之前和执行之后，数据库数据必须保持一致性状态。数据库的一致性状态必须由**用户**来负责，由**并发控制机制**实现。就拿网上购物来说，你只有让商品出库，又让商品进入顾客的购物车才能构成一个完整的事务。  
**事务的隔离性（Isolation）：**  
当两个或者多个事务**并发执行**时，为了保证数据的安全性，将一个事物内部的操作与其它事务的操作隔离起来，不被其它正在执行的事务所看到，使得并发执行的各个事务之间不能互相影响。  
**事务的持久性（Durability）：**  
事务完成(commit成功)以后，DBMS保证它对数据库中的数据的修改是永久性的，即使数据库因为故障出错，也应该能够恢复数据！（DB写数据都是先在cache缓存上写的，因为快，然后通过磁盘I/O往磁盘上写，当事务提交后，cache往磁盘上提交数据是要花时间的，这个花时间的过程中停电了，宕机了，自动重启了，此时数据就丢了，**但是MySQL的redo log机制，可以保证数据库的永久性，undo log回滚日志）**

**MySQL最重要的是日志，不是数据**  
![在这里插入图片描述][04df4cbb44a24324aed9c58adf5f1dca.png]

## 事务并发存在的问题 ##

**事务处理不经隔离，并发执行事务时通常会发生以下的问题：**

**脏读（Dirty Read）**：一个事务读取了另一个事务未提交的数据。例如当事务A和事务B并发执行时，当事务A更新后，事务B查询读取到A尚未提交的数据，此时事务A回滚，则事务B读到的数据就是无效的脏数据。  
**（事务B读取了事务A尚未提交的数据）**  
(脏读必须杜绝，其他下面的情况是事务已经提交了，不能算有问题，解不解决看业务的需求）

**不可重复读（NonRepeatable Read）**：一个事务的操作导致另一个事务前后两次读取到不同的数据。  
**例如** 当事务A和事务B并发执行时，当事务B查询读取数据后，事务A更新操作更改事务B查询到的数据，此时事务B再次去读该数据，发现前后两次读的数据不一样。**（事务B读取了事务A已提交的数据）**

**虚读（Phantom Read）幻读**：一个事务的操作导致另一个事务前后两次查询的结果数据量不同。例如 当事务A和事务B并发执行时，当事务B查询读取数据后，事务A新增或者删除了一条满足事务B查询条件的记录，此时事务B再去查询，发现查询到前一次不存在的记录，或者前一次查询的一些记录不见了。**（事务B读取了事务A新增加的数据或者读不到事务A删除的数据）**

## 事务的隔离级别 ##

**MySQL支持的四种隔离级别是：**

**1、TRANSACTION\_READ\_UNCOMMITTED**。未提交读。说明在提交前一个事务可以看到另一个事务的变化。这样读脏数据，不可重复读和虚读都是被允许的。

**2、TRANSACTION\_READ\_COMMITTED**。已提交读（**oracle默认**的）。  
说明读取未提交的数据是不允许的。这个级别仍然允许不可重复读和虚读产生。

**3、TRANSACTION\_REPEATABLE\_READ**。可重复读。说明事务保证能够再次读取相同的数据而不会失败，但虚读仍然会出现。

**4、TRANSACTION\_SERIALIZABLE**。串行化。是最高的事务级别，它防止读脏数据，不可重复读和虚读。串行执行，单线程操作。并发能力最低。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
**备注：**  
事务隔离级别越高，为避免冲突所花费的性能也就越多。  
在“可重复读”级别，实际上可以解决部分的虚读问题，但是不能防止update更新产生的虚读问题，要禁止虚读产生，还是需要设置串行化隔离级别。

## MySQL的事务处理命令 ##

打开MySQL的Command命令行窗口，测试以下命令：  
**1、SELECT @@AUTOCOMMIT; 查看MySQL是否自动提交事务**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
**0表示手动提交事务，1表示自动提交事务，设置事务提交方式为手动提交方式：**  
![在这里插入图片描述][df846c8c34b2471081d0bbea0ff110e2.png]  
**BEGIN**; 开启一个事务  
**COMMIT**; 提交一个事务  
**ROLLBACK**; 回滚一个事务到初始的位置  
**SAVEPOINT point1**; 设置一个名字为point1的保存点  
**ROLLBACK TO point1**; 事务回滚到保存点point1，而不是回滚到初始状态  
**SET TX\_ISOLATION=‘REPEATABLE-READ’;** 设置事务的隔离级别  
**SELECT @@ TX\_ISOLATION**; 查询事务的隔离级别  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]

## 实践测试验证 ##

![在这里插入图片描述][68d51da55c3a44e89d4f0fe26be0ac08.png]  
MySQL默认的隔离的级别是：**可重复读（允许幻读）**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]  
**先设置最低的隔离级别**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]  
**开启**  
![在这里插入图片描述][8c800137431f4fe38363ca31f87bc2bf.png]

## 测试第1隔离级别（未提交读） ##

![在这里插入图片描述][1115eb79b0b140868381a3ce3b4a6fc9.png]  
事务A还没有提交。  
事务B此时再查看一下。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]  
读出了事务A尚未提交的数据。  
事务A可以回滚。  
![在这里插入图片描述][b7e89039d72545c69e4841fb6430be30.png]  
但是刚才事务B做业务呢，已经来不及了。  
事务B回滚。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]

## 测试第2隔离级别（已提交读） ##

![在这里插入图片描述][6729707d72af40cfb4b2ddb0565fec11.png]  
事务B select  
![在这里插入图片描述][a4689ba9dc404d058bdc0b0b01d2ee45.png]  
现在事务A来修改  
![在这里插入图片描述][782bfec2aa764136813fb5f229ae02ca.png]  
事务B再查看，看不到修改，事务并发的MVCC版本控制  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]  
现在事务A提交了  
![在这里插入图片描述][c1f365f193934ca39663b166faf1b736.png]  
现在事务B再查看，可以看到事务A已提交的数据。

![在这里插入图片描述][7c35a62b837c4925acad1e4a9df68017.png]

## 测试第3隔离级别（可重复读） ##

![在这里插入图片描述][1eee09077682453a864e9ce6cf44a77b.png]  
![在这里插入图片描述][679f30b01daf4d9d8dd3743238c6f542.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]  
我们先测试脏读  
事务B先查看张三的年龄  
![在这里插入图片描述][56fd1acec2374f29bf89d3f4ba150b1c.png]  
事务A现在把张三年龄改为22，但是没有提交事务  
事务B还是看到原来的数据。

![在这里插入图片描述][572e2a8d205044288564465864889364.png]  
事务A提交。  
事务B查看，年龄还是21，并未看到事务A提交后的数据。可重复读！！！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]  
测试虚读  
![在这里插入图片描述][6a2f30cfc2df437394506c4c0a924045.png]  
![在这里插入图片描述][d7bfbea23e1948f0b4497d17367a8e2f.png]  
事务Bselect  
![在这里插入图片描述][db1bd6e6eecf40a39dee0fd8c3065b51.png]  
事务Aselect

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]  
事务A修改了事务B查询条件结果的数据值，提交事务  
![在这里插入图片描述][a2233199fc8b467288838eb69cebaa8f.png]  
事务B再次查看

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]  
**可以防止了insert，delete幻读。  
但是不能防止update**  
事务B update  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]

## 测试第4隔离级别（串行化） ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]  
事务B select  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]  
此时事务A insert 一下  
![在这里插入图片描述][e6d786ff4b274b8eadfdf3fc98caadb0.png]  
直接被阻塞住了。串行化。  
![在这里插入图片描述][bee8d740a60d4a55ad5415a2f552460e.png]  
**死锁带有时间。超时，就会返回错误。**  
**允许并发的读读。其他都不允许。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/7664c05ae94e4fd69f1babe22dc39a8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/5b1d62c30cf3467695dd000428617491.png
[94a0e3eb52c04d9ebcd4845bccf41917.png]: /images/20220829/091f22328f4f489492f3b32534d418c0.png
[8bc02c6167cf4dd0b5847adab26b67a3.png]: /images/20220829/2fc117e6fc994266a3cb1c9db5029242.png
[04df4cbb44a24324aed9c58adf5f1dca.png]: /images/20220829/b9c15f7e4e434e63b1f401cad2382873.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/10381ed09a324c9db5846983f507a24f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/f820dd5f65354f85ba07c61b846f0b3e.png
[df846c8c34b2471081d0bbea0ff110e2.png]: /images/20220829/a49e7ac2d0ca48e384f07aa58c774306.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/c0559f1f414c45e2bd5c95945fdded6a.png
[68d51da55c3a44e89d4f0fe26be0ac08.png]: /images/20220829/6ce03f4c82ff419bab8829c6545e446b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/1ef2af5ea4ab4e0bbce643e5bb63ea35.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/22fcee9512614b0da01ecb32e38720f2.png
[8c800137431f4fe38363ca31f87bc2bf.png]: /images/20220829/a5f291853808425c96a4a54b43316ab1.png
[1115eb79b0b140868381a3ce3b4a6fc9.png]: /images/20220829/3e05d4e8f171416f8d0b4686d0a0fc3f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/3637c1bd2da94d9f99287207e2d8a695.png
[b7e89039d72545c69e4841fb6430be30.png]: /images/20220829/68bcf76df64e4ea38401608efca1bdf6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/ad45a66a4e014dbca2646d38cf5cbe9b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/f7ad12aa100e4319b917c7b54314e6b7.png
[6729707d72af40cfb4b2ddb0565fec11.png]: /images/20220829/b444a32b485b4121b9934a4f424452ab.png
[a4689ba9dc404d058bdc0b0b01d2ee45.png]: /images/20220829/3790f7c5fedd457c909d54b2eff736fa.png
[782bfec2aa764136813fb5f229ae02ca.png]: /images/20220829/ebfe36f5d9cb43e38570ebc5b8909a3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/ba6243a0f0d9438c80b0d025539047e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]: /images/20220829/5dc6710355b147f89687a5cdb313da28.png
[c1f365f193934ca39663b166faf1b736.png]: /images/20220829/1dc1b31cb1e54577993c833e21244662.png
[7c35a62b837c4925acad1e4a9df68017.png]: /images/20220829/32f87e1d04374c25a549fb5a945c1cbf.png
[1eee09077682453a864e9ce6cf44a77b.png]: /images/20220829/3d6f3d8f5cab4c11b242abede19b0960.png
[679f30b01daf4d9d8dd3743238c6f542.png]: /images/20220829/bb344003c81848c0b87300ab655bbdfb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]: /images/20220829/7ab7ba3468f54ba68fd72396de639e02.png
[56fd1acec2374f29bf89d3f4ba150b1c.png]: /images/20220829/bfd89c803080448cb7472c378b58b299.png
[572e2a8d205044288564465864889364.png]: /images/20220829/75af0d666a9446fd8d50545010daf26a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]: /images/20220829/17d8efa776de48bd8f1c01faff9aa6d8.png
[6a2f30cfc2df437394506c4c0a924045.png]: /images/20220829/e42a375327e648f5aa3c8511c6ec9c1b.png
[d7bfbea23e1948f0b4497d17367a8e2f.png]: /images/20220829/92a7f3623c3047ec987e165cc841fff8.png
[db1bd6e6eecf40a39dee0fd8c3065b51.png]: /images/20220829/945ff4ffc0154164a5dad33899d83d77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]: /images/20220829/1ddb2c7062df4f4cbe5728ef49063bf4.png
[a2233199fc8b467288838eb69cebaa8f.png]: /images/20220829/5e8038c4f22b4e38a27747c7c5186a31.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]: /images/20220829/02ee782441bf447197fd2cf21b66e034.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]: /images/20220829/6945e20bc0824ed08a1db681ce079d32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]: /images/20220829/2c0a70afac0f4d3fb174b632538d76f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]: /images/20220829/6a4bf422be534a56a94e071053b08ed1.png
[e6d786ff4b274b8eadfdf3fc98caadb0.png]: /images/20220829/7e1dbd9399fa4be39e4f56d483f590cd.png
[bee8d740a60d4a55ad5415a2f552460e.png]: /images/20220829/e7d2edfaa6a0491b8324509a077f3736.png