快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

从一个业务看待 InnoDB事务,InnoDB数据库锁,同步的关系

请联系QQ:1793040 索取软件

写这篇文章的起因在于现在公司的一个”激活学习卡”业务,激活的接口是其它组提供(你只需要给接口”卡号”和”激活的人”),而分配哪张卡号是自己来写,关键是这个卡号是从一个表里每次去选一个。用sql语句每次limit 1 去捞取一条,但是这里面就出现了资源占用问题,sql语句每次limit 1 都是同一条,除非你已经使用(改变这条数据的状态)。一开始想到事务,但是越想越不对劲(后面还是用应用层面的锁同步机制),大概了解其原理后也彻底梳理了一下事务,数据库锁,同步的关系,整理出这篇文章。

这里写图片描述

脑中第一个想到了事务,事务的作用是什么?事务的本质是A(原子性)C(一致性)I(隔离性)D(持久性)。一个事务内会有多个方法。 要说事务跟多线程并发有什么联系,那就是事务的隔离机制,当多个事务并发执行,需要用事务的隔离机制保证多个事务并发执行不会相互影响。比如在可重复读(Repeatable-Read)的隔离机制下:下面一个流程我们可以看一下

序号 事务A A结果 事务B B结果
1 set tx_isolation=‘Repeatable-Read’; set tx_isolation=‘Repeatable-Read’;
2 start transaction; start transaction;
3 select * from where id=1; 在这里插入图片描述 select * from where id=1; 在这里插入图片描述
4 update test set score=score +1;
5 select * from where id=1; 这里写图片描述 select * from where id=1; 在这里插入图片描述
7 update test set score=score +1; 拿不到排他锁(X),最后会超时,更新失败
8 commit;
9 select * from where id=1; 在这里插入图片描述
10 update test set score=score +1; 事务A已commit,这次可以提交
11 select * from where id=1; 这里写图片描述

上面两个事务的交叉执行就已经涉及到 事务和锁 的知识点,我们一条条梳理

  • 首先 两个事务都使用了 Repeatable-Read 事务,可重复读利用MVCC特性 使用快照读 保证前后两次 读都不会受其它事务的影响,这点可以从第5步和第9步证明(事务A执行了修改,不管事务A有没有commit,事务B重新查询还是原来值,避免了脏读,不可重复度).关于 MVCC更多的实现细节,可以看 Innodb中的事务隔离级别和锁的关系.从这一特性,我们就可以认为 Repeatable-Read 事务 并不能帮我们解决 “学习卡”的问题,原因在于 当另一个事务拉取了一条卡号数据并回写这条数据的状态,即使我们前后查了两次 ,我们并不能发现这条数据被修改了(因为可重复读),所以出于业务考虑,只能通过业务层面的同步机制(串行化) 拉取卡号数据。

  • 接下来就是第7步,在事务A做出更新之后,事务B也尝试更新,但是更新失败,因为事务A没有commit操作,这里面涉及数据库锁的知识,先简单整理下:

共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

说明:

  • 共享锁和排他锁都是行锁,意向锁都是表锁,应用中我们只会使用到共享锁和排他锁,意向锁是mysql内部使用的,不需要用户干预,意向锁的作用也是为了提高效率,另一个事务不必检查每一行的锁记录(参考文档)。

  • 对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,不会加任何锁是因为此时属于快照读 (snapshot read),也跟MVCC有关(SERIALIZABLE事务隔离机制属于悲观锁,执行最严格的隔离机制,会给select语句加上共享锁,更新语句会加排他锁),事务可以通过以下语句显示给记录集手动加共享锁或排他锁。

    共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
    排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE。

  • 在MySQL中,行级锁并不是直接锁记录,而是锁索引。换句话说,只有通过索引条件检索数据,InnoDB才会使用行级锁,否则,InnoDB将使用表锁!也就是说,你对一个非索引的列update操作,会导致另外一个连接无法修改表内任意一条数据

从上面我们可以看出,当事务A执行更新操作,mysql会自己为这条数据增加 排他锁(X),而排它锁只允许获得的事务执行操作,其它事务将被阻塞挂起,最后超时。 我再简单做一个不同事务不同锁的兼容排斥情况。简单总结来说就是 。

当前锁/请求锁 X IX S IS
X 排斥 排斥 排斥 排斥
IX 排斥 兼容 排斥 兼容
S 排斥 排斥 兼容 兼容
IS 排斥 兼容 兼容 兼容
  • 第8步事务A commit后,意味着排他锁被释放,第10步事务B的更新获得了排他锁,第11步证明成功更新。

我也想过在捞取一张”学习卡”的select的sql加上排他锁( FOR UPDATE)特性,这样从数据库层面来说,在这条学习卡状态改变之前,别的线程事务是不能 访问和修改这条数据的,也就达到了同步的目的,但是第一,这样做导致代码执行串行化,容易超时失败。第二,当我 进行到”激活学习卡”的时候,我发现”激活接口”因为是外部接口,所以外部接口拿不到锁导致无法回写更改数据,导致超时,我发现数据库锁也不能解决这个业务问题。

虽然事务,数据库锁都无法帮我解决”学习卡”的问题,但是我转移思路,事务和数据库锁虽然从数据库层面可以杜绝多事务产生的线程问题,但是并不符合现在的业务,更合适的是从代码层面解决–比如我是添加redis同步锁锁住”捞取一张学习卡”和”激活学习卡”两部操作,这样多线程也会同步运行,学习卡也不会重复激活,也不会因为外部接口因为拿不到行锁导致 激活无法进行。

有人说事务,数据库锁和同步到底是怎样的关系,我认为在这个业务实践中,已经很明白了,事务,数据库锁和同步代表不同的领域,具体问题具体分析,要根据业务出发来技术选型。

这篇文章我尽力都用实践证明所说的一些原理,但是仍然因为是菜鸟,肯定还有疏忽之处,这也是我最近工作的一次总结。发现今天已经是7月了,想想年初所说的事到现在还没有实现,实在十分懒散,下半年继续努力了~!

2019.11.29补充:这里面添加了mvcc的知识,美团的那篇科普文章对各种锁做了更详尽的说明,让我们能更了解mysql锁方面的运行机制,对mysql优化也有帮助作用。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040