pessimistic锁定对optimistic锁定(1)_Oracle教程

    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