老师请问第5章7节 - 关于数据库乐观锁的一个问题

Question

老师在视频中讲解如下sql是一个乐观锁的例子
update table1 set num = num+1,version = version + 1 where version = 1 and id = 5

按照自己的认知，一般mysql(innodb rr事务隔离级别)中，update语句会被转化为一个悲观锁来执行，也就是在执行之前先lock住，再尝试进行修改，不管修改是否成功，最后再unlock，返回update的执行结果，当然如果mysql数据库按照cas实现单条update的乐观锁应该也能符合程序预期，所以这里比较困惑，请老师赐教，感谢

Answer 1

提问者 宝慕林5249423 2020-01-27 22:33:24

cas同样是通过加锁来实现并发场景对共享数据的数据操作符合程序预期

    java中通过系统调用（调用cpu原语指令）完成：合并compare_and_swap操作为原子操作指令。

    同理数据库update也是通过锁完成并发场景对数据一致性的预期，如下case

- SELECT iD, val1, val2       FROM theTable       WHERE iD = @theId;
- {code that calculates new values}
- BEGIN TRANSACTION;
- UPDATE anotherTable       SET col1 = @newCol1,
          col2 = @newCol2       WHERE iD = @theId;
- UPDATE theTable       SET val1 = @newVal1,
          val2 = @newVal2       WHERE iD = @theId           AND val1 = @oldVal1           AND val2 = @oldVal2;
- {if AffectedRows == 1 }
-     COMMIT TRANSACTION;
-     {go on with your other code}
- {else}
-     ROLLBACK TRANSACTION;
-     {decide what to do since it has gone bad... in your code}
- {endif}

    这时如果有两个app线程执行如上代码逻辑，已知（上述逻辑中我们把update的执行操作和事务提交操作分开）A线程执行到update，但并未commit，B线程尝试执行update语句，在mysql rr事务隔离级别下，B线程会等待A线程commit事务后，再执行，在此之间B线程只能一直等待，所以因为B线程被推迟了，update where 语句理所当然会执行失败。

总结：

1. java中cas通过cpu保证并发场景下数据一致

2. mysql中update语句通过锁保证数据一致，这里区别于java cas的是，mysql update语句并发场景下会阻塞其他线程更新相同数据，进而产生等待（具体表现为：mysql中事务排队，应用线程等待transaction执行）可能会导致请求执行超时，数据库大事务等问题，影响系统稳定性？

3. 援引stackflow：https://stackoverflow.com/questions/17431338/optimistic-locking-in-mysql
   

• 悟空 #1

  优秀，点赞

Answer 2

提问者 宝慕林5249423 2020-01-12 22:08:57

老师通过数据库update where 条件的方式来描述乐观锁，可能是因为update语句的affectRows结果，可能会返回大于0（符合预期）或者等于0（遇到写并发），从这个角度来看，确实和cas很像（没有写并发，执行成功，遇到写并发，执行失败，进而可以发起重试），不过还是想要请教一下关于update加锁的问题，个人理解这一点在实际开发中很重要（例如涉及：死锁，duplicate key..etc）希望老师不吝赐教

• 悟空 #1

  ​如果update不加锁，那么就不是CAS了，CAS要保证单条命令的原子性

• 悟空 #2

  数据库update的乐观锁，没有写并发，执行成功，遇到写并发，执行失败，进而可以发起重试。

• 提问者 宝慕林5249423 回复 悟空 #3

  好的，感谢老师，所以执行update是先lock住数据，再执行更新，如果这样的话，还是会存在发生死锁的情况，比如A向B转账的同时B向A转账，如果加锁顺序不一致，则会导致循环等待

点开查看后面1条评论

Answer 3

提问者 宝慕林5249423 2020-01-12 10:29:19

补充问题：mysql针对事务读写并行的情况进行了优化（mvcc），但是当两个写操作事务并行的时候，个人理解事务和事务之间也需要满足happen-before原则，即写操作和写操作之间不能并行，必然满足先后关系

如果有多个线程并发的执行如下sql语句

update table1 set spock = spock - 1 where id = ? and spock > 0;

猜想：如果多个线程高并发的执行如上sql，mysql使用cas操作共享数据不一定会比使用·悲观锁执行效率更高，因为多个并发写情况下使用cas造成更多的自旋，而悲观锁可以避免这一点，自己没有通读过mysql源码，以上仅是猜想

• 悟空 #1

  效率上，CAS是不一定比悲观锁更高，高并发下CAS自旋多