mysql:如何解决数据修改冲突(事务+行级锁的实际运用)

摘要:最近做一个接诊需求遇到一个问题,假设一个订单咨询超过3次就不能再接诊,但如果两个医生同时对该订单进行咨询,查数据库的时候都能查到满足条件的该订单,那两个医生都能接诊,所谓接诊可以理解为更新了接诊次数,此时就出现了bug(接诊超过3次)。

其实这个问题看似很明朗,但想要完全解决需要理解事务和锁的概念,以前总对事务的隔离级别和锁有点云里雾里,现在可以通过这个案例可以理清楚。

事务

操作数据库最小的工作单位,简单讲就是将多条dml(增删改)语句联合完成。要么同时成功,要么同时失败。看到这里你可能会发现光加事务解决不了上述问题,而且加了事务之后,多条事务之间的相互关系就涉及到事务的隔离级别,所以接着往下看。

事务隔离级别

READ UNCOMMITTED(读未提交,脏读)

事务中的修改,即使没有提交,对其他会话也是可见的。可以读取未提交的数据——脏读。脏读会导致很多问题,一般不适用这个隔离级别.。

 

-- ------------------------- read-uncommitted实例 ------------------------------
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ UNCOMMITTED";
-- commit;

-- Session B
SELECT * FROM USER;

//SessionB Console 可以看到Session A未提交的事物处理,在另一个Session 中也看到了,这就是所谓的脏读
id    name
2    READ UNCOMMITTED
34    READ UNCOMMITTED

 

READ COMMITTED(读已提交,不可重复读) 

一般数据库都默认使用这个隔离级别(MySQL 不是), 这个隔离级别保证了一个事务如果没有完全成功(commit 执行完),事务中的操作对其他会话是不可见的。

-- ------------------------- read-cmmitted实例 ------------------------------
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ  COMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ COMMITTED";
-- COMMIT;

-- Session B
SELECT * FROM USER;

//Console OUTPUT:
id    name
2    READ UNCOMMITTED
34    READ UNCOMMITTED


---------------------------------------------------
-- 当 Session  A执行了commit,Session B得到如下结果:
id    name
2    READ COMMITTED
34    READ COMMITTED

REPEATABLE READ (可重复读)

一个事务中多次执行统一读 SQL,返回结果一样。这个隔离级别解决了脏读的问题,幻读问题。这里指的是 innodb 的 rr 级别,innodb 中使用 next-key 锁对"当前读"进行加锁,锁住行以及可能产生幻读的插入位置,阻止新的数据插入产生幻行。

mysql:如何解决数据修改冲突(事务+行级锁的实际运用)

会话T1事务中执行一次查询,然后会话T2新插入一行记录,这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 的查询再次对表进行检索,但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”,因为对T1来说这一行就像突然 出现的一样。innoDB 的 RR 级别无法做到完全避免幻读。

SERIALIZABLE (可串行化)

最强的隔离级别,通过给事务中每次读取的行加锁,写加写锁,保证不产生幻读问题,但是会导致大量超时以及锁争用问题。

mysql默认的隔离级别是可重复读,看到这里大家应该明白,即使隔离级别是可重复读,但因为select操作时并未加锁,导致都会查到符合条件的数据,所以这里要引入一个锁的概念:行级锁。

行级锁

  • 共享锁(S) 共享锁也称为读锁,读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰;

  • 排他锁(X) 排他锁也称为写锁,一个写锁会阻塞其他的写锁或读锁,保证同一时刻只有一个连接可以写入数据,同时防止其他用户对这个数据的读写。

总结(解决方案)

其实上文分析了那么多,最后的解决方案很简单。就是在原来的read和update合起来加事务,原来的select语句加排他锁,即在select语句后面加for update。假如有事务A,B,加入排他锁之后,假设事务A先获得锁,事务B必须等到事务A commit之后才能开始select,所以读到的是最新修改的数据。至于为什么不加共享锁,除了可能造成脏写之后,在这种情况下还可能造成死锁。假如两个事务 A 、 B 都读取同一行记录,那么在这一行就加上了共享锁,但是 A 和B 事务中都需要修改这一行,那么都要等待对方释放共享锁才能进行,结果造成了死锁。

 

发表评论

评论已关闭。

相关文章