11. 数据库的事务

数据库的事务

数据库的事务

创建事务

sql

复制代码

start transaction; -- 开始事务
insert into orders (customer_id,order_date,status)
values (1,'2019-01-01',1);
insert into order_items
values (last_insert_id(),1,1,1);
rollback; -- 回滚，放弃执行事务，并把前面的也撤销
commit; -- 提交/完成事务

并发和锁定

所以，可以看到，当一个事务修改一行或多行时，会给这些行上锁，这些锁会阻止其他事务修改这些行，直到前一个事务完成（不管是提交还是退回）为止。

并发问题

1. 丢失更新

当两个事务尝试更新相同的数据，并且没有上锁时，就会造成丢失更新，后一个事务会覆盖前一个事务。

• 例如： 当事务 A 要更新记录 cfd ，并且事务 B 也要更新 cfd 时，若两个事务都读取记录 cfd， 在 A 更新了 cfd 且尚未提交时，B 更新了 cfd ，那后执行的事务 B 的更新会覆盖先执行的 A 的更新，更新将会丢失。
  
• 解决方法： 就是前面说的事务锁机制，锁会防止多个事务同时更新同一个数据，必须一个完成的再执行另一个
  

2. 脏读

一个事务读取了尚未被提交的数据

• 例如： 当事务 A 更新了 cfd 且尚未提交时，事务 B 读取了更新后的 cfd ，如果事务 A 此时因为一些原因回退了，那么事务 B 的读取就是错误的，读取了数据库中从未提交的数据，也叫脏读。
  
• 解决方法： 为事务建立隔离级别，这样事务修改的数据不会立马被其他事务读取。标准的 MySQL 事务隔离级别有四个，使用 “读已提交”，事务只能读取已经提交的数据，可以避免脏读。
  

3. 不可重复读

当我们在事务中添加更多隔离时，如果一个事务中，出现读取同一个数据两次但两次结果不一致的情况。这就是不可重复读或者不一致读

• 例如：

一种说法是，我们应该总是依照最新的数据做决定，所以这不是个问题。在商务场景中，我们一般不用担心这个问题

另一种说法是，我们应该保持数据一致性，以事务A在开始执行时的数据初始状态为依据来做决定，如果这是我们想要的话，就要增加事务 A 的隔离等级，让它在执行过程中看不见其它事务的数据更改（即便是提交过的）

SQL有个标准隔离等级叫 Repeatable Read 可重复读取，可以保证读取的数据是可重复和一致的，无论过程中其它事务对数据做了何种更改，读取到的都是数据的初始状态。

注意：可重复读取设置后，points 还是 10

4. 幻读

一个事务 A 完成读取后，发现有满足条件的记录没有被读取到，因为这个记录是由事务 B 在事务 A 读取之后进行的，这种错过读取的记录就是幻读

• 解决方法： 一种简单的方法，就是再进行一次事务 A 。但是如果要让事务一次就成功，就要确保这个事务执行时，没有其他事务正在运行。

为此，有另一种隔离级别称为 “序列化”——能保证当有别的事务再更新数据时，当前的事务可以知晓它的变动。如果其他事务修改了可能影响查询结果的数据，当前的事务必须等待它们完成。 这样事务就会按照一个固定的序列执行。

这一隔离级别是事务的最高级别，他能为我们的操作提供最大的确定性。

但是，高的隔离级别会导致查询的时间加长，系统也会变慢，所以隔离级别会损害性能和可拓展性。

所以我们应该真的有必要防止这种情况出现时，才去使用 “序列化”。

事务隔离级别总结

1. Lost Updates 丢失更新：两个事务更新同一行，最后提交的事务将覆盖先前所做的更改
   
2. Dirty Reads 脏读：读取了未提交的数据
   
3. Non-repeating Reads 不可重复读取 （或 Inconsistent Read 不一致读取）：在事务中读取了相同的数据两次，但得到了不同的结果
   
4. Phantom Reads 幻读：在查询中缺失了一行或多行，因为另一个事务正在修改数据而我们没有意识到事务的修改，我们就像遇见了鬼或者幽灵
   

为了解决这些问题，我们有四个标准的事务隔离等级：

1. Read Uncommitted 读取未提交：无法解决任何一个问题，因为事务间并没有任何隔离，他们甚至可以读取彼此未提交的更改
   
2. Read Committed 读取已提交：给予事务一定的隔离，这样我们只能读取已提交的数据，这防止了Dirty Reads 脏读，但在这个级别下，事务仍可能读取同个内容两次而得到不同的结果，因为另一个事务可能在两次读取之间更新并提交了数据，也就是它不能防止Non-repeating Reads 不可重复读取 （或 Inconsistent Read 不一致读取）
   
3. Repeatable Read 可重复读取：在这一级别下，我们可以确信不同的读取会返回相同的结果，即便数据在这期间被更改和提交
   
4. Serializable 序列化：可以防止以上所有问题，这一级别还能防止幻读，如果数据在我们执行过程中改变了，我们的事务会等待以获取最新的数据，但这很明显会给服务器增加负担，因为管理等待的事务需要消耗额外的储存和CPU资源
   

并发问题 VS 性能和可扩展性：

更低的隔离级别更容易并发，会有更多用户能在相同时间接触到相同数据，但也因此会有更多的并发问题，另一方面因为用以隔离的锁定更少，性能会更高

相反，更高的隔离等级限制了并发并减少了并发问题，但代价是性能和可扩展性的降低，因为我们需要更多的锁定和资源

MySQL的默认等级是 Repeatable Read 可重复读取，它可以防止除幻读外的所有并发问题并且比序列化更快，多数情况下应该保持这个默认等级。

如果对于某个特定的事务，防止幻读至关重要，可以改为 Serializable 序列化

对于某些对数据一致性要求不高的批量报告或者对于数据很少更新的情况，同时又想获得更好性能时，可考虑前两种等级

总的来说，一般保持默认隔离等级，只在特别需要时才做改变

修改事务隔离级别

sql

复制代码

show variables like 'transaction_isolation' -- 查看事务隔离级别环境变量
set transaction isolation level serializable;   -- 为下一个会话设置隔离级别
set session transaction isolation level serializable;   -- 为当前会话和未来的会话设置隔离级别
set global transaction isolation level serializable;  -- 全局设置会话隔离级别

死锁

当不同事务均因握住了别的事务需要的“锁”而无法完成的情况。事务一直在等待对方，并且永远没办法释放锁。

例如：

sql

复制代码

use sql_store;
start transaction;
-- 在两个会话新的会话里交换两个update的顺序，都先执行完第一个update，两个会话都会在下一行被对方锁住
update orders set status = 1 where order_id = 1;  
update customers set state = 'VA' where customer_id = 1;
commit;

报错： Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction

缓解办法

死锁如果只是偶尔发生一般不是什么问题，重新尝试或提醒用户重新尝试即可，死锁不可能完全避免，但有一些方法可以最小化其发生的概率：

• 注意语句顺序，为了减少死锁，我们在更新多条记录时可以遵循相同的顺序
• 尽量让你的事务减小，持续时间短一些，这样就不太容易和其他事务相冲突
• 如果事务要操作非常大的表，运行时间可能会很长，冲突的风险就会很高，看看能不能让这样的事务避开高峰期运行，以避开大量活跃用户。