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都告訴我們了!可以更加深入研究死鎖。