pessimistic鎖定對optimistic鎖定(1)
2024-07-21 02:34: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
