首页 / Mysql / mysql存储过程事务管理简析

mysql存储过程事务管理简析

ACID:Atomic、Consistent、Isolate…

ACID:Atomic、Consistent、Isolated、Durable
存储程序提供了一个绝佳的机制来定义、封装和管理事务。
1,MySQL的事务支持
1)MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关:
Sql代码

隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性
ANSI标准定义了4个隔离级别,MySQL的InnoDB都支持:
Java代码

Sql代码

MySQL默认的隔离级别是REPEATABLE READ,在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心,READ UNCOMMITTED会导致数据完整性的严重问题,而SERIALIZABLE会导致性能问题并增加死锁的机率
3)事务管理语句:
Sql代码

2,定义事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句独立为一个事务。
在复杂的应用场景下这种方式就不能满足需求了。
为了打开事务,允许在COMMIT和ROLLBACK之前多条语句被执行,我们需要做以下两步(也可以人为是两种方式):
1, 设置MySQL的autocommit属性为0,默认为1
2,使用START TRANSACTION语句显式的打开一个事务(然后autocommit属性会自动被设置为0)
如果已经打开一个事务,则SET autocommit=0不会起作用,因为START TRANSACTION会隐式的提交session中所有当前的更改,结束已有的事务,并打开一个新的事务。
使用SET AUTOCOMMIT语句的存储过程例子:
Sql代码

通常COMMIT或ROLLBACK语句执行时才完成一个事务,但是有些DDL语句等会隐式触发COMMIT,所以应该在事务中尽可能少用或注意一下:
Sql代码

3,使用Savepoint
使用savepoint回滚难免有些性能消耗,一般可以用IF改写
savepoint的良好使用的场景之一是“嵌套事务”,你可能希望程序执行一个小的事务,但是不希望回滚外面更大的事务:
Sql代码

4,事务和锁
事务的ACID属性只能通过限制数据库的同步更改来实现,从而通过对修改数据加锁来实现。
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
缺点是后面的事务必须等前面的事务完成才能开始执行,吞吐量随着等待锁释放的时间增长而递减。
MySQL/InnoDB通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制,而且读数据可以始终没有等待。
可以在SELECT语句里使用FOR UPDATE或LOCK IN SHARE MODE语句来加上行级锁
Sql代码

FOR UPDATE会锁住该SELECT语句返回的行,其他SELECT和DML语句必须等待该SELECT语句所在的事务完成
LOCK IN SHARE MODE同FOR UPDATE,但是允许其他session的SELECT语句执行并允许获取SHARE MODE锁
死锁:
死锁发生于两个事务相互等待彼此释放锁的情景
当MySQL/InnoDB检查到死锁时,它会强制一个事务rollback并触发一条错误消息
对InnoDB而言,所选择的rollback的事务是完成工作最少的事务(所修改的行最少)
Java代码

死锁在任何数据库系统里都可能发生,但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。
可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。
如果死锁不容易debug,你可以向你的程序中添加一些逻辑来处理死锁并重试事务,但这部分代码多了以后很难维护
所以,比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁,这样就能避免死锁:
Java代码

设置死锁ttl: innodb_lock_wait_timeout,默认为50秒
如果你在一个事务中混合使用InnoDB和非InnoDB表,则MySQL不能检测到死锁,此时会抛出“lock wait timeuot”1205错误
乐观所和悲观锁策略:
悲观锁:在读取数据时锁住那几行,其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所:读取数据时不锁,更新时检查是否数据已经被更新过,如果是则取消当前更新
一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁
悲观锁的例子:
Java代码

乐观锁的例子:
Java代码

5,事务设计指南
1,保持事务短小
2,尽量避免事务中rollback
3,尽量避免savepoint
4,默认情况下,依赖于悲观锁
5,为吞吐量要求苛刻的事务考虑乐观锁
6,显示声明打开事务
7,锁的行越少越好,锁的时间越短越好

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/1077.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏