数据库-关于丢失更新和乐观锁的那些故事

转自http://www.hetaoblog.com/%E6%95%B0%E6%8D%AE%E5%BA%93-%E5%85%B3%E4%BA%8E%E4%B8%A2%E5%A4%B1%E6%9B%B4%E6%96%B0%E5%92%8C%E4%B9%90%E8%A7%82%E9%94%81%E7%9A%84%E9%82%A3%E4%BA%9B%E6%95%85%E4%BA%8B/

1．问题场景

a. 用户A打开应用的界面，看到数据库的某条记录

b.用户B打开应用的界面，看到同样一条记录

c. 用户A对记录做了修改

d. 对于web应用而言[假设没有应用comet类似技术]，通常B不知道这个修改，这时B也对同样这条记录做修改，那B就有可能覆盖A做的修改;

这个问题在数据库中被称为丢失更新问题

2．我自己对这个问题的理解过程是这样的：

a. 不知道这个问题

我在做开发好长时间之后才意识到这个问题，意识到这个问题之后，我后来发现很长一段时间内都没真正搞明白为什么这是个问题-_- 而且我发现现在周围的很多同事，尤其是新毕业的学生，其实也一直过了很长时间都没明白这个问题，这说明吧不知道这个丢失更新问题是一个非常普遍的问题：）

b.用信号量以及操作之前再次验证的方法解决

最开始的时候，测试发现了这样一个问题，要求解决，我把操作系统的教科书搬来，对照着写了一个信号量semaphore类[那时候还是jdk 1.4.2，jdk里面没有concurrent包]，花了好长时间测试这个semaphore的实现是正确的[重复发明轮子的血泪史..]，

然后用来控制这个操作，每次操作前获取信号量，然后验证，再做真正的数据库操作。。。相当于在应用层每次都只做一件事。

c. 再次理解

再后来，我看了Tom的这边9i和10g的书，书中提到前面的丢失更新过程，大概有点明白为什么这是个问题


但是其实我有个疑问，对于数据库中的记录而言，A做的修改本来就有可能被B覆盖的，为什么这会是一个丢失更新问题呢？ 正好项目里面又出现了类似的情况，我仔细观察了下，终于明白为什么这是个问题，以及为什么要使用对应的乐观锁悲观锁方案了。下面对此做详细说明

3. 一个比较清楚的场景

下面这个假设的实际场景可以比较清楚的帮助我们理解这个问题：

   1. 假设当当网上用户下单买了本书，这时数据库中有条订单号为 001的订单，其中有个status字段是’有效’,表示该订单是有效的；
   2. 后台管理人员查询到这条001的订单，并且看到状态是有效的
   3. 用户发现下单的时候下错了，于是撤销订单，假设运行这样一条 SQL: update order_table set status = ‘取消’ where order_id = 001;
   4. 后台管理人员由于在b这步看到状态有效的，这时，虽然用户在c 这步已经撤销了订单，可是管理人员并未刷新界面，看到的订单状态还是有效的，于是点击”发货”按钮，将该订单发到物流部门，同时运行类似如下SQL，将订单状态改成已发货:update order_table set status = ‘已发货’ where order_id = 001

如果当当的系统这样实现，显然不对了，肯定要挨骂了，明明已经取消了订单，为什么还会发货呢？而且确实取消订单的操作发生在发货操作之前啊。 因为在这样的实现下，后台管理人员无论怎么做都有可能会出错，因为他打开系统看到有效的订单和他点发货之间肯定有个时间差，在这个时间差的时候总是存在用户取消订单的可能。

4. 当时的详细解决方法 几年前当测试人员告诉我系统存在这个问题的时候，我的解决方法是这样的， 首先，先把操作系统的教科书搬来，然后对照着了一个semaphore,然后反复测试各种情况证明写的是正确的； 然后，

1. 获取一个信号量，保证每次只能有一个线程进入下面的步骤

2. 检查数据库，看这条订单是否状态是有效的

a.    如果有效则继续，进入发货步骤 b)        如果无效则返回，释放信号量，告诉用户状态已经发生改变

3. 发货，释放信号量

看到这里，也许很多人要骂我蠢了，直接把SQL语句改成下面这样吧就可以了么？ update order_table set status = ‘已发货’ where order_id = 001 and status = ‘有效’ 是的，的确是这样。虽然我当时的项目的情况比和这个稍微复杂一点，涉及到多张表格，不能直接这么做，但当时的确不知道这个更新丢失问题，也没想到合适的类似方式，于是就在应用层做了这么一个每次实际上只能有一个用户在做真正的更新这样一个方式来解决，这样做的结果是，在应用层单独做了类似这么一个锁的机制。我记得当时的项目毕业答辩的时候，老师问我同步的这个问题不直接用数据库的锁的方案来解决？我当时胡乱回答了下，后来想起来，其实压根没理解老师的意思-_- 而且这样做有一个问题，假设在特殊情况下，这条订单被DBA直接修改了，没有经过应用，那么应用做这个操作也会是错的，因为在2.a到3之前的这段时间，有可能正好是DBA直接修改的时候。那么3做的操作也是不对的。 而且，现实情况是在后来的几年开发过程中，我也的确在一些不同的项目代码中看到，其他很多人也在使用类似的代码解决测试人员告诉他们的这些同步问题-_-

5．正确而简洁的解决方法

问题清楚了，也说明了我曾经使用的解决方案也是一个简洁直接的解决方案，纯粹是把简单问题复杂化，下面说说实际有效的解决方案； 就这个丢失更新问题，可以通过数据库的锁来实现，基本两种思路，一种是悲观锁，另外一种是乐观锁； 简单的说就是一种假定这样的问题是高概率的，最好一开始就锁住，免得更新老是失败；另外一种假定这样的问题是小概率的，最后一步做更新的时候再锁住，免得锁住时间太长影响其他人做有关操作；

6． 乐观锁的方法

这里先说web开发中常用的乐观锁的方法：

1.很简单，就是使用前面所说的这样一条SQL，这其实是所谓使用”前镜像”的方式来保证需要更新的数据是符合要求的，

update order_table set status = ‘已发货’ where order_id = 001 and status = ‘有效’ Tom的书上举的例子是对所有列做更新，所以他的SQL大致如下 Update table set col1 = newcol1value, col2 = newcol2value…. where col1 = oldcol1value and col2 = oldcol2value…. 这个我觉得需要根据应用具体分析，如果需要判断所有的值，那就判断所有的值，如果只关心其中一个或部分值，那只需要取相关的值就好了，就比如这里的订单的状态

2.使用版本列[比如时间戳]

这个方法比较简单，也最常用，就是在数据库表格中加一列last_modified_date，就是最后更新的时间，每次更新的时候都将这列设成 systimestamp，当前系统时间；

然后每次更新的时候，就改成这样 Update table set col = newvalue where id = ** and last_modified_date = old last_modified_date 这样，就可以检验出数据库的值是否在上次查看和这次更新的时候发生了变化，如果发生了变化，那么last_modified_date就变化了，以后的更新就会返回更新了0行，系统就可以通知用户数据发生了变化，然后选择刷新数据或者其他流程。

至于这个last_modified_date的维护，可以选择让应用每次都维护这个值，或者是使用存储过程来包装更新的操作，或者是使用触发器来更新相关的值。几种方法各有利弊，比如应用维护需要保证每段相关代码都正确的维护了这个值；存储过程有一定的开销，通常很多开发对写存储过程可能也不熟练；触发器是简单的实现，但是也是有开销的。具体使用哪种方法需要根据实际情况具体取舍。

3.使用校验或Hash值

这种方法和前面的方法类似，无非是根据其他有实际意义的列来计算出一个虚拟的列，我个人觉得TOM在介绍这个纯粹是介绍了一种”奇技淫巧”，反正我是在实际过程中不知道哪里会需要这样的解决方案，或许也是因为我知道的太少了吧：）

4.使用Oracle 10g的ORA_ROWSCN

这个就是利用10g的一个ora_rowscn特性，可以对每行做精确追踪，不过这个要求在create table的时候就指定相关参数，表格如果创建了以后就不能用alter table来修改了，因为这依赖于物理的实际存储。 同样，我觉得这也可以归为”奇技淫巧”一类； 具体如果有兴趣了解详情的话，可以参考Tom的书

再来篇悲观锁的
http://www.hetaoblog.com/database-lost-update-pessimistic-lock/

在前天的文章中写了丢失更新问题和乐观锁的解决方法，这里介绍下另外一种解决方法，就是悲观锁的做法

1. 在回到之前说的假想的实际场景：

a. 假设当当网上用户下单买了本书，这时数据库中有条订单号为001的订单，其中有个status字段是’有效’,表示该订单是有效的；

b. 后台管理人员查询到这条001的订单，并且看到状态是有效的

c. 用户发现下单的时候下错了，于是撤销订单，假设运行这样一条SQL: update order_table set status = ‘取消’ where order_id = 001;

d. 后台管理人员由于在b这步看到状态有效的，这时，虽然用户在c这步已经撤销了订单，可是管理人员并未刷新界面，看到的订单状态还是有效的，于是点击”发货”按钮，将该订单发到物流部门，同时运行类似如下SQL，将订单状态改成已发货:update order_table set status = ‘已发货’ where order_id = 001

之前说的乐观锁的解决方法是在最后一步d做数据库更新的时候，使用前镜像或者时间戳，

将SQL改成

update order_table set status = ‘已发货’ where order_id = 001 and status = ‘有效’

或

Update table set col = newvalue where id = ** and last_modified_date = old last_modified_date

来保证最后一步的更新是有效的，数据在之前没发生过变化。之所以说是乐观的做法，是因为这种做法假设数据不会发生变化，直到最后才做检查，所以是乐观的；

2. 传统悲观锁的做法

那么如果我们假设数据在这之前很可能发生变化，那么可以采取悲观的做法，就是在第二步后台人员做查询的时候(做select的时候)就将数据锁住，使得之后[就是在c的时候]，其他用户不能对数据做更改，从而保证d这步做发货操作的时候该订单始终是有效的。

总结成通用的做法是，

a. 用户查询一条记录，并试图后续要做更新的时候，那么在查询的时候使用Select *** for update nowait 语句，通过添加for update nowait语句，将这条记录锁住，避免其他用户更新，从而保证后续的更新是在正确的状态下更新的。

b． 然后，在保持这个连接的时候，后续在做真正的更新

当然，这样做有一个前提，就是要求用户保持这个数据库连接； 这在90年代的C/S程序中是比较可行的，但是现在的web应用大多已经不是这个结构，那已经不合适使用这样简单的悲观锁了。道理很简单，如果用户打开页面做查询的时候就将记录锁住，并且保持这个连接，那对连接的占用太长了，整个系统能承受的并发量就很小了。以oracle 10g为例，默认情况下，最大连接数是150，也就是说最多只能承受150个用户同时访问了。

3. 传统悲观锁做法的变通

在我发表之前的乐观锁的做法的时候，在论坛上很多人提出了质疑，认为就假想的实际场景根本不需要使用所谓乐观锁的做法，只需要在最后更新之前再检查下该订单当时的状态就好了。

也就是在d这步，先做个数据库查询，如果状态仍然是有效的话，然后再更新；

如果这样做的话，其实在d这步做查询检查状态的时候，必须用类似悲观锁的做法，使用select *** for update nowait，同时将这条记录锁住。这样的先查询确定状态才是有效的，否则的话，查询完到运行第二条SQL这段时间，状态仍然会有可能发生改变的。

这种做法其实是悲观锁的一种变通做法，而简单的做普通查询，本质上是没有解决问题的。

3. 应该使用哪种方法

所有悲观锁的做法都适合于状态被修改的概率比较高的情况，具体是否合适则需要根据实际情况判断。

我个人认为现在大部分情况下应该都使用乐观锁。

而且，如果是采用刚才所说的悲观锁的变通做法，有一个明显的缺点就是多做了一次数据库查询，降低了效率。

http://www.hetaoblog.com/pessimistic-optimistic-locking-views/
来篇总结的

在之前写了数据库的经典丢失更新问题，以及对应的乐观锁做法和悲观锁做法后，将文章转载到BBS的时候引起了很多的讨论，很多网友发表了不同的观点。这里再次做集中讨论：

本质上，数据库的乐观锁做法和悲观锁做法主要就是解决下面假设的场景，避免丢失更新问题：

一个比较清楚的场景

下面这个假设的实际场景可以比较清楚的帮助我们理解这个问题：

   1. 假设当当网上用户下单买了本书，这时数据库中有条订单号为001的订单，其中有个status字段是’有效’,表示该订单是有效的；
   2. 后台管理人员查询到这条001的订单，并且看到状态是有效的
   3. 用户发现下单的时候下错了，于是撤销订单，假设运行这样一条SQL: update order_table set status = ‘取消’ where order_id = 001;
   4. 后台管理人员由于在b这步看到状态有效的，这时，虽然用户在c这步已经撤销了订单，可是管理人员并未刷新界面，看到的订单状态还是有效的，于是点击”发货”按钮，将该订单发到物流部门，同时运行类似如下SQL，将订单状态改成已发货:update order_table set status = ‘已发货’ where order_id = 001

其实之前已经分别对乐观锁的做法和悲观锁的做法做了详细的分析，

这里引用wiki的定义做更权威的引用说明

http://en.wikipedia.org/wiki/Lock_%28database%29

There are mechanisms employed to manage the actions of multiple concurrent users on a database – the purpose is to prevent lost updates and dirty reads. The two types of locking are Pessimistic and Optimistic Locking.

    * Pessimistic locking: A user who reads a record, with the intention of updating it, places an exclusive lock on the record to prevent other users from manipulating it. This means no one else can manipulate that record until the user releases the lock. The downside is that users can be locked out for a very long time, thereby slowing the overall system response and causing frustration.
          o Where to use pessimistic locking: This is mainly used in environments where data-contention (the degree of users request to the database system at any one time) is heavy; where the cost of protecting data through locks is less than the cost of rolling back transactions if concurrency conflicts occur. Pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency requires a persistent connection to the database and is not a scalable option when users are interacting with data, because records might be locked for relatively large periods of time. It is not appropriate for use in web application development.

本质上，这里wiki的意思就是，悲观锁和乐观锁都是为了解决丢失更新问题或者是脏读。悲观锁和乐观锁的重点就是是否在读取记录的时候直接上锁。悲观锁的缺点很明显，需要一个持续的数据库连接，这在web应用中已经不适合了。

观点1：只有冲突非常严重的系统才需要悲观锁；

分析：这是更准确的说法；我在原文中说到：

“所有悲观锁的做法都适合于状态被修改的概率比较高的情况，具体是否合适则需要根据实际情况判断。”，表达的也是这个意思，不过说法不够准确；的确，之所以用悲观锁就是因为两个用户更新同一条数据的概率高，也就是冲突比较严重的情况下，所以才用悲观锁。

观点2：最后提交前作一次select for update检查，然后再提交update也是一种乐观锁的做法

分析：这是更准确的说法；

的确，这符合传统乐观锁的做法，就是到最后再去检查。但是wiki在解释悲观锁的做法的时候，’It is not appropriate for use in web application development.’， 现在已经很少有悲观锁的做法了，所以我自己将这种二次检查的做法也归为悲观锁的变种，因为这在所有乐观锁里面，做法和悲观锁是最接近的，都是先 select for update，然后update

*****除了上面的观点1和观点2是更准确的说法，下面的所有观点都是错误的***********

观点3：这个问题的原因是因为数据库隔离级别是 uncommitted read级别；

分析：这个观点是错误的；

这个过程本身就是在read committed隔离级别下发生的，从a到d每一步，尤其是d这步，并不是因为读到了未提交的数据，仅仅是因为用户界面没有刷新[事实上也不可能做自动刷新，这样相当于数据库一发生改变立刻要刷新了，这需要监听数据库了，显然这是简单问题复杂化了];

观点4：悲观锁是指一个用户在更新数据的时候，其他用户不能读取这条记录；也就是update阻塞读才叫悲观锁；

分析：这个观点是错的；

这在db2背景的开发中尤其常见；因为db2默认就是update会阻塞读；但是这是各个数据库对读写的时候上锁的并发处理实现不一样。但这根本不是悲观锁乐观锁的区别。Oracle可以做到写不阻塞读仅仅是因为做了多版本并发控制(Multiversion concurrency control), http://en.wikipedia.org/wiki/Multiversion_concurrency_control;

但是在Oracle里面，一样可以做乐观锁和悲观锁的控制。这本质上是应用层面的选择。

观点5：Oracle实际上用的就是乐观锁

分析：这个观点是错的；

前面说了，Oracle的确可以做到写不阻塞读，但是这不是悲观锁和乐观锁的问题。这是因为实现了多版本并发控制。按照wiki的定义，悲观锁和乐观锁是在应用层面选择的。Oracle的应用只要在第二步做了select for update，就是悲观锁的做法；

况且Oracle在任何隔离级别下，除了分布式事务两阶段提交的短暂时间，其他所有情况下都不存在写阻塞读的情况，如果按照这个观点的话那 Oracle已经不能做悲观锁了-_-

观点6：不需要这么麻烦，只需要在d这步，最后提交更新的时候再做一个普通的select检查一下就可以；[就是double check的做法]

分析：这个观点是错的。

这个做法其实在http://www.hetaoblog.com/database-lost-update-pessimistic-lock/，’3. 传统悲观锁做法的变通’这节已经说明了，如果要这么做的话，仍然需要在最后提交更新前double check的时候做一个select for update， 否则select结束到update提交前的时间仍然有可能记录被修改；

观点7：应该尽可能使用悲观锁；

分析：这个观点是错的；

a. 根据悲观锁的概念，用户在读的时候(b这步)就会将记录锁住，直到更新结束的时候才会将锁释放，所以整个锁的过程时间比较长；

b. 另外，悲观锁需要有一个持续的数据库连接，这在当今的web应用中已经几乎不存在；wiki上也说了, 悲观锁‘is not appropriate for use in web application development.’

所以，现在大部分应用都应该是乐观锁的；