Hello,
We have a problem with deadlock again.
You wrote, before, that guy handling locks can check LOCK and LOCK_WAIT table.
Here is the table content:
Table LOCKS
SESSION TRANSCOUNT PROCESS USERNAME DATE TIME TERMID LASTWRITE LOCKMODE LOCKSTATE APPLPROCESS APPLNODE SCHEMANAME OWNER TABLENAME TABLEID ROWIDLENGTH ROWIDHEX ROWID 1118105 211286 109 ADMIN carpc 404 470 row_exclusive write 1028 localhost ADMIN ADMIN LOGSDS 00000000000003EC 5 00C53771900000000000 1118105 211286 109 ADMIN carpc 404 470 row_exclusive write 1028 localhost ADMIN ADMIN LOGFILE 0000000000000570 9 00FFFE0000000098AB00 x'FFFE0000000098AB' 1119331 211339 120 ADMIN carpc E88 tab_share temp 3720 localhost ADMIN ADMIN APPUSERSESSION 00000000000003E9 0 1119331 211339 120 ADMIN carpc E88 tab_share temp 3720 localhost ADMIN ADMIN EVENTUSER 00000000000003EA 0 1119331 211339 120 ADMIN carpc E88 tab_share temp 3720 localhost ADMIN ADMIN EVENTSTATEMENT 00000000000003EB 0 1118044 211347 108 ADMIN carpc 5E4 tab_share temp 1508 localhost ADMIN ADMIN APPUSERSESSION 00000000000003E9 0 1118044 211347 108 ADMIN carpc 5E4 tab_share temp 1508 localhost ADMIN ADMIN EVENTUSER 00000000000003EA 0 1118044 211347 108 ADMIN carpc 5E4 tab_share temp 1508 localhost ADMIN ADMIN EVENTSTATEMENT 00000000000003EB 0 1118022 211283 106 ADMIN carpc B3C tab_share temp 2876 localhost ADMIN ADMIN APPUSERSESSION 00000000000003E9 0 1118022 211283 106 ADMIN carpc B3C tab_share temp 2876 localhost ADMIN ADMIN EVENTUSER 00000000000003EA 0 1118022 211283 106 ADMIN carpc B3C tab_share temp 2876 localhost ADMIN ADMIN EVENTSTATEMENT 00000000000003EB 0
Table LOCK_WAITS
TABLENAME TABLEID H_TERMID H_PROCESS H_APPLPROCESS H_APPLNODE H_ROWIDHEX H_ROWID H_LOCKMODE H_DATE H_TIME H_LOCKTIMEOUT R_TERMID R_PROCESS R_APPLPROCESS R_APPLNODE R_ROWIDHEX R_ROWID R_REQMODE R_DATE R_TIME R_REQTIMEOUT EVENTUSER 00000000000003EA carpc E88 120 3720 localhost tab_share carpc 404 109 1028 localhost 00C61486280000000000 row_exclusive 4535 EVENTUSER 00000000000003EA carpc 5E4 108 1508 localhost tab_share carpc 404 109 1028 localhost 00C61486280000000000 row_exclusive 4535 EVENTUSER 00000000000003EA carpc B3C 106 2876 localhost tab_share carpc 404 109 1028 localhost 00C61486280000000000 row_exclusive 4535 LOGSDS 00000000000003EC carpc 404 109 1028 localhost 00C53771900000000000 row_exclusive carpc E88 120 3720 localhost tab_share 4535 LOGSDS 00000000000003EC carpc 404 109 1028 localhost 00C53771900000000000 row_exclusive carpc 5E4 108 1508 localhost tab_share 4555 LOGSDS 00000000000003EC carpc 404 109 1028 localhost 00C53771900000000000 row_exclusive carpc B3C 106 2876 localhost tab_share 4535
So, you can see that process which makes the deadlock problem is PID=1028.
This process locks LOGFILE and LOGSDS table fist and then table EVENTUSER.
It seams that when we change lock sequence :
1. lock table EVENTUSER
2. lock LOGFILE and LOGSDS tables
deadlock should not occure.
This was the reason why I put explicit lock statement into my DB procedure.
But, I do not know why this explicit lock does not work.
In "LOCK" table I do not see any EVENTUSER either SDS_IN tables - only LOGFILE and LOGSDS tables are locked before lock EVENTUSER table is requested.
That process (PID=1028) works following:
CALL call insert_sds_in (3, '10122', '10113', 3, 10, x'80B8949DDDA057202600', 12, 3, 0, 0)
where DB procedure insert_sds_in makes:
CREATE DBPROC INSERT_SDS_IN (IN ServerId Integer,
IN SenderAddr Varchar(30), IN ReceiverAddr Varchar(30),
IN DataType Integer, IN DataLen Integer, IN Data Varchar(2084) BYTE,
IN SdsType Integer, IN Protocol Integer, IN Reference Integer,
IN Consume Integer) AS
BEGIN
/* against deadlock */
LOCK (WAIT) TABLE ADMIN.EVENTUSER IN EXCLUSIVE MODE;
/* insert new data */
INSERT ADMIN.SDS_IN
SET SERVER_ID = :ServerId, SENDERADDR = :SenderAddr,
REFERENCE = :Reference, RECEIVERADDR = :ReceiverAddr,
DATATYPE = :DataType, DATALEN = :DataLen, DATA = :Data,
SDSTYPE = :SdsType, PROTOCOL = :Protocol, CONSUME = :Consume;
END;
Inside insert trigger for table SDS_ID I do:
CREATE TRIGGER SDS_IN_INSERT FOR SDS_IN AFTER INSERT EXECUTE
(
...
INSERT ADMIN.LOGSDS
SET .....
...
)
This locks table LOGSDS and runns trigger:
CREATE TRIGGER LOGSDS_INSERT FOR LOGSDS AFTER INSERT EXECUTE
(
...
INSERT ADMIN.LOGFILE
SET ....
... some code ...
INSERT ADMIN.EVENTUSER
SET ....
...
)
This locks table LOGFILE and runns trigger:
CREATE TRIGGER LOGFILE_INSERT FOR LOGFILE AFTER INSERT EXECUTE
(
...
INSERT ADMIN.EVENTUSER
SET ....
...
)
Can you help me please how to prevent such deadlock ??
Thank you for support.
Dusan
BTW: There is some DB parameter "DEADLOCK_DETECTION ". How does it works ?