程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫

lock

編輯:SyBase教程

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved