做数据库的都经常会碰到数据库死锁问题,当死锁发生时使得对数据库的操作不能继续影响业务的继续进行。要检测和发现数据库的死锁问题主要通过如下几张表:v$session,v$lock,v$locked_object,dba_objects,dba_blockers。
动态视图v$session中主要显示了当前数据库连接的会话信息,对于检测死锁问题主要有这几个字段可以用:sid,serial#,username。
动态视图v$lock中主要存放的每个session获得的相应的锁的信息,但不一定是死锁。里面还包含了锁的类型,创建时间,地址等信息。
动态视图v$locked_object包含里面的session_id对应与v$session里面的sid字段,锁的对象id,即v$lock表中锁所锁定的对象object_id,还包括了操作系统用户、oracle用户、锁模式等信息。
dba_objects就不用详细介绍了,预了解可以参考官方文档。
dba_blockers里面只有一个字段HOLDING_SESSION,是拥有锁的session_id和SID同义。
上面介绍了那么多表,下面介绍一下死锁的检测:
首先要想查看锁的情况,连接数据库的用户必须要有意思表和相关视图的访问权限,一般使用具有dba权限的用户访问,例如:sqlplus "/as sysdba"。连接后可以使用如下SQL检测:“提示”在被杀死的会话中无论是使用commit语句还是rollback语句,都会出现“ORA-00028: 您的会话己被终止”的提示,并且需要重新登录才能操作数据库。
执行后会有相应的结果如下:
-- ---------------------------- ---------- ----------
TEST_LOCK 121 2104
最后如果要解决这个session引起的死锁问题,我们可以使用SQL语句将这个session杀死。
附:相关表的字段
SQL> DESC v$session
Name Type Nullable Default Comments
----------------------- ------------ -------- ------- --------
SADDR RAW(4) Y
SID NUMBER Y
SERIAL# NUMBER Y
AUDSID NUMBER Y
PADDR RAW(4) Y
USER# NUMBER Y
USERNAME VARCHAR2(30) Y
COMMAND NUMBER Y
OWNERID NUMBER Y
TADDR VARCHAR2(8) Y
LOCKWAIT VARCHAR2(8) Y
STATUS VARCHAR2(8) Y
SERVER VARCHAR2(9) Y
SCHEMA# NUMBER Y
SCHEMANAME VARCHAR2(30) Y
OSUSER VARCHAR2(30) Y
PROCESS VARCHAR2(12) Y
MACHINE VARCHAR2(64) Y
TERMINAL VARCHAR2(30) Y
PROGRAM VARCHAR2(48) Y
TYPE VARCHAR2(10) Y
SQL_ADDRESS RAW(4) Y
SQL_HASH_VALUE NUMBER Y
SQL_ID VARCHAR2(13) Y
SQL_CHILD_NUMBER NUMBER Y
PREV_SQL_ADDR RAW(4) Y
PREV_HASH_VALUE NUMBER Y
PREV_SQL_ID VARCHAR2(13) Y
PREV_CHILD_NUMBER NUMBER Y
MODULE VARCHAR2(48) Y
MODULE_HASH NUMBER Y
ACTION VARCHAR2(32) Y
ACTION_HASH NUMBER Y
CLIENT_INFO VARCHAR2(64) Y
FIXED_TABLE_SEQUENCE NUMBER Y
ROW_WAIT_OBJ# NUMBER Y
ROW_WAIT_FILE# NUMBER Y
ROW_WAIT_BLOCK# NUMBER Y
ROW_WAIT_ROW# NUMBER Y
LOGON_TIME DATE Y
LAST_CALL_ET NUMBER Y
PDML_ENABLED VARCHAR2(3) Y
FAILOVER_TYPE VARCHAR2(13) Y
FAILOVER_METHOD VARCHAR2(10) Y
FAILED_OVER VARCHAR2(3) Y
RESOURCE_CONSUMER_GROUP VARCHAR2(32) Y
PDML_STATUS VARCHAR2(8) Y
PDDL_STATUS VARCHAR2(8) Y
PQ_STATUS VARCHAR2(8) Y
CURRENT_QUEUE_DURATION NUMBER Y
CLIENT_IDENTIFIER VARCHAR2(64) Y
BLOCKING_SESSION_STATUS VARCHAR2(11) Y
BLOCKING_INSTANCE NUMBER Y
BLOCKING_SESSION NUMBER Y
SEQ# NUMBER Y
EVENT# NUMBER Y
EVENT VARCHAR2(64) Y
P1TEXT VARCHAR2(64) Y
P1 NUMBER Y
P1RAW RAW(4) Y
P2TEXT VARCHAR2(64) Y
P2 NUMBER Y
P2RAW RAW(4) Y
P3TEXT VARCHAR2(64) Y
P3 NUMBER Y
P3RAW RAW(4) Y
WAIT_CLASS_ID NUMBER Y
WAIT_CLASS# NUMBER Y
WAIT_CLASS VARCHAR2(64) Y
WAIT_TIME NUMBER Y
SECONDS_IN_WAIT NUMBER Y
STATE VARCHAR2(19) Y
SERVICE_NAME VARCHAR2(64) Y
SQL_TRACE VARCHAR2(8) Y
SQL_TRACE_WAITS VARCHAR2(5) Y
SQL_TRACE_BINDS VARCHAR2(5) Y
SQL> DESC v$lock
Name Type Nullable Default Comments
------- ----------- -------- ------- --------
ADDR RAW(4) Y
KADDR RAW(4) Y
SID NUMBER Y
TYPE VARCHAR2(2) Y
ID1 NUMBER Y
ID2 NUMBER Y
LMODE NUMBER Y
REQUEST NUMBER Y
CTIME NUMBER Y
BLOCK NUMBER Y
SQL> DESC v$locked_object
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
XIDUSN NUMBER Y
XIDSLOT NUMBER Y
XIDSQN NUMBER Y
OBJECT_ID NUMBER Y
SESSION_ID NUMBER Y
ORACLE_USERNAME VARCHAR2(30) Y
OS_USER_NAME VARCHAR2(30) Y
PROCESS VARCHAR2(12) Y
LOCKED_MODE NUMBER Y
SQL> DESC dba_objects
Name Type Nullable Default Comments
-------------- ------------- -------- ------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30) Y Username of the owner of the object
OBJECT_NAME VARCHAR2(128) Y Name of the object
SUBOBJECT_NAME VARCHAR2(30) Y Name of the sub-object (for example, partititon)
OBJECT_ID NUMBER Y Object number of the object
DATA_OBJECT_ID NUMBER Y Object number of the segment which contains the object
OBJECT_TYPE VARCHAR2(19) Y Type of the object
CREATED DATE Y Timestamp for the creation of the object
LAST_DDL_TIME DATE Y Timestamp for the last DDL change (including GRANT and REVOKE) to the object
TIMESTAMP VARCHAR2(19) Y Timestamp for the specification of the object
STATUS VARCHAR2(7) Y Status of the object
TEMPORARY VARCHAR2(1) Y Can the current session only see data that it place in this object itself?
GENERATED VARCHAR2(1) Y Was the name of this object system generated?
SECONDARY VARCHAR2(1) Y Is this a secondary object created as part of icreate for domain indexes?
SQL> DESC dba_blockers
Name Type Nullable Default Comments
--------------- ------ -------- ------- --------
HOLDING_SESSION NUMBER Y