v$lock

V$LOCK lists the locks currently held by the Oracle Database and outstanding(未完成的) requests for a lock or latch.

ADDR? - Address of lock state object

KADDR - Address of lock

SID - Identifier for session holding or acquiring the lock

TYPE - Type of user or system lock

ID1 - Lock identifier #1 (depends on type)

ID2 - Lock identifier #2 (depends on type)

LMODE - Lock mode in which the session holds the lock

REQUEST - Lock mode in which the process requests the lock

CTIME - Time since current mode was granted

BLOCK - A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

個(gè)別字段說(shuō)明：

SID : 持有鎖或等待鎖的會(huì)話id

TYPE : 鎖的類型, 其中用戶類型的鎖分為：TX(行鎖) TM(表鎖) UL(User supplied)

ID1 : 若type=TM,? id1為對(duì)象的id, ID2=0;

若type=TX，將id1轉(zhuǎn)化為0xUUUUSSSS形式,則 UUUU對(duì)應(yīng)v$transaction中的XIDUSN，SSSS對(duì)應(yīng)v$transaction總的XIDSLOT, ID2對(duì)應(yīng)v$TRANSACTION中的XIDSQN

LMODE : 會(huì)話持有的鎖模式

REQUEST : 進(jìn)程請(qǐng)求的鎖模式

BLOCK??: 為1代表阻礙者，表示正在阻礙其它會(huì)話

解決問(wèn)題：某些會(huì)話莫名其妙的被阻塞了，定位到底是被誰(shuí)阻塞了？

1. 執(zhí)行如下語(yǔ)句

select t.OSUSER,t.PROCESS,t.MACHINE,t.PROGRAM,t.USER#,t.USERNAME,t.SID,t.SERIAL#,t1.SQL_TEXT,t.BLOCKING_INSTANCE,t.BLOCKING_SESSION,t.EVENT "cause"

from v$session t,v$sqltext t1

where t.SQL_ADDRESS=t1.ADDRESS

and t.SQL_HASH_VALUE=t1.HASH_VALUE

and t.LOCKWAIT is not null

and t.BLOCKING_SESSION_STATUS='VALID';

根據(jù)上面的cause列以及blocking_instance blocking_session再去定位blocking_session,要和發(fā)起blocking_session的用戶商量，是否對(duì)應(yīng)的session不再用，若不再用，則kill 掉

2. kill 掉 blocking session

alter system kill session 'blocking_sid,blocking_serial#';? --這里要注意了，不要把系統(tǒng)的session kill掉

小技巧：

--查看當(dāng)前會(huì)話的sid

select userenv('sid') from dual;

--查看正在阻塞其他會(huì)話的會(huì)話

SQL> select * from v$lock where block=1;

ADDR?????????? KADDR?????????? SID? TY??????? ID1??????? ID2????? LMODE??? REQUEST????? CTIME????? BLOCK

--------????? ??? --------?????????????? ------? ----??????----------- ---------- ---------- ---------- ---------- ----------

44AC5434? ?44AC5550???????? 18? TX???? 655380??????? 128????????? 6????????? 0??????? 144????????? 1

總結(jié)

以上是生活随笔為你收集整理的lock object oracle,oracle lock 08 - 关于锁的解决方案的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。