oracle11g 行锁 表锁,示例：Oracle表锁、行锁模拟和处理

for update模拟锁表

--session 1

SQL> select * from tt for update;

--session 2

SQL> update tt set id=1 where id=2;

按用户查询锁的情况

set line 300

col OBJECT_NAME format a30

col event format a30

col type format a10

col object_name format a15

col object_type format a15

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('&User')

AND l.id1 = o.object_id (+)

AND l.sid = s.sid

ORDER BY sid, type;

Enter value for user: zylong

old 3: WHERE UPPER(s.username) = UPPER('&User')

new 3: WHERE UPPER(s.username) = UPPER('zylong')

SID BLOCKER EVENT TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE

---------- ---------- ------------------------------ ---------- ---------- ---------- --------------- ---------------

27 SQL*Net message from client AE 4 0 ORA$BASE EDITION

32 SQL*Net message from client AE 4 0 ORA$BASE EDITION

35 SQL*Net message from client AE 4 0 ORA$BASE EDITION

35 SQL*Net message from client TM 3 0 TT TABLE

35 SQL*Net message from client TX 6 0

40 35 enq: TX - row lock contention AE 4 0 ORA$BASE EDITION

40 35 enq: TX - row lock contention TM 3 0 TT TABLE

40 35 enq: TX - row lock contention TX 0 6

8 rows selected.

查询锁的源头

with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter

from gv$session

where blocking_instance is not null

and blocking_session is not null)

select lpad(' ',2*(level-1))||waiter lock_tree from

(select * from lk

union all

select distinct 'root', blocker from lk

where blocker not in (select waiter from lk))

connect by prior waiter=blocker start with blocker='root';

LOCK_TREE

--------------------------------------------------------------------------------------------------

1.35

1.40

查询进程信息

col SPID for a10

col MACHINE for a15

col PROGRAM for a25

col OBJECT_NAME for a15

col ORACLE_USERNAME for a15

col OS_USER_NAME for a15

select b.session_id,

a.serial#,

p.spid,

a.machine,

a.program,

a.status,

c.object_name,

b.oracle_username,

b.os_user_name,

a.sql_id

from v$process p, v$session a, v$locked_object b, all_objects c

where p.addr = a.paddr

and a.process = b.process

and c.object_id = b.object_id;

SESSION_ID SERIAL# SPID MACHINE PROGRAM STATUS OBJECT_NAME ORACLE_USERNAME OS_USER_NAME SQL_ID

---------- ---------- ---------- --------------- ------------------------- -------- --------------- --------------- --------------- -------------

40 2237 13530 test sqlplus@test (TNS V1-V3) ACTIVE TT ZYLONG oracle 1t3admr0mnxd6

35 1675 13248 test sqlplus@test (TNS V1-V3) INACTIVE TT ZYLONG oracle

查询SQL信息

SQL> select SQL_TEXT from v$sql where SQL_ID='1t3admr0mnxd6';

SQL_TEXT

------------------------------------------------------------------------

update tt set id=1 where id=2

--or

set long 500000

select SQL_FULLTEXT from v$sql where SQL_ID='1t3admr0mnxd6';

kill session

alter system kill session '35,1675' immediate;

--or

$ ps -ef |grep spid

% kill -9 spid

c:\> orakill

其中 = Oracle实例名称(ORACLE_SID) =要杀死的线程的线程ID