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 锁对”当前读”进行加锁，锁住行以及可能产生幻读的插入位置，阻止新的数据插入产生幻行。

会话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 事务中都需要修改这一行，那么都要等待对方释放共享锁才能进行，结果造成了死锁。