您的位置: 天津信息网 > 健康

pessimistic锁定对optimistic锁定

发布时间:2019-09-13 19:20:19

pessimistic (悲观)locking和optimistic (乐观)locking是oracle保证数据并行访问和避免lost update的2种策略.

pessimistic (悲观)locking是指用户在读取和更新数据的时候,悲观的认为这期间其他用户可能会更新同一记录;因此在更新数据前读取数据的时候,尝试获得该记录的绝对锁,如果其他用户已经锁住该纪录,则被阻塞用户可以选择等待(select for upodate)还是取消等待(select for update nowait)。从而避免lost update,保证了数据一致性。

optimistic (乐观)locking是指用户在读取和更新数据的时候,乐观的认为这期间其他用户不可能更新同一记录,在更新数据前读取数据的时候,不会获得该记录的绝对锁;因此在更新纪录的时候,其他用户很可能已经修改过了该纪录;因此,在更新的时候需要采取自定义编码策略来避免lost update,保证数据一致性。

演示lost update的例子

At year end, Manager Ian decide to increase engineer SHERSA’s salary by 500$;while HR officer need to increase each engineer’s salary by 5%.

Time1: Ian read SHERSA’s salary record:

SQL> select * from emp where name=' SHERSA ';

ID NAME SALARY

---------- ------------------------- ----------

8 SHERSA 3000

Time2: HR read SHERSA’s salary record:

SQL> select * from emp where name='SHERSA';

ID NAME SALARY

---------- ------------------------- ----------

8 SHERSA 3000

Time3: HR update SHERSA’s salary.

SQL> update emp set salary=salary*1.05 where id=8;

SQL> commit;

SQL> select * from emp where name='SHERSA';

ID NAME SALARY

---------- ------------------------- ----------

8 SHERSA 3150

Time4: Ian update SHERSA’s salary by increment 500

SQL> update emp set salary=3500 where id=8;

SQL> commit;

SQL> select * from emp where name='SHERSA';

ID NAME SALARY

---------- ------------------------- ----------

8 SHERSA 3500

Ian更新的数据覆盖了HR更新的纪录,导致丢失更新(lost update);SHERSA受到了经济损失。

为了避免lost update,需要pessimistic locking 或者optimistic locking。

pessimistic locking 策略

通过在读纪录时,采用select for update锁住该纪录,再进行更新。

Time1: Ian read SHERSA’s salary record:

SQL> select * from emp where name=' SHERSA ' for update;

ID NAME SALARY

---------- ------------------------- ----------

8 SHERSA 3000

Time2: HR read SHERSA’s salary record:

SQL> select * from emp where name= ' SHERSA ' for update;

无法获得行上的绝对锁,被另外的session Ian阻塞

Time3: HR has to wait …. Or cancel operation by using “for update nowait”

SQL> select * from emp where name='SHERSA' for update nowait;

select * from emp where name='SHERSA' for update nowait

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Get “resource busy”错误

Time4: Ian update SHERSA’s salary by increment 500

SQL> update emp set salary=3500 where id=8;

SQL> commit;

SQL> select * from emp where name='SHERSA';

ID NAME SALARY

---------- ------------------------- ----------

8 SHERSA 3500

Time5: HR update SHERSA’s salary

SQL> update emp set salary=salary*1.05 where id=8;

SQL> commit;

SQL> select * from emp where id=8;

ID NAME SALARY

---------- ------------------------- ----------

8 SHERSA 3675

查看本文来源

丁桂薏芽健脾凝胶能治腹泻吗
小孩子老是不爱吃饭怎么办
晚上多夜尿吃什么
儿童反复发烧是怎么回事
猜你会喜欢的
猜你会喜欢的