2023年8月3日发(作者：)

mysql性能优化（五）mysql中SELECT+UPDATE处理并发更新问题解决⽅案⼀ 问题背景：假设MySQL数据库有⼀张会员表vip_member（InnoDB表），结构如下：当⼀个会员想续买会员（只能续买1个⽉、3个⽉或6个⽉）时，必须满⾜以下业务要求：•如果end_at早于当前时间，则设置start_at为当前时间，end_at为当前时间加上续买的⽉数•如果end_at等于或晚于当前时间，则设置end_at=end_at+续买的⽉数•续买后active_status必须为1（即被激活）⼆ 问题分析：对于上⾯这种情况，我们⼀般会先SELECT查出这条记录，然后根据查出记录的end_at再UPDATE start_at和end_at，伪代码如下（为uid是1001的会员续1个⽉）：代码如下:

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员if _at < NOW(): UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001else: UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001假如同时有两个线程执⾏上⾯的代码，很显然存在“数据覆盖”问题（即⼀个是续1个⽉，⼀个续2个⽉，但最终可能只续了2个⽉，⽽不是加起来的3个⽉）。三 解决⽅案：A、我想到的第⼀种⽅案是把SELECT和UPDATE合成⼀条SQL代码如下:

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 #duration:INTEGER# MONTH) ELSE DATE_ADD(end_at, INTERVAL #duration:INTEGER# MONTH) END, active_status=1, updated_at=NOW()WHERE uid=#uid:BIGINT#LIMIT 1; So easy！B、第⼆种⽅案：事务，即⽤⼀个事务来包裹上⾯的SELECT+UPDATE操作。 那么是否包上事务就万事⼤吉了呢？ 显然不是。因为如果同时有两个事务都分别SELECT到相同的vip_member记录，那么⼀样的会发⽣数据覆盖问题。那有什么办法可以解决呢？难道要设置事务隔离级别为SERIALIZABLE，考虑到性能不现实。 我们知道InnoDB⽀持⾏锁。查看MySQL官⽅⽂档（innodb locking reads）了解到InnoDB在读取⾏数据时可以加两种锁：读共享锁和写独占锁。 读共享锁是通过下⾯这样的SQL获得的：代码如下:SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;如果事务A获得了先获得了读共享锁，那么事务B之后仍然可以读取加了读共享锁的⾏数据，但必须等事务A commit或者roll back之后才可以更新或者删除加了读共享锁的⾏数据。代码如下:SELECT counter_field FROM child_codes FOR UPDATE;UPDATE child_codes SET counter_field = counter_field + 1; 如果事务A先获得了某⾏的写共享锁，那么事务B就必须等待事务A commit或者roll back之后才可以访问⾏数据。 显然要解决会员状态更新问题，不能加读共享锁，只能加写共享锁，即将前⾯的SQL改写成如下：代码如下:vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 FOR UPDATE # 查uid为1001的会员if _at < NOW(): UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001else: UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001另外这⾥特别提醒下：UPDATE/DELETE SQL尽量带上WHERE条件并在WHERE条件中设定索引过滤条件，否则会锁表，性能可想⽽知有多差了。C、第三种⽅案：乐观锁，类CAS机制 第⼆种加锁⽅案是⼀种悲观锁机制。⽽且FOR UPDATE⽅式也不太常⽤，联想到CAS实现的乐观锁机制，于是我想到了第三种解决⽅案：乐观锁。 具体来说也挺简单，⾸先SELECT SQL不作任何修改，然后在UPDATE SQL的WHERE条件中加上SELECT出来的vip_memer的end_at条件。代码如下:vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员cur_end_at = _atif _at < NOW():else: UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 AND e UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 AND end_at=cur_end_a这样可以根据UPDATE返回值来判断是否更新成功，如果返回值是0则表明存在并发更新，那么只需要重试⼀下就好了。四 ⽅案⽐较：三种⽅案各⾃优劣也许众说纷纭，只说说我⾃⼰的看法：•第⼀种⽅案利⽤⼀条⽐较复杂的SQL解决问题，不利于维护，因为把具体业务糅在SQL⾥了，以后修改业务时不但需要读懂这条SQL，还很有可能会修改成更复杂的SQL•第⼆种⽅案写独占锁，可以解决问题，但不常⽤•第三种⽅案应该是⽐较中庸的解决⽅案，并且甚⾄可以不加事务，也是我个⼈推荐的⽅案此外，乐观锁和悲观锁的选择⼀般是这样的（参考了⽂末第⼆篇资料）：•如果对读的响应度要求⾮常⾼，⽐如证券交易系统，那么适合⽤乐观锁，因为悲观锁会阻塞读•如果读远多于写，那么也适合⽤乐观锁，因为⽤悲观锁会导致⼤量读被少量的写阻塞•如果写操作频繁并且冲突⽐例很⾼，那么适合⽤悲观写独占锁

2023年8月3日发(作者：)

mysql性能优化（五）mysql中SELECT+UPDATE处理并发更新问题解决⽅案⼀ 问题背景：假设MySQL数据库有⼀张会员表vip_member（InnoDB表），结构如下：当⼀个会员想续买会员（只能续买1个⽉、3个⽉或6个⽉）时，必须满⾜以下业务要求：•如果end_at早于当前时间，则设置start_at为当前时间，end_at为当前时间加上续买的⽉数•如果end_at等于或晚于当前时间，则设置end_at=end_at+续买的⽉数•续买后active_status必须为1（即被激活）⼆ 问题分析：对于上⾯这种情况，我们⼀般会先SELECT查出这条记录，然后根据查出记录的end_at再UPDATE start_at和end_at，伪代码如下（为uid是1001的会员续1个⽉）：代码如下:

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员if _at < NOW(): UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001else: UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001假如同时有两个线程执⾏上⾯的代码，很显然存在“数据覆盖”问题（即⼀个是续1个⽉，⼀个续2个⽉，但最终可能只续了2个⽉，⽽不是加起来的3个⽉）。三 解决⽅案：A、我想到的第⼀种⽅案是把SELECT和UPDATE合成⼀条SQL代码如下:

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 #duration:INTEGER# MONTH) ELSE DATE_ADD(end_at, INTERVAL #duration:INTEGER# MONTH) END, active_status=1, updated_at=NOW()WHERE uid=#uid:BIGINT#LIMIT 1; So easy！B、第⼆种⽅案：事务，即⽤⼀个事务来包裹上⾯的SELECT+UPDATE操作。 那么是否包上事务就万事⼤吉了呢？ 显然不是。因为如果同时有两个事务都分别SELECT到相同的vip_member记录，那么⼀样的会发⽣数据覆盖问题。那有什么办法可以解决呢？难道要设置事务隔离级别为SERIALIZABLE，考虑到性能不现实。 我们知道InnoDB⽀持⾏锁。查看MySQL官⽅⽂档（innodb locking reads）了解到InnoDB在读取⾏数据时可以加两种锁：读共享锁和写独占锁。 读共享锁是通过下⾯这样的SQL获得的：代码如下:SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;如果事务A获得了先获得了读共享锁，那么事务B之后仍然可以读取加了读共享锁的⾏数据，但必须等事务A commit或者roll back之后才可以更新或者删除加了读共享锁的⾏数据。代码如下:SELECT counter_field FROM child_codes FOR UPDATE;UPDATE child_codes SET counter_field = counter_field + 1; 如果事务A先获得了某⾏的写共享锁，那么事务B就必须等待事务A commit或者roll back之后才可以访问⾏数据。 显然要解决会员状态更新问题，不能加读共享锁，只能加写共享锁，即将前⾯的SQL改写成如下：代码如下:vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 FOR UPDATE # 查uid为1001的会员if _at < NOW(): UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001else: UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001另外这⾥特别提醒下：UPDATE/DELETE SQL尽量带上WHERE条件并在WHERE条件中设定索引过滤条件，否则会锁表，性能可想⽽知有多差了。C、第三种⽅案：乐观锁，类CAS机制 第⼆种加锁⽅案是⼀种悲观锁机制。⽽且FOR UPDATE⽅式也不太常⽤，联想到CAS实现的乐观锁机制，于是我想到了第三种解决⽅案：乐观锁。 具体来说也挺简单，⾸先SELECT SQL不作任何修改，然后在UPDATE SQL的WHERE条件中加上SELECT出来的vip_memer的end_at条件。代码如下:vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员cur_end_at = _atif _at < NOW():else: UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 AND e UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 AND end_at=cur_end_a这样可以根据UPDATE返回值来判断是否更新成功，如果返回值是0则表明存在并发更新，那么只需要重试⼀下就好了。四 ⽅案⽐较：三种⽅案各⾃优劣也许众说纷纭，只说说我⾃⼰的看法：•第⼀种⽅案利⽤⼀条⽐较复杂的SQL解决问题，不利于维护，因为把具体业务糅在SQL⾥了，以后修改业务时不但需要读懂这条SQL，还很有可能会修改成更复杂的SQL•第⼆种⽅案写独占锁，可以解决问题，但不常⽤•第三种⽅案应该是⽐较中庸的解决⽅案，并且甚⾄可以不加事务，也是我个⼈推荐的⽅案此外，乐观锁和悲观锁的选择⼀般是这样的（参考了⽂末第⼆篇资料）：•如果对读的响应度要求⾮常⾼，⽐如证券交易系统，那么适合⽤乐观锁，因为悲观锁会阻塞读•如果读远多于写，那么也适合⽤乐观锁，因为⽤悲观锁会导致⼤量读被少量的写阻塞•如果写操作频繁并且冲突⽐例很⾼，那么适合⽤悲观写独占锁