Oracle的事務和鎖

一、什麼是事務

必須具備以下四個屬性，簡稱ACID 屬性：
原子性（Atomicity）：事務是一個完整的操作。事務的各步操作是不可分的（原子的）；要麼都執行，要麼都不執行。
一致性（Consistency）：一個查詢的結果必須與資料庫在查詢開始時的狀態保持一致（讀不等待寫，寫不等待讀）。
隔離性（Isolation）：對於其他會話來說，未完成的（也就是未提交的）事務必須不可見。
永續性（Durability）：事務一旦提交完成後，資料庫就不可以丟失這個事務的結果，資料庫透過日誌能夠保持事務的永續性。

二、事務的開始和結束

2.1 事務採用隱性的方式，起始於session的第一條DML語句，

2.2 事務結束於：
1）COMMIT（提交）或ROLLBACK（回滾）
2）DDL語句被執行（提交）
3）DCL語句被執行（提交）
4）使用者退出SQLPLUS（正常退出是提交，非正常退出是回滾）
5）伺服器故障或系統崩潰（回滾）
6）shutdowm immediate(回滾）

在一個事務裡如果某個DML語句失敗，之前其他任何DML語句將保持完好，而且不會提交！

三、Oracle 的事務儲存點功能

savepoint命令允許在事務進行中設定一個標記（儲存點），這個標記可以控制rollback的效果，即在一個事務中回滾掉最近的部分dml語句，保留下儲存點之前的的dml語句，並使事務本身繼續執行(考點）。也就是說回滾到儲存點這個動作並不使事務結束。

SAVEPOINT實驗
savepoint sp1;
delete from emp1 where empno=7900;
savepoint sp2;
update emp1 set ename=’timran’ where empno=7788;
select * from emp1；
rollback to sp2;
select * from emp1；
rollback to sp1;

//rollback to XXX 不會使事務結束。

四、SCN的概念

SCN全稱是System Change Number
它是一個不斷增長的整數，相當於Oracle內部的一個時鐘，只要資料庫一有變更，這個SCN就會+1，Oracle透過SCN記錄資料庫裡事務的一致性。SCN涉及了例項恢復和介質恢復的核心概念，它幾乎無處不在：控制檔案，資料檔案，日誌檔案都有SCN，包括block上也有SCN，

實際上，我們所說的保證同一時間點一致性讀的概念，其背後是物理層面的block讀，Oracle會依據你發出select命令，記錄下那一刻的SCN值，然後以這個SCN值去同所讀的每個block上的SCN比較，如果讀到的塊上的SCN大於select發出時記錄的SCN，則需要利用Undo段，在記憶體中構造CR塊(Consistent Read)。

得到當前SCN有兩個辦法：

SQL> conn / as sysdba
SQL> select current_scn from v$database;

CURRENT_SCN
———–
7222678

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
7222708

五、共享鎖與排他鎖的基本原理：

排他鎖，排斥其他排他鎖和共享鎖。
共享鎖，排斥其他排他鎖，但不排斥其他共享鎖。

因為有事務才有鎖的概念。Oracle資料庫鎖可以分為以下幾大類：

DML鎖（data locks，資料鎖），用於保護資料的完整性。
DDL鎖（dictionary locks，資料字典鎖），用於保護資料庫物件的結構，如表、索引等的結構定義。
SYSTEM鎖（internal locks and latches），保護資料庫的內部結構。

1）當一個使用者對某表做DML操作時，也會加DDL鎖，這樣在事務未結束前，可防止另一個使用者對該表做DDL操作。初始化引數ddl_lock_timeout可以設定了DDL鎖的等待時間。時間過後如果事務仍未結束，則顯示資源正忙。
2）當一個使用者對某表做DDL操作時，也會加DML鎖（EXCLUSIVE 排他鎖），這樣可以防止另一個使用者對該表做DML操作

我們探討的是Oracle的DML鎖(又叫資料鎖），它包括TM和TX兩種

TM是物件導向的鎖，它表示你鎖定了系統中的一個物件，在鎖定期間不允許其他人對這個物件做DDL操作。TM鎖首先產生，目的就是為了實施DDL保護。
TX 是面向事務的鎖，表示發起了一個事務，是否有事務產生，這是根據是否使用UNDO段作為評判標準的。

比如一個update語句，有表級鎖（即TM)和行鎖（即TX鎖）。Oracle是先申請表級鎖TM（其中的RX鎖）, 獲得後系統再自動申請行鎖(TX), 並將實際鎖定的資料行的鎖標誌置位（即指向該TX鎖）。

對於DML操作

行鎖(TX)只有一種
表鎖(TM)共有五種，分別是 RS,RX,S,SRX,X。

六、五種TM表鎖的含義：

ROW SHARE 行共享(RS)，允許其他使用者同時更新其他行，允許其他使用者同時加共享鎖，不允許有獨佔（排他性質）的鎖
ROW EXCLUSIVE 行排他(RX)，允許其他使用者同時更新其他行，只允許其他使用者同時加行共享鎖或者行排他鎖
SHARE 共享(S)，不允許其他使用者同時更新任何行，只允許其他使用者同時加共享鎖或者行共享鎖
SHARE ROW EXCLUSIVE(SRX) 共享行排他，不允許其他使用者同時更新其他行，只允許其他使用者同時加行共享鎖
EXCLUSIVE (X)排他，其他使用者禁止更新任何行，禁止其他使用者同時加任何排他鎖。

sql語句 加鎖模式 許可其他使用者的加鎖模式
———————————————————————- ————————-
select * from table_name 無 RS,RX,S,SRX,X

insert, update, delete(DML操作） RX RS,RX

select * from table_name for update RX RS,RX

———————————————————————- ————————-

lock table table_name in row share mode RS RS,RX,S,SRX

lock table table_name in row exclusive mode RX RS,RX

lock table table_name in share mode S RS,S

lock table table_name in share row exclusive mode SRX RS

lock table table_name in exclusive mode X 無

七、加鎖模式

第一種方式：自動加鎖

做DML操作時，如insert，update，delete，以及select….for update由oracle自動完成加鎖

session1 scott: //用for update加鎖
SQL> select * from dept where deptno>20 for update;

DEPTNO DNAME LOC
———- ————– ————-
30 SALES CHICAGO
40 OPERATIONS BOSTON

session2 sys: //試探，以防被鎖住
SQL>select * from scott.dept for update nowait;
SQL>select * from scott.dept for update wait 5;

session1 scott:
SQL> select * from emp where deptno=30 for update;

session2 sys: 跳過加鎖的記錄，鎖定其他記錄。
SQL> select * from scott.emp for update skip locked;

注意：
1）對整個表for update 是不鎖insert語句的。
2）wait 5：等5秒自動退出。nowait：不等待。skip locked：跳過。都可起到防止自己被掛起的作用。

第二種方式：人工方式加鎖，用lock命令以顯式的方式加鎖。

lock table 表名 in exclusive mode.（一般限於後三種表鎖）

觀察鎖的動態檢視v$lock
觀察鎖的靜態檢視dba_locks

select * from v$lock;

select * from dba_locks where session_id=149;

八、死鎖和解鎖

8.1 Oracle自動偵測死鎖，自動解決鎖爭用。

製作死鎖案例：

scott:

SQL> select * from a;

ID_A
———-
1
2

brain::

SQL> select * from b;

ID_B
———-
100
200

ORA-00060: deadlock detected while waiting for resource

scott: //改自己,不提交

update table a set id=11 where id=1;
brain: //改自己,不提交
update table b set id=1100 where id=100;
scott: //改對方,被鎖住
update table brain.b id=1000 where id=100;
brain: //改對方,造成死鎖
update table brain.b id=1000 where id=100;

8.2 管理員如何解鎖

可以根據以下方法準確定位要kill session的sid號和serial#號，

SQL> select * from v$lock where type in (‘TX’,’TM’);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- —- ———- ———- ———- ———- ———- ———-
38B66D60 38B66D8C 127 TX 327680 1042 0 6 2985 0
00567BAC 00567BDC 134 TM 71090 0 3 0 2996 0
00567BAC 00567BDC 127 TM 71090 0 3 0 2985 0
37960894 379608D4 134 TX 327680 1042 6 0 2996 1

SQL> select a.sid,a.serial#,b.sql_text from v$session a,v$sql b where a.prev_sql_id=b.sql_id and a.sid=127;

SID SERIAL# SQL_TEXT
———- ———- ——————————————————————————–
127 2449 update emp1 set sal=8000 where empno=7788

SQL> select sid,serial#,blocking_session,username,event from v$session where blocking_session_status=’VALID';

SID SERIAL# BLOCKING_SESSION USERNAME EVENT
———- ———- —————- —————————— —————————————-
127 2449 134 SCOTT enq: TX – row lock contention

也可以根據v$lock檢視的block 和request確定session阻塞關係,確定無誤後再殺掉這個session

SQL>ALTER SYSTEM KILL SESSION ‘127,2449’;　

更詳細的資訊，可以從多個檢視得出，相關的檢視有：v$session,v$process,v$sql,v$locked,v$sqlarea等等

阻塞(排隊）從OEM裡看的更清楚 OEM–>Performance–>Additional Monitoring Links–>Blocking Sessions(或Instance Locks)