lock，lock是什麼意思
racle中鎖的概念十分重要，對於其他關係型資料庫更不用說，有的資料庫發出select語句都要加鎖，但是oracle有獨立的undo（可從undo裡讀取修改過的資料）所以不加鎖。
下面介紹oracle裡的幾種鎖模式：
| 鎖代碼 |
鎖模式名稱 |
鎖模式縮寫 |
鎖模式別名 |
鎖層級 |
| 0 |
none |
none |
none |
|
| 1 |
null |
null |
null |
表級鎖 |
| 2 |
ROW-S |
SS |
RS |
表級鎖 |
| 3 |
ROW-X |
SX |
RX |
表級鎖 |
| 4 |
Share |
S |
S |
表級鎖 |
| 5 |
S/ROW-X |
SSX |
SRX |
表級鎖 |
| 6 |
Exclusive |
X |
X |
表/行級鎖 |
鎖代碼說明：
0：none
1：null 空
2：Row-S 行共用(RS)：共用表鎖 (row share)
3：Row-X 行專用(RX)：用於行的修改 (row exclusive)
4：Share 共用鎖定(S)：阻止其他DML操作(share)
5：S/Row-X 共用行專用(SRX)：阻止其他事務操作 (share row exclusive)
6：exclusive 專用(X)：排它鎖，最進階鎖，獨立訪問使用 (exclusive)
（數字越大鎖層級越高, 影響的操作越多)
TX表示的是行級鎖，TM表示的是表級鎖。
下面是關於summary of table locks:
| Sql Statement |
Mode of table lock |
RS |
RX |
S |
SRX |
X |
| select * from table .... |
none |
Y |
Y |
Y |
Y |
Y |
| insert into table .... |
RX |
Y |
Y |
N |
N |
N |
| update table .... |
RX |
Y* |
Y* |
N |
N |
N |
| delete from table .... |
RX |
Y* |
Y* |
N |
N |
N |
| select ... from table for update of |
RS |
Y* |
Y* |
Y* |
Y* |
N |
| lock table table in row share mode |
RS |
Y |
Y |
Y |
Y |
N |
| lock table table in row execlusive |
RX |
Y |
Y |
N |
N |
N |
| lock table table in share mode |
S |
Y |
N |
Y |
N |
N |
| lock table table in share row execlusive mode |
SRX |
Y |
N |
N |
N |
N |
| lock table table in execlusive mode |
X |
N |
N |
N |
N |
N |
Y(YES) N(NO)
Y*:if no conficting row locks are held by another transaction.otherwise,waits occur.(如果沒有衝突行鎖是被另一個事務持有。否則,等待出現)
對於鎖的概念理解很重要，尤其在寫應用程式的時候。
-bash-3.2$ lsb_release -a
Description: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> show user;
USER 為 "HR"
SQL> create table t(id number,name varchar2(10));
表已建立。
SQL> insert into t values(1,'diy');
已建立 1 行。
SQL> insert into t values(2,'os');
已建立 1 行。
SQL> COMMIT;
提交完成。
block（阻塞）：
SQL> update t set name='d' where id=1;
已更新 1 行。
SQL> select distinct sid from v$mystat;
SID
----------
21
在另一個session裡：
SQL> select distinct sid from v$mystat;
SID
----------
19
SQL> update t set name='o' where id=1;
此時會一直等待，知道第一個事務結束（commit或rollback),這是block，不是死結！
我們分析這個過程：
SQL> show user;
USER 為 "SYS"
SQL> select * from v$lock where sid in(19,21) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
38687638 38687664 19 AE 100 0 4 0 810 0
38687ED4 38687F00 19 TX 262175 1888 0 6 230 0
004CA6B0 004CA6E0 19 TM 76714 0 3 0 230 0
004CA6B0 004CA6E0 21 TM 76714 0 3 0 240 0
37FBACC0 37FBAD00 21 TX 262175 1888 6 0 240 1（鎖定了一個事務）
38687720 3868774C 21 AE 100 0 4 0 3730 0
已選擇6行。
注意AE：Edition Lock，是11g新增加的鎖類型，這是一個會話鎖，只要有會話就會有一個鎖。此時session號為21的先更新id=1這一行，獲得了一個TM（RX)鎖，又獲得了TX(X)鎖；
session號為22的也獲得了一個TM鎖(RX)，但是和上面的TM相容，所以此時沒有阻塞，
但是由於行鎖並不和上面的行鎖相容，所以沒有獲得行鎖X,從上面的LMODE可以看出。
我們可以通過下面兩個視圖分析資料庫中被鎖的對象：
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
0 0 0 76714 19 HR oracle 6130 3
4 31 1888 76714 21 HR oracle 5022 3
SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID=76714;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- -------------- -------------- ------------------- ------- - - - ---------- ------------------------------
HR T 76714 76714 TABLE 22-4月 -15 22-4月 -15 2015-04-22:12:39:06 VALID N N N 1
我們在重建索引時，為了不影響系統效能，往往：
alter index index_name rebuild online;
但是我們為什麼不：alter index index_name rebuild
下面簡單操作示範：
SQL> create table ttt as select * from dba_objects;
表已建立。
SQL> select count(*) from dba_objects;
COUNT(*)
----------
72746
SQL> create index index_id on TTT(OBJECT_ID);
索引已建立。
SQL> set autotrace traceonly;
SQL> SELECT * FROM TTT;
已選擇72746行。
執行計畫
----------------------------------------------------------
Plan hash value: 774701505
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51569 | 10M| 283 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TTT | 51569 | 10M| 283 (1)| 00:00:04 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
308 recursive calls
0 db block gets
5909 consistent gets
1035 physical reads
0 redo size
8067725 bytes sent via SQL*Net to client
53755 bytes received via SQL*Net from client
4851 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed
SQL> alter index index_id rebuild;
索引已更改。
SQL> SELECT * FROM TTT;
已選擇72746行。
執行計畫
----------------------------------------------------------
Plan hash value: 774701505
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51569 | 10M| 283 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TTT | 51569 | 10M| 283 (1)| 00:00:04 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
36 recursive calls
0 db block gets
5886 consistent gets
0 physical reads
0 redo size
8067725 bytes sent via SQL*Net to client
53755 bytes received via SQL*Net from client
4851 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed
SQL> alter index index_id rebuild online;
索引已更改。
SQL> SELECT * FROM TTT;
已選擇72746行。
執行計畫
----------------------------------------------------------
Plan hash value: 774701505
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51569 | 10M| 283 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TTT | 51569 | 10M| 283 (1)| 00:00:04 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
5 recursive calls
0 db block gets
5879 consistent gets
0 physical reads
0 redo size
8067725 bytes sent via SQL*Net to client
53755 bytes received via SQL*Net from client
4851 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed
兩種掃描方式都是全表掃描，都會發生排序（sort操作）但是rebulid online操作比rebulid效能更好，從邏輯讀次數可知。
rebulid操作會阻塞dml操作，而online操作不會（online操作降低了鎖層級）
deadlock:
SQL>show user;
USER 為 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
21
SQL> update t set name='d' where id=1;
已更新 1 行。
SQL> SHOW USER;
USER 為 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
19
SQL> update t set name='s' where id=2;
已更新 1 行。
SQL> SHOW USER;
USER 為 "HR"
SQL> select distinct sid from v$mystat;
SID
----------
21
SQL> update t set name='y' where id=2;
update t set name='y' where id=2
*
第 1 行出現錯誤:
ORA-00060: 等待資源時檢測到死結
SQL> show user;
USER 為 "HR"
SQL> select distinct sid from v$mystat;
SID
----------
19
SQL> update t set name='s' where id=1;
上述順序按操作順序排列。
警示日誌裡（alert)有警告：
Wed Apr 22 14:43:05 2015
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_5024.trc.
查看轉儲檔案：
.......
.......
*** 2015-04-22 14:43:04.053
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00030014-000009b7 24 21 X 32 19 X
TX-0006000d-00000a27 32 19 X 24 21 X
session 21: DID 0001-0018-00000019 session 19: DID 0001-0020-00000014
session 19: DID 0001-0020-00000014 session 21: DID 0001-0018-00000019
Rows waited on:
Session 21: obj - rowid = 00012BAA - AAASuqAAEAAABuvAAB
.........
.........
上面的內容是不是太詳細了！哪個session，rowid都告訴我們了！可以更加深入研究死結。
