鎖的基本原理，基本原理

鎖的基本原理，基本原理
oracle032鎖的基本原理
1、Oracle鎖類型
     鎖的作用：保護資料，沒有鎖就沒有並發，鎖是用來限制並發的
     資料庫反應慢不一定是資料負嚴重，也可能是鎖的原因阻止了事務的進行：開兩個用戶端修改資料會明顯發現第二個會很慢就是因為第一個使用者鎖住了行記錄
    
     latch鎖：chain，鏈（server Pro 並發訪問時就通過擷取latch鎖來進行保護鏈）
     LOCK鎖：buffer 、資料區塊、資料行
          獨佔鎖定（X）：就是A訪問加鎖X而B就不能訪問加鎖了
          共用鎖定（S）：就是A、B都可以加相同的共用S鎖
2、行級鎖：DML語句；是LOCK鎖的最小粒度鎖；oracle特有也最影響並發的效率的
     事務鎖TX：一個事務一個事務鎖，是因為行級鎖產生的
          鎖的結構
          事務鎖的加鎖和解鎖過程：DML語句（加鎖）；commit/rollback（解鎖）
     只有獨佔鎖定（行級鎖和事務鎖都是獨佔鎖定的類型）
          不影響讀（CR塊）
3、表級鎖：TM

     行級獨佔鎖定（Row exclusive）RX鎖
          當我們進行DML時，會自動在被更新的表上添加RX鎖，可以執行LOCK命令顯式的在表上添加RX鎖
          允許其他事務通過DML語句修改相同表裡的其他資料行
          允許使用lock命令對錶添加RX鎖定
          不允許其他事務對錶添加X鎖
     行級共用鎖定（Row Shared，簡稱RS鎖）
          select … from for update     是會產生一個事務，和普通DML語句一樣產生行級鎖，並在表層級加了一個RS鎖沒加RX鎖；但實際並沒有修改資料    
     共用鎖定（Share，簡稱S鎖）
          通過lock table in share mode命令添加該S鎖
     獨佔鎖定（Exclusive，簡稱X鎖）：對錶的結構進行改變，和表的刪除；其他使用者不能進行任何操作
          通過lock table in exclusive mode命令添加X鎖
     共用行級獨佔鎖定（Share Row Exclusive，簡稱SRX鎖）
          通過lock table in share row exclusive mode命令添加SRX鎖

     lock table  in [row share][row exclusive][share][share row exclusive][exclusive] mode;

研究鎖側重於兩點：鎖的相容性，鎖的產生原因
4、鎖的相容性
5、加鎖語句以及鎖的釋放
6、鎖相關視圖
     v$transaction
          XIDUSN表示當前事務使用的復原段的編號
          XIDSLOT說明該事務在復原段頭部的事務表中對應的記錄編號（也可以叫做槽號）
          XIDSQN說明序號
          STATUS說明該事務是否為活動的
     v$lock
          記錄了session已經獲得的鎖定以及正在請求的鎖定的資訊
          SID說明session的ID號
          TYPE說明鎖定鎖定層級，主要關注TX和TM
          LMODE說明已經獲得的鎖定的模式，以數字編碼錶示
          REQUEST說明正在請求的鎖定的模式，以數字編碼錶示
          BLOCK說明是否阻止了其他使用者獲得鎖定，大於0說明是，等於0說明否

     鎖定模式          鎖定簡稱     編碼數值
     Row Exclusive          RX           3
     Row Shared           RS          2
     Share                S           4
     Exclusive           X           6
     Share Row Exclusive      SRX           5
     NULL                 N/A           0或者1

     v$enqueue_lock
     該視圖中包含的欄位以及欄位含義與v$lock中的欄位一模一樣。
     只不過該視圖中只顯示那些申請鎖定，但是無法獲得鎖定的session資訊。
     其中的記錄按照申請鎖定的時間先後順序排列，先申請鎖定的session排在前面，排在前面的session將會先獲得鎖定。

     v$locked_object
     記錄了當前已經被鎖定的對象的資訊
     XIDUSN表示當前事務使用的復原段的編號
     XIDSLOT說明該事務在復原段頭部的事務表中對應的記錄編號
     XIDSQN說明序號
     OBJECT_ID說明當前被鎖定的對象的ID號，可以根據該ID號到dba_objects裡尋找被鎖定的對象名稱
     LOCKED_MODE說明鎖定模式的數字編碼

     v$session
     記錄了當前session的相關資訊
     SID表示session的編號
     SERIAL#表示序號
     SID和SERIAL#可以認為是v$session的主鍵，它們共同唯一標識一個session測試鎖：

開啟3個session： SQL>  select sid from v$mystat where rownum=1;        SID ----------         29 SQL> SQL>  select sid from v$mystat where rownum=1;        SID ----------         35 SQL> SQL>  select sid from v$mystat where rownum=1;        SID ----------         38 SQL> 使用sessionID為29進行更新t表操作 SQL>  update t set name='qq' where id = 2; 1 row updated. SQL> 查詢事務： select xidusn,xidslot,xidsqn,status from v$transaction;                   5     30     1099     ACTIVE 說明當前存在一個活動的事務 下面是查詢sessionID為29的串連所產生的事務資訊：  select sid,type,id1,id2,      decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,   decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')      request_mode,block      from v$lock      where sid=29; SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK 29     AE     100     0     Share     None     0 29     TM     74585     0     Row Exclusive     None     0//表級鎖 29     TX     327710     1099     Exclusive     None     0//行級鎖 SID表示sessionID，TYPE表示鎖的類型，ID2表示事務槽被覆蓋的次數，ID1中的資訊包含兩種資訊：1.事務表  2.事務表中行 通過：      將ID1拆解      select trunc(327710/power(2,16)) as undo_blk#,bitand(327710,to_number('ffff','xxxx')) + 0 as slot#     from dual;UNDO_BLK#, SLOT#      5     30//顯示的是該事務的資訊，這也說明了ID1表示了兩種資訊的說法。LOCK_MODE表示鎖的模式，REQUEST_MODE是否請求鎖，BLOCK表示鎖住哪個事務select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS,NAMESPACE from dba_objects where object_id=74585;//被鎖的對象 OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,    STATUS, NAMESPACE  SYS     T            74585     TABLE     22-12月-14     04-1月 -15     VALID     1     對於TM鎖來說，ID1表示被鎖定的對象的對象ID，ID2始終為0      對於TX鎖來說，ID1表示事務使用的復原段編號以及在事務表中對應的記錄編號，ID2表示該記錄編號被重用的次數（wrap） 使用sessionID為35進行更新t表操作：SQL> update t set name='ww' where id = 2; //沒有執行結果說明被鎖住了下面是查詢sessionID為29、35的串連所產生的事務資訊：     select sid,type,id1,id2, decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block from v$lock where sid in(29,35) order by sid; SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK29     TM     74585     0     Row Exclusive     None     0 29     AE     100     0     Share     None     0 29     TX     327710     1099     Exclusive     None     1//鎖住了一個事務 35     TM     74585     0     Row Exclusive     None     0 35     AE     100     0     Share     None     0 35     TX     327710     1099     None     Exclusive     0//請求一個獨佔鎖定，因為上面的29鎖住了 使用sessionID為38進行更新t表操作：SQL> update t set name='ww' where id = 2; //沒有執行結果說明被鎖住了 查詢v$enqueue_lock來獲得鎖定隊列中的session資訊：      select sid,type,      decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')      request_mode      from v$enqueue_lock      where sid in(35,38); SID, TYPE, REQUEST_MODE35     AE     None 38     AE     None 35     TX     Exclusive 38     TX     Exclusive可以知道sessionID為35,38的串連需要鎖為X鎖 查詢幾個鎖之間的關係：select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type, decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, b.ctime as time_held,c.sid as waiter_sid, decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode, c.ctime time_waited from   v$lock b, v$enqueue_lock c, v$session a where  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1 order by time_held, time_waited; BLOCKER_SID, SERIAL#, BLOCKER_USERNAME, TYPE, LOCK_MODE, TIME_HELD, WAITER_SID, REQUEST_MODE, TIME_WAITED        29     557               SYS     TX     Exclusive     2452     38          Exclusive     274         29     557               SYS     TX     Exclusive     2452     35          Exclusive     712BLOCKER_SID：鎖的sessionID為29，BLOCKER_USERNAME：鎖的使用者名稱是sys，TYPE：鎖的類型為TX（行級鎖）LOCK_MODE：鎖的模型為獨佔鎖定，TIME_HELD：鎖所持續的時間WAITER_SID：該鎖被哪個sessionID鎖等待，REQUEST_MODE：請求鎖的類型，TIME_WAITED：請求時間通過上面的資訊就可以知道某個串連所持有的鎖時間，假如時間過長說明該串連存在問題，就可以殺掉該串連alter system kill session '29,557';//第一個參數BLOCKER_SID，第二個為SERIAL#參數 一個事務修改多行，產生一個TX鎖 select sid from v$mystat where rownum=1; SQL> update t set name='rr' where id=2; 1 row updated. SQL> update t set name='rr' where id=2; 1 row updated. SQL> update t set name='rr' where id=2; 1 row updated. SQL> select sid,type,id1,id2, decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode, decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,block from v$lock where sid=38; SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK38     AE     100     0     Share     None     0 38     TM     74585     0     Row Exclusive     None     0 38     TX     327680     1099     Exclusive     None     0 可以獲得的TX鎖定的總個數由初始化參數transactions決定，而可以獲得的TM鎖定的個數則由初始化參數dml_locks決定： select name,value from v$parameter where name in('transactions','dml_locks');        NAME, VALUEdml_locks     1084 transactions     271 查詢資源的使用方式： select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U" from v$resource_limit where resource_name in('transactions','dml_locks');         R_N,     C_U,  M_U,        I_U   dml_locks     1     1           1084 transactions     1     1            271通過上面的參數可以知道dml_locks資源當前使用了1個而且最多的時候也只使用了1個，最大的資源數為1084transactions同上，通過這個可以判斷資源設定是否合理，假如M_U的值等於或者接近於I_U時說明需要增大I_U值因為：可以獲得的TX鎖定的總個數由初始化參數transactions決定，而可以獲得的TM鎖定的個數則由初始化參數dml_locks決定

     grant select on v_$mystat to hr;

死結
兩個session（以A和C來表示），如果A持有C正在申請的鎖定，同時C也持有A正在申請的鎖定時，這時發生死結現象。死結是典型的“雙輸”情況，如果任其發展，則會出現A和C這兩個session正在執行的事務都無法結束的現象。因此，在Oracle資料庫中，造成死結的那個DML語句會被撤銷。死結總是由於應用程式設計不合理引起的。
當某個session的事務引起了死結時，Oracle會自動將阻塞該事務的其他事務中相應的DML語句撤銷，而阻塞該事務的其他事務中的其他DML語句並沒有撤銷。

session 1
select sid from v$mystat where rownum=1;
update employees set last_name=last_name||'a'
where employee_id=100

session 2
select sid from v$mystat where rownum=1;
update employees set last_name=last_name||'b'
where employee_id=101;

session 1
update employees set last_name=last_name||'c' where employee_id=101;

session 2
update employees set last_name=last_name||'d' where employee_id=100;

對HR使用者進行解鎖和密碼設定： SQL>  select username,password,account_status from dba_users where username='HR'; USERNAME                       PASSWORD ------------------------------ ------------------------------ ACCOUNT_STATUS -------------------------------- HR EXPIRED & LOCKED//到期且被鎖住的狀態 SQL> alter user hr account unlock; User altered. SQL>  select username,password,account_status from dba_users where username='HR'; USERNAME                       PASSWORD ------------------------------ ------------------------------ ACCOUNT_STATUS -------------------------------- HR EXPIRED//此時hr只是到期並沒有被鎖住 SQL> alter user hr identified hr; alter user hr identified hr                          * ERROR at line 1: ORA-00924: missing BY keyword SQL>  alter user hr identified by hr; User altered. SQL>  select username,password,account_status from dba_users where username='HR'; USERNAME                       PASSWORD ------------------------------ ------------------------------ ACCOUNT_STATUS -------------------------------- HR OPEN//開啟狀態 SQL>