前两天同事又碰到oracle连接n慢的问题,由于这个现象时有时无,于是我检查了下是不是表或者其他对象有被锁住,或者是否有死锁。对象资源被锁是一个经常要碰到的急需解决的问题。
在Oracle里,当前的锁的信息存储在动态性能视图v$lock和v$locked_object中。先来看下它们定义:
Oracle官方文档及简单翻译 (oracle官方可下载资源网址在我的收藏里有列出):
V$LOCK
V$LOCK
lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
视图V$LOCK列出了当前被Oracle数据库持有的所有的锁以及未完成的锁和栓锁请求。
Column | Datatype | Description |
---|---|---|
ADDR | RAW(4 | 8) | Address of lock state object/内存中被锁的对象的地址 |
KADDR | RAW(4 | 8) | Address of lock/内存中锁地址 |
SID | NUMBER | Identifier for session holding or acquiring the lock/持有或申请锁的会话标识号 |
TYPE | VARCHAR2(2) | Type of user or system lock/系统锁或者用户锁类型 The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table 8-1 .
|
ID1 | NUMBER | Lock identifier #1 (depends on type) 锁的第1标识号 如果锁的类型是TM,该值表示将要被锁定的对象的标识号; 如果锁的类型是TX,该值表示撤销段号码的十进制值 |
ID2 | NUMBER | Lock identifier #2 (depends on type) 锁的第2标识号。 如果锁的类型是TM,该值为0; 如果锁的类型是TX,该值表示交换次数 |
LMODE | NUMBER | Lock mode in which the session holds the lock: 会话保持的锁的模式
|
REQUEST | NUMBER | Lock mode in which the process requests the lock: 会话申请的锁的模式。与LMODE中的模式相同
|
CTIME | NUMBER | Time since current mode was granted 以秒为单位的,获得当前锁(或转换成当前锁的模式)以来的时间 |
BLOCK | NUMBER | A value of either 0 or 1, depending on whether or not the lock in question is the blocker. 当前锁是否阻塞另一个锁。0=不阻塞;1=阻塞 |
V$LOCKED_OBJECT
V$LOCKED_OBJECT
lists all locks acquired by every transaction on the system. It shows which sessions are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.
视图V$LOCKED_OBJECT列出系统中每个事务获得的所有锁。它列出了那些以具体哪些模式对具体哪些对象持有TM锁的会话。
Column | Datatype | Description |
---|---|---|
XIDUSN | NUMBER | Undo segment number/撤销段号码 |
XIDSLOT | NUMBER | Slot number/被锁定的对象在撤销段中的位置 |
XIDSQN | NUMBER | Sequence number/序列号 |
OBJECT_ID | NUMBER | Object ID being locked/被锁定的对象的标识号 |
SESSION_ID | NUMBER | Session ID/ 会话的标识号 |
ORACLE_USERNAME | VARCHAR2(30) | Oracle user name/Oracle用户名 |
OS_USER_NAME | VARCHAR2(30) | OS user name/操作系统用户名 |
PROCESS | VARCHAR2(12) | OS process ID/操作系统进程标识号 |
LOCKED_MODE | NUMBER | Lock mode/ 对象被锁定的模式。 0=None;1=Null;2=Row-S (SS);3=Row-X (SX); 4=Share;5=S/Row-X (SSX);6=Exclusive |
查找有关锁/死锁的更多我们需要的信息有时还需要通过表all_objects或者视图v$session,它们就不再一一讲解,请到oracle官方文档 查询。
查询有关锁的信息:
查询一:
select a.os_user_name,
a.oracle_username,
a.object_id,
c.object_name,
c.object_type
from v$locked_object a, dba_objects c
where a.object_id=c.object_id;
查询二:
select s.PROCESS,
s.SID,
s.SERIAL#,
b.object_name,
b.subobject_name,
a.LOCKED_MODE,
s.OSUSER,
s.LOGON_TIME,
s.MACHINE,
s.PROGRAM,
s.SQL_ADDRESS,
s.SQL_HASH_VALUE
from v$locked_object a, dba_objects b, v$session s
where a.OBJECT_ID = b.object_id
and a.SESSION_ID = s.SID
找到锁/死锁就好办了。找到相应需要解开的锁可以通过完成该事务或者杀死会话解锁。
杀锁进程可以用以下命令:
alter system kill session '
'查出的SID,查出的SERIAL#'。(这个必须要用查询二来获得相关Session信息)
杀oracle会话进程有时会失效,需要根据SPID到系统中杀相应的oracle进程。另外要注意的是, 我看到网上有外国的学者说, 当直接连接数据库时, 直接用OS命令 $kill process_num 或者 $kill -9 process_num杀系统进程来终止用户连接也不能完全解决问题, 因为一个用户进程可能产生一个以上的锁,杀oracle进程不能彻底解决锁的问题。
Reference: