当前位置: 代码迷 >> Oracle开发 >> 一个关于丢失更新的有关问题
  详细解决方案

一个关于丢失更新的有关问题

热度:92   发布时间:2016-04-24 07:42:31.0
请教大家一个关于丢失更新的问题。
1.请教大家一个关于丢失更新的问题。我们在做应用开发时,如果要更新某个字段,在写sql时往往是更新了所有的字段。问题就在此时出现了,一个会话user1查询了a记录,并修改了字段addr,几乎同时,会话user2也查询了a记录,它修改了字段birth. 第二天,user1发现a记录的addr又还原了,如何防止这种问题出现呢?

for example :
  如果user1和user2在同一时间查询a记录,user1采用了悲观锁,修改了addr,此时user2没有动作,此时看到的仍然是addr没修改前的a,过了几分钟后,user2才修改了birth. 一小时后,user1发现addr又还原了。(我的修改场景是做应用时,我们选择了a记录,然后展现a记录的所有属性,并能修改除主键外的所有属性)

   





------解决方案--------------------
做一 个事务控制
------解决方案--------------------
java代码大概是这样的!
javax.sql.connect.setAutoCommit(false);

conn.execute(sql);

conn.commit;

Exception 

rollback;
------解决方案--------------------
最好在更新前重新检索,或者更新的时候只更新本字段而不是整行更新
------解决方案--------------------
select for update
------解决方案--------------------
奇怪,悲观锁不就是解决这种问题的吗?

网上找的一个小例子
Oracle Internals page 57

Consider an airline seat reservation application. Two different customers may
simultaneously ask two different operators whether a seat is available on a
particular flight. What should the application do?
The application can use SELECT FOR UPDATE NOWAIT to retrieve the
information. This guarantees that if a seat appears to be available, then it has
already been locked, and a booking for that seat will be able to be successfully
taken. This is called early locking, or pessimistic locking.
The alternative is to defer the taking of a lock until the customer resolves to make
a booking. This is called late locking, or optimistic locking.
The choice of either pessimistic or optimistic locking affects the design of both
business and application processes. So careful thought is needed. Pessimistic
locking should be avoided where possible, despite being slightly easier to
implement, because it increases the risk of blocking locks.
------解决方案--------------------
select ... ... for update
  相关解决方案