MySQL 如何在一个语句中更新一个数值后返回该值 -- 自增长种子竞态问题处理

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

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

<span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> counters
  (
     id </span><span style="color: #0000ff;">INT</span> <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">UNIQUE</span>,   <span style="color: #008080;">--</span><span style="color: #008080;"> 计数器ID，多个计数器可以存在一个表中, </span>
     value <span style="color: #0000ff;">INT</span>                 <span style="color: #008080;">--</span><span style="color: #008080;"> 计数器当前值</span>
<span style="color: #000000;">  );
 
  </span><span style="color: #008080;">--</span><span style="color: #008080;"> 初始化计数器1，从10开始计数</span>
  <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> counters <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">1</span>, <span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">);
 
  </span><span style="color: #008080;">--</span><span style="color: #008080;"> 计数器1自增步长1</span>
  <span style="color: #0000ff;">UPDATE</span> counters <span style="color: #0000ff;">SET</span> value <span style="color: #808080;">=</span> value <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  </span><span style="color: #008080;">--</span><span style="color: #008080;"> 获取计数器1自增长后的当前值</span>
  <span style="color: #0000ff;">SELECT</span> value <span style="color: #0000ff;">FROM</span> counters <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
 </span>

如何避免竞态问题？

方法一：使用Transaction和SELECT FOR UPDATE

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

  START <span style="color: #0000ff;">TRANSACTION</span><span style="color: #000000;">;
 
  </span><span style="color: #008080;">--</span><span style="color: #008080;"> 锁定计数器1</span>
  <span style="color: #0000ff;">SELECT</span> value <span style="color: #0000ff;">FROM</span> counters <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">FOR</span> <span style="color: #0000ff;">UPDATE</span><span style="color: #000000;">;
 
   </span><span style="color: #008080;">--</span><span style="color: #008080;"> 计数器1自增长步长1</span>
  <span style="color: #0000ff;">UPDATE</span> counters <span style="color: #0000ff;">SET</span> value <span style="color: #808080;">=</span> value <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  </span><span style="color: #008080;">--</span><span style="color: #008080;"> 获取自增长后的当前值</span>
  <span style="color: #0000ff;">SELECT</span> value <span style="color: #0000ff;">FROM</span> counters <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
    
  </span><span style="color: #0000ff;">COMMIT</span>;

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

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

实现1：通过Session变量。

<span style="color: #008080;">--</span><span style="color: #008080;"> 计数器1当前值自增长步长1</span>
<span style="color: #0000ff;">UPDATE</span><span style="color: #000000;"> counters
   </span><span style="color: #0000ff;">SET</span> value <span style="color: #808080;">=</span> (<span style="color: #008000;">@newValue</span> :<span style="color: #808080;">=</span> value <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
</span><span style="color: #008080;">--</span><span style="color: #008080;"> 获取自增长之后的值</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #008000;">@newValue</span>;

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

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

<span style="color: #008080;">--</span><span style="color: #008080;"> 计数器1自增长步长1，并通过LAST_INSERT_ID(Num)方法记录插入的值</span>
<span style="color: #0000ff;">UPDATE</span><span style="color: #000000;"> counters
   </span><span style="color: #0000ff;">SET</span> value <span style="color: #808080;">=</span> LAST_INSERT_ID(value <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
</span><span style="color: #008080;">--</span><span style="color: #008080;"> 获取最后一次插入的值 （自增长之后的当前值）</span>
<span style="color: #0000ff;">SELECT</span> LAST_INSERT_ID();

MySQL 如何在一个语句中更新一个数值后返回该值 -- 自增长种子竞态问题处理

标签：comm   col   sql   font   sel   code   有一个   nsa   方法