有时因为DML语句没有及时commit,导致其它session等待其占用的锁，引起hang.
引用：
http://www.itpub.net/244694.html
http://www.builder.com.cn/developer/database/story/0,3800066906,39451598-1,00.htm
1.找到等待的session
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null
and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
union
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.username is not null
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;
2.取出session等待的信息
select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# from v$session where sid = &sid;
3.取出等待的对象，所在的表空间
select owner, segment_name, segment_type, tablespace_name from dba_extents
where file_id = &fileid_in
and &blockid_in between block_id and block_id + blocks - 1
但是还不能查到引起持有资源的那个sql?
其它：
asktom上tom对lock的一个答复
You Asked (Jump to Tom's latest followup)
Hi Tom,
Here are the few questions on Locking.
1)How do I use v$lock to figure out lock details ? Details like
whos has locked,on which table,who else is waiting, and what type
of locking (shared or exlusive ?), since when it is locked.
2)Why and when exclusive/shared locks are used ? Could you give me
example please ?
Thanks for the answer, I understand better.
3)Could you tell me significance of v$mystat and v$locked_object ?
Thank you .
4) Do we have page level locking in Oracle ?
Thank you
Thanks in advance,
Gururaj
and we said...
1) Here is a small example showing how this happens. We will use three V$ tables
in order to see how this works; V$TRANSACTION, V$SESSION, and V$LOCK.
V$TRANSACTION contains an entry for every active transaction. V$LOCK contains an
entry for all locks held as well as locks being waited on. V$SESSION shows us
the sessions logged in. We'll start by starting a transaction in one session and
looking at the state of the system at that point:
tkyte@TKYTE816> update dept set deptno = deptno+10;
4 rows updated.
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 46 160
The interesting things to note here are:
ć The LMODE is 6 in the V$LOCK table and the request is 0. If you refer to the
definition of the V$LOCK table in the Oracle Server Reference, you will find
that LMODE=6 is an exclusive lock. A value of 0 in the request means we are not
making a request ¡V we have the lock.
ć There is only one row in this table. This V$LOCK table is more of a queuing
table than a lock table. Many people expect there would be four rows in V$LOCK
since we have four rows locked. What you must remember however is that Oracle
does not store a master list of every row locked anywhere. To find out if a row
is locked, we must go to that row.
ć I took the ID1 and ID2 columns, and performed a bit of bit manipulation on
them. Oracle needed to save three 16bit numbers, but only had two columns in
order to do it. So, the first column ID1 holds two of these numbers. By dividing
by 2^16 with trunc(id1/power(2,16)) rbs and by masking out the high bits with
bitand(id1,to_number('ffff','xxxx'))+0 slot, I am able to get the two numbers
that are hiding in that one number back out.
ć The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my
transaction ID.
Now I'll start another session using the same user name, update some rows in
EMP, and then try to update DEPT:
tkyte@TKYTE816> update emp set ename = upper(ename);
14 rows updated.
tkyte@TKYTE816> update dept set deptno = deptno-10;
I am now blocked in this session. If we run the V$ queries again, we see:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
TKYTE 9 2 46 160 0 6
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
2 46 160
What we see here is that a new transaction has begun ¡V (3,82,163) is the
transaction ID. It has two rows in V$LOCK this time. One row represents the
locks that it owns (where LMODE=6). It also has a row in there that shows a
REQUEST with a value of 6. This is a request for an exclusive lock. The
interesting thing to note here is that the RBS/SLOT/SEQ values of this request
row are the transaction ID of the holder of the lock. We can easily see that the
transaction with SID=8 is blocking the transaction with SID=9. Now, if we commit
in SID = 8 the above changes:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request, block
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
that request row has gone ¡V it disappeared the instant the other session gave
up its lock. That request row was the queuing mechanism. The database is able to
wake up the blocked sessions the instant the transaction is completed. Note that
the above gives us a very easy way to see blockers and waiters:
tkyte@TKYTE816> select
(select username from v$session where sid=a.sid) blocker,
2 a.sid,
3 ' is blocking ',
4 (select username from v$session where sid=b.sid) blockee,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and a.id2 = b.id2
11 /
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
-------- ---------- ------------- -------- ----------
TKYTE 8 is blocking TKYTE 9
simply by doing a self-join of V$LOCK with itself (I ran this query before
committing the session with SID=8).
2) exclusive lock -- I updated a row. no one else can update it until I commit.
I have an X lock on that row and only one person at a time can have an X lock.
an X lock provides serialization to a resource.
A shared lock -- when I update a table, I take a shared lock on the DEFINITION
of the table. Everyone else can do that as well (more then one session can get
a shared lock on the table definition). So, more than one person at a time can
update the table. If you wanted to ALTER the table, you would need an X lock on
the defintion. You cannot get an X lock when there are shared locks so you wait
until there are no shared locks.
3) mystat has the statistics (cpu use, blocks read, cursors opened, etc) for
your session only. v$locked_object shows you all of the locks in the system.
4) no, never.
--------