MySQL 如何在一个语句中更新一个数值后返回该值 …

2018-06-18 00:10:02来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

什么是竞态问题?

  假设有一个计数器,首先当前值自增长,然后获取到自增长之后的当前值。自增长后的值有可能被有些操作用来当做唯一性标识,因此并发的操作不能允许取得相同的值。

  为什么不能使用使用UPDATE语句更新计数器,然后SELECT语句获取自增长后的当前值?问题在于并发的操作有可能获取到相同的计数器值。

CREATE TABLE counters
  (
     id INT NOT NULL UNIQUE,   -- 计数器ID,多个计数器可以存在一个表中, 
     value INT                 -- 计数器当前值
  );
 
  -- 初始化计数器1,从10开始计数
  INSERT INTO counters VALUES (1, 10);
 
  -- 计数器1自增步长1
  UPDATE counters SET value = value + 1 WHERE id = 1;

  -- 获取计数器1自增长后的当前值
  SELECT value FROM counters WHERE id = 1;
 

 

如何避免竞态问题?

方法一:使用Transaction和SELECT FOR UPDATE

  如果一个Transaction中执行SELECT FOR UPDATE,该步操作会锁住该行记录。其它对该行记录的并发操作会被阻塞,直到当前SELECT FOR UPDATE所在Transaction提交或超时。

  START TRANSACTION;
 
  -- 锁定计数器1
  SELECT value FROM counters WHERE id = 1 FOR UPDATE;
 
   -- 计数器1自增长步长1
  UPDATE counters SET value = value + 1 WHERE id = 1;

  -- 获取自增长后的当前值
  SELECT value FROM counters WHERE id = 1;
    
  COMMIT;

 

方法二:在一个语句中完成UPDATE和SELECT

  方案一虽然可行并且可靠,但是加上了锁后一定程度上可能会影响一些性能。幸运的是我们可以使用方案二,在一个语句中完成UPDATE和SELECT,可以有两种方法实现。

 

实现1:通过Session变量。

-- 计数器1当前值自增长步长1
UPDATE counters
   SET value = (@newValue := value + 1)
WHERE id = 1;

-- 获取自增长之后的值
SELECT @newValue;

 

实现2:通过MySQL自带的LAST_INSERT_ID方法

  LAST_INSERT_ID方法常用的场景是获取自增长列最后一次插入的值。它还有另外一个用法,当传入一个值时它会返回传入的值,并且在下一次调用不含参数的LAST_INSERT_ID()方法时,还是会返回先前传入的值。

-- 计数器1自增长步长1,并通过LAST_INSERT_ID(Num)方法记录插入的值
UPDATE counters
   SET value = LAST_INSERT_ID(value + 1)
WHERE id = 1;

-- 获取最后一次插入的值 (自增长之后的当前值)
SELECT LAST_INSERT_ID();

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL数据库 Too many connections

下一篇:MySQL 8.0.2复制新特性(翻译)