SQL1: update scott.test1 a set a.num=a.num-1 where a.id=1 and exists (select 1 from scott.test1 b where a.id=b.id and b.num>0);

SQL2: update scott.test1 a set a.num=a.num-1 where a.id=1 and a.num>0;

以上两个SQL按道理讲实现的效果应该一样，如果num为0了，后面的更新都会返回0条被更新，实际SQL1会出现被更新为负值的情况。详情如下

实验一：

初始==>a.id=1 a.num=1

session1==>update scott.test1 a set a.num=a.num-1 where a.id=1 and exists (select 1 from scott.test1 b where a.id=b.id and b.num>0);

session2==>update scott.test1 a set a.num=a.num-1 where a.id=1 and exists (select 1 from scott.test1 b where a.id=b.id and b.num>0);

session1==>commit;

session2==>commit;

结果==>a.num=-1

实验二：

初始==>a.id=1 a.num=1

session1==>update scott.test1 a set a.num=a.num-1 where a.id=1 and a.num>0;

session2==>update scott.test1 a set a.num=a.num-1 where a.id=1 and a.num>0;

session1==>commit;

session2==>commit;

结果==>a.num=0