一、Oracel 的鎖介紹

oracle造成鎖表的情況：

1.1、查看鎖的對象視圖：

select object_id,session_id,locked_mode from v$locked_object;

1.2、鎖的級別

0：none
1：null 空
2：Row-S 行共享(RS)：共享表鎖
3：Row-X 行專用(RX)：用于行的修改
4：Share 共享鎖(S)：阻止其他DML操作
5：S/Row-X 共享行專用(SRX)：阻止其他事務(wù)操作
6：exclusive 專用(X)：獨立訪問使用 。

1.3 常用的sql會造成鎖表

1、select … from … for update; 3 級鎖
2、insert / update / delete .（DML）… ; 3級鎖
3、創(chuàng)建索引的時候也會產(chǎn)生3,4級別的鎖
4、主外鍵約束時 update / delete … ; 可能會產(chǎn)生4,5的鎖
5、DDL（alter,drop）語句可能會產(chǎn)生時是6的鎖

在鎖定中，存在以下兩種基本的模式。

排他鎖（Exclusive，簡稱X鎖）：一旦用戶對某個資源添加了X鎖，則其他用戶都不能再對該資源添加任何類型的鎖，直到該用戶釋放了資源上的X鎖為止。

共享鎖（Share，簡稱S鎖）：一旦用戶對某個資源添加了S鎖，則其他用戶都不能在該資源上添加X鎖，只能添加S鎖，直到該用戶釋放了資源上的S鎖為止。

根據(jù)被保護的對象種類的不同，鎖定可以分成多種類型，比如由于DDL引起的鎖定、DML事務(wù)引起的鎖定、分布式事務(wù)中涉及的鎖定等。我們主要應(yīng)該關(guān)注DML事務(wù)引起的鎖定。

1.4 DML事務(wù)鎖定的機制

鎖定能夠保證當(dāng)某個用戶正在更新表里的一行數(shù)據(jù)時，其他用戶不能同時更新相同的數(shù)據(jù)行，而且也不能刪除或修改被更新的表。

鎖定分為兩種級別：行級別（TX鎖）和表級別（TM鎖）。

1.4.1 行級鎖（TX鎖）

假設(shè)某個用戶（假設(shè)為A）發(fā)出如下的語句更新一條記錄：

update employees set last_name='HanSijie' 
where employee_id=100;

Oracle在對該SQL進行解析以后，找到employee_id為100的記錄所在的數(shù)據(jù)塊（假設(shè)為58號數(shù)據(jù)塊），并找一個可用的undo數(shù)據(jù)塊，將last_name列上被更新前的舊值放入該undo數(shù)據(jù)塊，然后在數(shù)據(jù)塊頭部分配一個ITL槽，在該ITL槽里存放當(dāng)前的事務(wù)ID號、SCN號、所使用的undo數(shù)據(jù)塊的地址，以及當(dāng)前還未提交的標(biāo)記等信息。接下來，在58號數(shù)據(jù)塊中，找到被更新的數(shù)據(jù)行，在其頭部設(shè)置一個鎖定標(biāo)記，并在頭部記錄當(dāng)前事務(wù)所使用的ITL槽的槽號。做完這些工作以后，將控制權(quán)（也就是光標(biāo)）返回給用戶。該鎖定標(biāo)記說明當(dāng)前用戶在被修改的數(shù)據(jù)行上已經(jīng)添加了X鎖。

如果這時，另一個用戶（假設(shè)為N）也對employee_id為100的記錄進行修改，則其過程和上面描述的一樣，只不過B在對數(shù)據(jù)行的頭部設(shè)置鎖定標(biāo)記時，發(fā)現(xiàn)該數(shù)據(jù)行頭部已經(jīng)有一個鎖定標(biāo)記了，說明該記錄已經(jīng)被添加了X鎖，于是用戶進程N必須等待，等待該X鎖被釋放。

可以看到，Oracle數(shù)據(jù)庫是在物理層面上實現(xiàn)對數(shù)據(jù)行的鎖定問題。而且鎖定一條記錄，并不影響其他用戶對該記錄的讀取。比如，如果當(dāng)前有一個用戶（假設(shè)為C）發(fā)出SQL語句，檢索employee_id為100的記錄信息，這時服務(wù)器進程發(fā)現(xiàn)被檢索的記錄有鎖定標(biāo)記，說明當(dāng)前該記錄已經(jīng)被其他用戶修改了，但是還沒提交。于是根據(jù)數(shù)據(jù)行頭部記錄的ITL槽的槽號，在數(shù)據(jù)塊頭部找到該ITL槽，并根據(jù)其中記錄的undo數(shù)據(jù)塊的地址，找到該undo數(shù)據(jù)塊，將其中所保存的改變前的舊值取出，并據(jù)此構(gòu)建CR（Consistent Read一致性讀）塊，該CR塊中的數(shù)據(jù)就是被更新的數(shù)據(jù)塊（也就是58號數(shù)據(jù)塊）在更新前的內(nèi)容。于是根據(jù)該CR塊的內(nèi)容，將用戶所需要的信息返回給C。

對于Oracle數(shù)據(jù)庫來說，行級鎖只有X鎖定模式，沒有S鎖定模式。Oracle的鎖定總是盡可能地在最低級別上完成。比如更新數(shù)據(jù)行時，僅僅是鎖定被更新的數(shù)據(jù)行，并不會鎖定同一個數(shù)據(jù)塊中的其他數(shù)據(jù)行，也不會阻塞其他用戶查詢被更新的數(shù)據(jù)行。

1.4.2 表級鎖（TM鎖）

還是接著上面的例子，這時A用戶已經(jīng)發(fā)出了更新employee_id為100的記錄的SQL語句。當(dāng)A還沒有提交之前，另外一個用戶D發(fā)出下面的語句：

drop table employees;

或者是 update 沒有走索引

由于用戶A還沒有提交所做的事務(wù)，因此該事務(wù)還沒有結(jié)束，其他用戶還不能刪除該表，否則A所發(fā)出的事務(wù)就無法正常結(jié)束。為了阻止這時用戶D的刪除操作，我們能夠想到的最直觀的方法就是，在執(zhí)行刪除表的命令之前，先依次檢查employees表里的每一條記錄，查看每一條數(shù)據(jù)行的頭部是否存在鎖定標(biāo)記，如果是，則說明當(dāng)前正有事務(wù)在更新該表，刪除表的操作必須等待。

顯然，這種方式會引起很大的性能問題，Oracle不會采用這種方式。實際上，當(dāng)我們在對employees表的數(shù)據(jù)進行更新時，不僅會在數(shù)據(jù)行的頭部記錄行級鎖，而且還會在表的級別上添加一個表級鎖。那么當(dāng)D用戶要刪除表時，發(fā)現(xiàn)employees表上具有一個表級鎖，于是等待。

通過這種在表級別上添加鎖定的方式，我們就能夠比較容易并且高效地（因為不需要掃描表里的每一條記錄來判斷在表上是否有DML事務(wù)）對鎖定進行管理了。表級鎖共具有五種模式，如下所示。

行級排他鎖（Row Exclusive，簡稱RX鎖）

當(dāng)我們進行DML時會自動在被更新的表上添加RX鎖，或者也可以通過執(zhí)行l(wèi)ock命令顯式的在表上添加RX鎖。在該鎖定模式下，允許其他的事務(wù)通過DML語句修改相同表里的其他數(shù)據(jù)行，或通過lock命令對相同表添加RX鎖定，但是不允許其他事務(wù)對相同的表添加排他鎖（X鎖）。

行級共享鎖（Row Shared，簡稱RS鎖）

通常是通過select … from for update語句添加的，同時該方法也是我們用來手工鎖定某些記錄的主要方法。比如，當(dāng)我們在查詢某些記錄的過程中，不希望其他用戶對查詢的記錄進行更新操作，則可以發(fā)出這樣的語句。當(dāng)數(shù)據(jù)使用完畢以后，直接發(fā)出rollback命令將鎖定解除。當(dāng)表上添加了RS鎖定以后，不允許其他事務(wù)對相同的表添加排他鎖，但是允許其他的事務(wù)通過DML語句或lock命令鎖定相同表里的其他數(shù)據(jù)行。

共享鎖（Share，簡稱S鎖）

通過lock table in share mode命令添加該S鎖。在該鎖定模式下，不允許任何用戶更新表。但是允許其他用戶發(fā)出select …from for update命令對表添加RS鎖。

排他鎖（Exclusive，簡稱X鎖）

通過lock table in exclusive mode命令添加X鎖。在該鎖定模式下，其他用戶不能對表進行任何的DML和DDL操作，該表上只能進行查詢。

共享行級排他鎖（Share Row Exclusive，簡稱SRX鎖）

通過lock table in share row exclusive mode命令添加SRX鎖。該鎖定模式比行級排他鎖和共享鎖的級別都要高，這時不能對相同的表進行DML操作，也不能添加共享鎖。

二、死鎖的定位方法

通過檢查數(shù)據(jù)庫表，能夠檢查出是哪一條語句被死鎖，產(chǎn)生死鎖的機器是哪一臺。

2.1 用dba用戶查看死鎖

select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

如果有輸出的結(jié)果，則說明有鎖，且能看到死鎖的機器是哪一臺。字段說明：
Username：死鎖語句所用的數(shù)據(jù)庫用戶；
Lockwait：死鎖的狀態(tài)，如果有內(nèi)容表示被死鎖。
Status： 狀態(tài)，active表示被死鎖
Machine： 死鎖語句所在的機器。
Program： 產(chǎn)生死鎖的語句主要來自哪個應(yīng)用程序。

2.2、用dba用戶查看到被死鎖的語句

select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

三、死鎖的解決方法

一般情況下，只要將產(chǎn)生死鎖的語句提交就可以了，但是在實際的執(zhí)行過程中。用戶可能不知道產(chǎn)生死鎖的語句是哪一句?？梢詫⒊绦蜿P(guān)閉并重新啟動就可以了。經(jīng)常在Oracle的使用過程中碰到這個問題，所以也總結(jié)了一點解決方法。

3.1、查找死鎖的進程

sqlplus “/as sysdba” (sys/change_on_install)

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

3.2 查看引起鎖的語句

select A.SQL_TEXT, B.USERNAME, C.OBJECT_ID, C.SESSION_ID,  B.SERIAL#, C.ORACLE_USERNAME,C.OS_USER_NAME,C.Process,
       ''''||C.Session_ID||','||B.SERIAL#||''''
from v$sql A, v$session B, v$locked_object C
where A.HASH_VALUE = B.SQL_HASH_VALUE and
B.SID = C.Session_ID

3.2、kill掉這個死鎖的進程：

alter system kill session ‘sid,serial#’; 
#（其中sid=l.session_id）

3.3、如果還不能解決：

select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

其中sid用死鎖的sid替換

ps -ef|grep spid

其中spid是這個進程的進程號，kill掉這個Oracle進程

(1)鎖表查詢的代碼有以下的形式：

select count(*) from v$locked_object;
select * from v$locked_object;

(2)查看哪個表被鎖

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

(3)查看是哪個session引起的

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

(4)殺掉對應(yīng)進程
執(zhí)行命令：

alter system kill session'1025,41';

其中1025為sid,41為serial#.

四、綜上所訴形成kill語句

SELECT SQ.SQL_TEXT, SQ.SQL_FULLTEXT, A.OBJECT_ID, A.LOCKED_MODE, A.SESSION_ID,C.serial#, B.OBJECT_NAME, P.PID, P.SPID, 
 'ALTER SYSTEM KILL SESSION '''||C.sid|| ', '||C.serial#|| '''; ' 
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B, V$SESSION C, V$PROCESS P, V$SQLAREA SQ
 WHERE A.OBJECT_ID = B.OBJECT_ID
   AND A.SESSION_ID = C.SID(+)
   and c.SQL_ID=sq.SQL_ID(+)
   AND C.PADDR = P.ADDR;

五、行級鎖查詢處理

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('sys')
AND   l.id1        = o.object_id (+)
AND   l.sid        = s.sid
ORDER BY sid, type;

查看機器、用戶、狀態(tài)、SQL語句、連接工具

SELECT username, machine, type, status,SID,SERIAL#,MODULE,ACTION,EVENT#,EVENT
from v$session T
WHERE username IN（'SYS'）
AND SID IN (SELECT SID
FROM v$lock
WHERE  TYPE='TM');


SELECT a.username, a.machine, a.type, a.status,a.SID,a.SERIAL#,a.PROGRAM,a.MODULE,
a.MODULE,a.ACTION,a.EVENT#,a.EVENT,b.type
from v$session a 
left join  v$lock b on a.sid = b.sid
where a.USERNAME = 'SYS'  AND b.TYPE = 'TM';

文章來源地址http://www.zghlxwxcb.cn/news/detail-732044.html

SELECT l.sid, s.blocking_session blocker,s.event,l.type,
l.lmode,l.request,o.object_name,o.object_type,s.SQL_ID,s.ACTION
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('sys')
AND   l.id1        = o.object_id (+)
AND   l.sid        = s.sid
ORDER BY sid, type;

select * from v$sqlarea b where b.SQL_ID = 'd1scr63zq42gf'

到了這里，關(guān)于大數(shù)據(jù)-玩轉(zhuǎn)數(shù)據(jù)-oracle的鎖查詢及處理的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！