MySQL知识综合案例（IF-ELSE、存储过程、悲观锁、乐观锁）

前言

假设 MySQL 中有一张会员表 vip_member（引擎为 InnoDB），结构如下

# 创建 vip_member 
DROP TABLE IF EXISTS vip_member;


# 插入一条数据
INSERT INTO vip_member
VALUES
	( 1001, DATE_SUB( now(), INTERVAL 6 DAY ), DATE_SUB( now(), INTERVAL 3 DAY ), DATE_SUB( now(), INTERVAL 3 DAY ), 0 );

当一个用户想续买会员时，必须满足以下业务要求：

• 如果 end_at 早于当前时间，则设置 start_at 为当前时间，end_at = 当前时间 + 续买月数
• 如果 end_at 等于或晚于当前时间，则设置 end_at = end_at + 续买月数。

续买后 active_status 必须为1（即被激活）

方案一

对于上面这种情况，首先想到的是从会员表中查处该用户的记录，根据该记录的 end_at 再分别设置 start_at 和 end_at ，代码如下：

UPDATE
  vip_member
SET
  start_at = 
  	CASE WHEN end_at < NOW() THEN NOW()
    ELSE start_at
  END,
  end_at = 
  	CASE WHEN end_at < NOW() THEN DATE_ADD(NOW(), INTERVAL 1 MONTH)
    ELSE DATE_ADD(end_at, INTERVAL 1 MONTH)
  END,
  active_status = 1,
  updated_at = NOW()
WHERE
  uid =1001;

如果同时有事务执行上面的代码，很显然存在“数据覆盖”问题（即一个是续1个月，一个续2个月，但最终可能只续了2个月，而不是加起来的3个月）。

方案二

我们知道 InnoDB 支持行锁。查看 MySQL 官方文档了解到InnoDB在读取行数据时可以加两种锁：读共享锁 和写 独占锁。

读共享锁是通过下面这样的 SQL 获得的：

SELECT * FROMparent WHERE NAME = 'Jones'LOCK IN SHARE MODE;

如果事务 A 获得了先获得了读共享锁，那么事务 B 之后仍然可以读取加了读共享锁的行数据，但必须等事务提交或者回滚之后，才可以更新或者删除加了读共享锁的行数据。

写独占锁是通过下面这样的 SQL 获得的：

SELECT counter_field FROM child_codesFOR UPDATE;

如果事务 A 先获得了某行的写独占锁，那么事务 B 就必须等待事务提交或者回滚之后才可以加独占锁。

显然要解决会员状态更新问题，不能加读共享锁，只能加写共享锁，即将前面的SQL改写成如下：

#  创建一个存储过程
DROP PROCEDURE IF EXISTS renewMembership;

CREATE PROCEDURE renewMembership(IN m INT) 
	BEGIN
		DECLARE endDate INT DEFAULT 0;
		SELECT end_at INTO endDate 
		FROM vip_member 
		WHERE uid = 1001 
	        FOR UPDATE;
		IF endDate < NOW() THEN UPDATE vip_member 
				SET start_at = NOW(),
					end_at = DATE_ADD( NOW(), INTERVAL m MONTH ),
					active_status = 1,
					updated_at = NOW() 
				WHERE uid = 1001;
		ELSE UPDATE vip_member 
			SET end_at = DATE_ADD( end_at, INTERVAL m MONTH ),
				active_status = 1,
				updated_at = NOW() 
			WHERE uid = 1001;
	    END IF;
	END;

# 调用存储过程
CALL renewMembership(1);

方案三

方案二实际是一种悲观锁机制，对应的有 乐观锁 的解决方案，如下代码所示：

# 创建存储过程
DROP PROCEDURE IF EXISTS renewMembership;

CREATE PROCEDURE renewMembership(IN m INT)
	BEGIN
		SELECT end_at INTO @endDay FROM vip_member WHERE uid=1001;
		IF @endDay < NOW() THEN UPDATE vip_member 
			SET start_at=NOW(), 
				end_at=DATE_ADD(NOW(), INTERVAL m MONTH),
				active_status=1, updated_at=NOW() 
			WHERE uid=1001 AND end_at=@endDay;
		ELSE UPDATE vip_member 
			SET end_at=DATE_ADD(end_at,INTERVAL m MONTH),
				active_status=1,
				updated_at=NOW()
			WHERE uid=1001 AND end_at=@endDay;
		END IF;
	END;

# 调用存储过程
call renewMembership(1);

即在更新会员信息时，只需额外的判断当前的续费前的会员截止日期与本次更新过程中提前缓存的截止日期相同，以确保其它更新过程没有对当前记录进行操作。