Oracle数据库死锁问题探讨与处理

做数据库的都经常会碰到数据库死锁问题，当死锁发生时使得对数据库的操作不能继续影响业务的继续进行。要检测和发现数据库的死锁问题主要通过如下几张表：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: 您的会话己被终止”的提示，并且需要重新登录才能操作数据库。

select  a.username, a.sid, a.serial#  from  v$session a,dba_blockers b  where  a.sid  =  b.holding_session;

执行后会有相应的结果如下：

USERNAME                              SID    SERIAL#
-- ---------------------------- ---------- ----------
TEST_LOCK                            121       2104

最后如果要解决这个session引起的死锁问题，我们可以使用SQL语句将这个session杀死。

alter  system  kill  session  '121,2104';

 

附：相关表的字段

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