执行存储过程更新一个表中的数据的时候产生如下的错误：

SQL> exec update_jc_kxx_yxrq;

begin update_jc_kxx_yxrq; end;

ORA-20998: Err=-2049,Msg=0-ORA-02049: 超时: 分布式事务处理等待锁定
ORA-06512: 在"ICUSER.UPDATE_JC_KXX_YXRQ", line 36
ORA-06512: 在line 2

以sys用户登陆数据库查询死锁

SQL> select username,lockwait,status,machine,program from v$session where sid in
2 (select session_id from v$locked_object);

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------
ICUSER 000000038A37B7A8 ACTIVE
icdb
JDBC Thin Client

ICUSER 000000038A37C040 ACTIVE
icdb
JDBC Thin Client

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------

ICUSER 000000038A37BF30 ACTIVE
icdb
JDBC Thin Client

ICUSER INACTIVE
icdb

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------
JDBC Thin Client

ICUSER 000000038A37C150 ACTIVE
WorkGroupNameIC
collect.exe

ICUSER 000000038A37BFB8 ACTIVE

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------
icdb
JDBC Thin Client

ICUSER 000000038A37C0C8 ACTIVE
icdb
JDBC Thin Client

说明数据库有死锁

然后使用一下语句查找被死锁的语句

SQL> select sql_text from v$sql where hash_value in
2 (select sql_hash_value from v$session where sid in
3 (select session_id from v$locked_object));

SQL_TEXT
--------------------------------------------------------------------------------
update JC_KXX SET LJXF =NVL ( LJXF , 0 ) + :1 , YE =:2 WHERE KH =:3
update jc_kxx set zt='07' where kh='1000530330'

再使用以下语句查找被死锁的进程

SQL> SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
2 l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
3 FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30523 32 42463
ICUSER oracle

ICUSER 30523 28 25508
ICUSER oracle

ICUSER 30523 76 14781
ICUSER oracle


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30523 88 3943
ICUSER oracle

ICUSER 30523 46 17889
ICUSER oracle

ICUSER 30582 46 17889
ICUSER oracle


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30603 46 17889
ICUSER oracle

ICUSER 30592 46 17889
ICUSER oracle

ICUSER 30588 46 17889
ICUSER oracle


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30659 62 34578
ICUSER Administrator 1056:1020

ICUSER 30523 62 34578
ICUSER Administrator 1056:1020

ICUSER 30669 62 34578
ICUSER Administrator 1056:1020


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30523 24 37522
ICUSER oracle

使用一下语句把死锁的进程kill

alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

如：SQL> alter system kill session '24,37522';

再次执行存储过程，错误没有了。语句执行成功！