Skip to Content
0
Former Member
Jun 02, 2008 at 10:44 AM

Deadlock problem

37 Views

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 ?