數(shù)據(jù)庫(kù)事務(wù)是oracle非?；A(chǔ)又極為重要的概念。之前已經(jīng)介紹過相關(guān)的一些概念，相關(guān)文章見下：
oracle產(chǎn)生事務(wù)transaction幾種方式或方法
oracle事務(wù)隔離級(jí)別transaction isolation level初識(shí)
產(chǎn)生數(shù)據(jù)庫(kù)事務(wù)時(shí)，必然會(huì)在數(shù)據(jù)庫(kù)事務(wù)運(yùn)行期間產(chǎn)生各種各樣的鎖。與鎖相關(guān)的動(dòng)態(tài)性能視圖為v$lock,里面有個(gè)列l(wèi)mode，即持鎖模式或叫鎖模式，其具體含義及取值
鎖模式lmode可以有7種不同的取值,每個(gè)值到底是什么意思，具體含義見下
創(chuàng)建測(cè)試表并插入記錄
SQL> create table t_lockmode(a int,b int); Table created. SQL> insert into t_lockmode select 1,1 from dual; 1 row created. SQL> commit; Commit complete.
這種鎖模式 允許 多個(gè)會(huì)話并發(fā)訪問被鎖定的表，但是不允許 其它會(huì)話以 exclusive排它模式鎖定整個(gè)表 這種鎖模式也是鎖模式 share update的同義詞 這種鎖模式仍然存在是為了兼容 oracle舊版本 --未加鎖前的測(cè)試會(huì)話的持鎖信息 （可見數(shù)據(jù)庫(kù)一直會(huì)持有各種鎖，下述的鎖是系統(tǒng)鎖，而非用戶鎖） SQL> select addr,sid,type,lmode,request,block from v$lock where sid=73; ADDR SID TY LMODE REQUEST BLOCK ---------------- ---------- -- ---------- ---------- ---------- 000000008D2498B8 73 AE 4 0 0 000000008D249AE8 73 TO 3 0 0 --測(cè)試會(huì)話加 row share鎖模式 SQL> lock table t_lockmode in row share mode; Table(s) Locked. --加鎖模式 row share后的持鎖信息 SQL> / ADDR SID TY LMODE REQUEST BLOCK ---------------- ---------- -- ---------- ---------- ---------- 000000008D2498B8 73 AE 4 0 0 000000008D249AE8 73 TO 3 0 0 00007FE54C209CD8 73 TM 2 0 0 --lmode=2 ---其它會(huì)話可以row share鎖模式并發(fā)訪問表 SQL> select sid from v$mystat where rownum=1; SID ---------- 28 SQL> lock table t_lockmode in row share mode; Table(s) Locked. SQL> rollback; Rollback complete. --其它會(huì)話可以 row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row exclusive mode; Table(s) Locked. SQL> rollback; Rollback complete. ---其它會(huì)話可以share鎖模式并發(fā)訪問表 SQL> lock table t_lockmode in share mode; Table(s) Locked. SQL> rollback; Rollback complete. ----其它會(huì)話可以 share row exclusive鎖模式并發(fā)訪問表 SQL> lock table t_lockmode in share row exclusive mode; Table(s) Locked. SQL> rollback; Rollback complete. ----其它會(huì)話不能以 exclusive鎖模式并發(fā)訪問表 --卡住 SQL> lock table t_lockmode in exclusive mode; SQL> / ADDR SID TY LMODE REQUEST BLOCK ---------------- ---------- -- ---------- ---------- ---------- 000000008D2498B8 73 AE 4 0 0 000000008D249AE8 73 TO 3 0 0 00007FE54C2042E0 73 TM 2 0 1
這種鎖模式 同于row share,但是不允許其它會(huì)話以 share鎖模式訪問 這種鎖模式 在執(zhí)行DML操作（update,insert,delete）會(huì)自動(dòng)獲取這種鎖模式 測(cè)試會(huì)話以row exclusive鎖模式持有表 SQL> lock table t_lockmode in row exclusive mode; Table(s) Locked. SQL> select addr,sid,type,lmode,request,block from v$lock where sid=73; ADDR SID TY LMODE REQUEST BLOCK ---------------- ---------- -- ---------- ---------- ---------- 000000008D2498B8 73 AE 4 0 0 000000008D249AE8 73 TO 3 0 0 00007FE54C2042E0 73 TM 3 0 0 --lmode=3 --其它會(huì)話可以row share鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row share mode; Table(s) Locked. SQL> rollback; Rollback complete. --其它會(huì)話可以 row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row exclusive mode; Table(s) Locked. SQL> rollback; Rollback complete. --其它會(huì)話 不能以share鎖模式 并發(fā)訪問表 --卡住 SQL> lock table t_lockmode in share mode; ^Clock table t_lockmode in share mode * ERROR at line 1: ORA-01013: user requested cancel of current operation --其它會(huì)話 不能以share row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in share row exclusive mode; ^Clock table t_lockmode in share row exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation --其它會(huì)話 不能以exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in exclusive mode; ^Clock table t_lockmode in exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation
這種鎖模式 允許 多個(gè)會(huì)話并發(fā)查詢，但是不允許 對(duì)于鎖定表的update操作 測(cè)試會(huì)話以share鎖模式持有表 SQL> lock table t_lockmode in share mode; Table(s) Locked. SQL> select addr,sid,type,lmode,request,block from v$lock where sid=73; ADDR SID TY LMODE REQUEST BLOCK ---------------- ---------- -- ---------- ---------- ---------- 000000008D2498B8 73 AE 4 0 0 000000008D249AE8 73 TO 3 0 0 00007FE54C209CD8 73 TM 4 0 0 --lmode=4 --其它會(huì)話可以row share鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row share mode; Table(s) Locked. SQL> rollback; Rollback complete. --其它會(huì)話不能以 row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row exclusive mode; ^Clock table t_lockmode in row exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation --其它會(huì)話可以 share鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in share mode; Table(s) Locked. SQL> rollback; Rollback complete. ---其它會(huì)話 不允許以 share row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in share row exclusive mode; ^Clock table t_lockmode in share row exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation ---其它會(huì)話 不允許以 exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in exclusive mode; ^C lock table t_lockmode in exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation
這種鎖模式 用于查看整個(gè)表，允許 其它會(huì)話查看表的數(shù)據(jù)，但是不允許其它會(huì)話 以share鎖模式獲取表 ，也不允許 其它會(huì)話update被鎖定表 這種鎖模式 允許 對(duì)于鎖定表的查詢，但不允許 對(duì)于鎖定表的其它任何操作 測(cè)試會(huì)話以 share row exclusive鎖模式持有表 SQL> lock table t_lockmode in share row exclusive mode; Table(s) Locked. SQL> / ADDR SID TY LMODE REQUEST BLOCK ---------------- ---------- -- ---------- ---------- ---------- 000000008D2498B8 73 AE 4 0 0 000000008D249AE8 73 TO 3 0 0 00007FE54C209CD8 73 TM 5 0 0 --其它會(huì)話 允許 以row share鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row share mode; Table(s) Locked. SQL> rollback; Rollback complete. --其它會(huì)話 不允許 以row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row exclusive mode; ^Clock table t_lockmode in row exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation ---其它會(huì)話 不允許 以share鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in share mode; ^Clock table t_lockmode in share mode * ERROR at line 1: ORA-01013: user requested cancel of current operation ---其它會(huì)話 不允許 以share row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in share row exclusive mode; ^Clock table t_lockmode in share row exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation ---其它會(huì)話 不允許以 exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in exclusive mode; ^Clock table t_lockmode in exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation
這種鎖模式 允許 對(duì)于鎖定表的查詢，但不允許 對(duì)于鎖定表的其它任何操作 --測(cè)試會(huì)話以 exclusive鎖模式持有表 SQL> lock table t_lockmode in exclusive mode; Table(s) Locked. SQL> / ADDR SID TY LMODE REQUEST BLOCK ---------------- ---------- -- ---------- ---------- ---------- 000000008D2498B8 73 AE 4 0 0 000000008D249AE8 73 TO 3 0 0 00007FE54C2042E0 73 TM 6 0 0 --lmode=6 --其它會(huì)話 不允許以 row share鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row share mode; ^Clock table t_lockmode in row share mode * ERROR at line 1: ORA-01013: user requested cancel of current operation ---其它會(huì)話不允許 以row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in row exclusive mode; ^Clock table t_lockmode in row exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation ---其它會(huì)話不允許以 share鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in share mode; ^Clock table t_lockmode in share mode * ERROR at line 1: ORA-01013: user requested cancel of current operation ---其它會(huì)話不允許 以share row exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in share row exclusive mode; ^Clock table t_lockmode in share row exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation --其它會(huì)話不允許 以exclusive鎖模式 并發(fā)訪問表 SQL> lock table t_lockmode in exclusive mode; ^Clock table t_lockmode in exclusive mode * ERROR at line 1: ORA-01013: user requested cancel of current operation
exclusive鎖模式最牛逼，它是唯我獨(dú)尊，獨(dú)對(duì)排它訪問，它一占用表鎖資源，其它會(huì)話只能等待
row share(share update)鎖模式相對(duì)而言最溫和，它基本和所有的鎖模式可以并存，只是不允許exclusive鎖模式
share row exclusive鎖模式雖然沒有exclusive鎖模式這么牛逼，它可以排第二種嚴(yán)厲鎖模式，它只能兼容row share(share update)鎖模式
row exclusive及share鎖模式排位在share row exclusive之后，它可以兼容3種鎖模式，不兼容余下2種鎖模式
（收費(fèi)20元）
重慶分公司
重慶分公司
