Deadlock problem

[Former Member]

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 ?

[Former Member]

Hi,

perhaps I missed something. But what confuses me really is the fact, that task 1028 has two row locks, one for LOGSDS and one for LOGFILE, but none for SDS_IN.

If he had passed the routine INSERT_SDS_IN, where the lock exclusive is in, I assume, a row lock for INSERT_SDS_IN should / would be seen in systemview LOCKS. But there is none.

Could it be, that the insert in LOGSDS and (because of the trigger) in LOGFILE and the request for EVENTUSER is done without calling that first routine, but just with a 'normal' insert LOGSDS ?

Perhaps you can put an exclusive lock for eventuser directly before insert .. logfile.

That will not prevent deadlocks, but show you/us, if lock exclusive will do, if it will be done when logsds has been inserted. If then exclusive locks will be seen on eventuser, you should find out, which codepart in your application does not use insert_sds_in, although expected to do so.

Elke

[lbreddemann]

Hi Dusan,

I propose we keep this topic in just one thread.

It's already discussed in thread .

Still we need all statements that actually generate the deadlock.

KR Lars

[Former Member]

Hello Lars,

OK, we keep this topic in this thread (I did not seen any response before).

Here are the statements :

process (PID=1028):

CALL call insert_sds_in (3, '10122', '10113', 3, 10, x'80B8949DDDA057202600', 12, 3, 0, 0)
//
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;
//
CREATE TRIGGER SDS_IN_INSERT FOR SDS_IN AFTER INSERT EXECUTE
(
...
  INSERT ADMIN.LOGSDS
    SET .....
...
)
//
CREATE TRIGGER LOGSDS_INSERT FOR LOGSDS AFTER INSERT EXECUTE
(
...
  INSERT ADMIN.LOGFILE
    SET ....
... some code ...
  INSERT ADMIN.EVENTUSER
    SET ....
...
)
//
CREATE TRIGGER LOGFILE_INSERT FOR LOGFILE AFTER INSERT EXECUTE
(
...
  INSERT ADMIN.EVENTUSER
    SET ....
...
)

process (PID=2876):

select * from MapServer_Event where LockName='' order by priority desc, id asc

process (PID=1508):

call Check_AutomaticPM
//
CREATE DBPROC CHECK_AUTOMATICPM AS

  VAR
    radioId       Integer;
    receiverAddr  Varchar(30);
    senderAddr    Varchar(30);

BEGIN
  DECLARE AUTOMATICPM_CUR CURSOR FOR
    SELECT RADIO_ID, SENDERADDR
      FROM ADMIN.AUTOMATICPM
      WHERE INTERVALVALUE > 0 
        AND (FROMDT < Timestamp OR FROMDT = '1899-12-30 00:00:00')
        AND (TODT > Timestamp OR TODT = '1899-12-30 00:00:00')
        AND DECODE (INTERVALUNIT,
              0, ADDTIME (SENTDT, MAKETIME (0, INTERVALVALUE, 0)),
              1, ADDTIME (SENTDT, MAKETIME (INTERVALVALUE, 0, 0)),
              3, ADDDATE (SENTDT, INTERVALVALUE),
              SENTDT) < Timestamp
      FOR REUSE;

  /* generate PM requests*/
  WHILE ($rc = 0) DO
  BEGIN
    FETCH AUTOMATICPM_CUR INTO :radioId, :senderAddr;
    IF ($rc = 0) THEN
    BEGIN
      DECLARE RECEIVERRADIO_CUR CURSOR FOR
        SELECT CHR (ISSI)
          FROM ADMIN.RADIO
            WHERE ID = :radioId;
      FETCH RECEIVERRADIO_CUR INTO :receiverAddr;
      CALL INSERT_MESSAGE (-1, 2, -1, -1, -1, -1, :senderAddr, :receiverAddr,
        1, 0, 12, -1, -1, -1, -1, x'', 0);
      UPDATE ADMIN.AUTOMATICPM
        SET SENTDT = Timestamp
        WHERE RADIO_ID = :radioId;
    END;
  END;
END;
//
CREATE DBPROC INSERT_MESSAGE (IN UsrId Integer, IN MsgId Integer,
  IN DateTimeNum Integer, IN Reference Integer, IN ReferenceSeq Integer,
  IN ServerId Integer, IN SenderAddr Varchar(30), IN ReceiverAddr Varchar(30),
  IN ReceiverMode Integer, IN MsgState Integer, IN SdsType Integer,
  IN NumParam1 Fixed(10), IN NumParam2 Fixed(10), IN NumParam3 Fixed(10),
  IN NumParam4 Fixed(10), IN ByteParam Varchar(2084) BYTE, IN Alarm Integer) AS

  VAR
    logVoiceId    Integer;
    logSdsId      Integer;
    radioGroupId  Integer;
    color         Integer;
    isColor       Integer;
    intDist       Integer;
    intTime       Integer;
    errorCode     Integer;
    timeConnect   Timestamp;
    statusId      Integer;
    sdsText       Varchar(255);
    senderIssi    Fixed(30);
    receiverIssi  Fixed(30);
    endState      Integer;

    statusGetGroupMemberDmo Integer;
    statusGetGroupMemberTmo Integer;
    statusTalkGroupDec      Integer;
    statusTalkGroupInc      Integer;
    statusQuickGroup        Integer;
    statusSwitchToDmo       Integer;

BEGIN
  /* initialization */
  SET logVoiceId  = -1;
  SET logSdsId    = -1;
  SET statusId    = -1;
  SET sdsText     = '';
  SET endState    = 2; /* sent */

  SET senderIssi = TEXT2NUMERIC (SenderAddr);
  IF (senderIssi IS NULL) THEN
    SET senderIssi = -1;
  SET receiverIssi = TEXT2NUMERIC (ReceiverAddr);
  IF (receiverIssi IS NULL) THEN
    SET receiverIssi = -1;

  /* against deadlock */
  LOCK (WAIT) TABLE ADMIN.EVENTUSER IN EXCLUSIVE MODE;

  /* Find status parameters */
  DECLARE STATUSGETGROUPMEMBERDMO_CUR CURSOR FOR
    SELECT NUM (VALUE)
      FROM ADMIN.SYSINFO
      WHERE NAME = '_StatusGetGroupMemberDmo';
  FETCH STATUSGETGROUPMEMBERDMO_CUR INTO :statusGetGroupMemberDmo;
  CLOSE STATUSGETGROUPMEMBERDMO_CUR;
  DECLARE STATUSGETGROUPMEMBERTMO_CUR CURSOR FOR
    SELECT NUM (VALUE)
      FROM ADMIN.SYSINFO
      WHERE NAME = '_StatusGetGroupMemberTmo';
  FETCH STATUSGETGROUPMEMBERTMO_CUR INTO :statusGetGroupMemberTmo;
  CLOSE STATUSGETGROUPMEMBERTMO_CUR;
  DECLARE STATUSTALKGROUPINC_CUR CURSOR FOR
    SELECT NUM (VALUE)
      FROM ADMIN.SYSINFO
      WHERE NAME = '_StatusTalkGroupInc';
  FETCH STATUSTALKGROUPINC_CUR INTO :statusTalkGroupInc;
  CLOSE STATUSTALKGROUPINC_CUR;
  DECLARE STATUSTALKGROUPDEC_CUR CURSOR FOR
    SELECT NUM (VALUE)
      FROM ADMIN.SYSINFO
      WHERE NAME = '_StatusTalkGroupDec';
  FETCH STATUSTALKGROUPDEC_CUR INTO :statusTalkGroupDec;
  CLOSE STATUSTALKGROUPDEC_CUR;
  DECLARE STATUSSWITCHTODMO_CUR CURSOR FOR
    SELECT NUM (VALUE)
      FROM ADMIN.SYSINFO
      WHERE NAME = '_StatusSwitchToDmo';
  FETCH STATUSSWITCHTODMO_CUR INTO :statusSwitchToDmo;
  CLOSE STATUSSWITCHTODMO_CUR;

  /* find message color */
  DECLARE MSGCOLOR_CUR CURSOR FOR
    SELECT COLOR, ISCOLOR
      FROM ADMIN.MESSAGE WHERE ID = :MsgId;
  FETCH MSGCOLOR_CUR INTO :color, :isColor;
  IF ($rc = 100) THEN /* message not found */
  BEGIN
    SET color = 0;
    SET isColor = 0;
  END;
  CLOSE MSGCOLOR_CUR;

  /* Status Req */
  IF (MsgId = 3) THEN
  BEGIN
    /* -> GetGroupMembers TMO Req */
    IF (NumParam1 = statusGetGroupMemberTmo) THEN SET MsgId = 10;
    /* -> GetGroupMembers DMO Req */
    IF (NumParam1 = statusGetGroupMemberDmo) THEN SET MsgId = 11;
    /* -> GetGroupMembers DMO Req */
    IF (NumParam1 = statusSwitchToDmo) THEN SET MsgId = 12;
    /* -> Increment TalkGroup Req */
    IF (NumParam1 = statusTalkGroupInc) THEN SET MsgId = 31;
    /* -> Decrement TalkGroup Req */
    IF (NumParam1 = statusTalkGroupDec) THEN SET MsgId = 32;
  END;

  /* set request parameters */
  IF (receiverIssi <> -1) THEN
  BEGIN
    IF (MsgId = 1) THEN         /* Position Req */
    BEGIN
      INSERT ADMIN.RADIODETAIL
        SET ADDR = :receiverIssi, POSPM = 0
        UPDATE DUPLICATES;
    END
    ELSE IF (MsgId = 2) THEN    /* PerformanceManagement Req */
    BEGIN
      INSERT ADMIN.RADIODETAIL
        SET ADDR = :receiverIssi, POSPM = 1
        UPDATE DUPLICATES;
    END
    ELSE IF (MsgId = 3) THEN    /* Status Req */
    BEGIN
      SET statusId = NumParam1;
    END
    ELSE IF (MsgId = 4) THEN    /* Text Req */
    BEGIN
      SET sdsText = ASCII (ByteParam);
      INSERT ADMIN.RADIODETAIL
        SET ADDR = :receiverIssi, TEXTSDSFLAG = :Numparam1
        UPDATE DUPLICATES;
      IF (((NumParam1 DIV 4) MOD 2) = 1) THEN /* consume flag => bit2 */
        SET endState = 5  /* consume */
      ELSE IF (((NumParam1 DIV 2) MOD 2) = 1) THEN /* delivery flag => bit1 */
        SET endState = 3; /* delivery */
    END
    ELSE IF (MsgId IN (10, 11)) THEN    /* GetGroupMembers Req - DMO, TMO */
    BEGIN
      DECLARE RADIOGROUP_CUR CURSOR FOR
        SELECT ID FROM ADMIN.RADIOGROUP WHERE GSSI = :receiverIssi;
      FETCH RADIOGROUP_CUR INTO :radioGroupId;
      IF ($rc = 0) THEN
        UPDATE ADMIN.RADIO SET MEMBERGROUP_ID = -1
          WHERE MEMBERGROUP_ID = :radioGroupId;
      CLOSE RADIOGROUP_CUR;
      INSERT ADMIN.SYSINFO
        SET NAME = 'GroupMemberGssi', VALUE = :ReceiverAddr
        UPDATE DUPLICATES;

      IF (MsgId = 10) THEN  /* TMO */
        SET NumParam1 = statusGetGroupMemberTmo
      ELSE                  /* DMO */
        SET NumParam1 = statusGetGroupMemberDmo;
    END
    ELSE IF (MsgId = 12) THEN    /* Switch to DMO Req */
    BEGIN
      SET NumParam1 = statusSwitchToDmo;
    END
    ELSE IF (MsgId = 21) THEN    /* Set Events Req */
    BEGIN
      INSERT ADMIN.RADIODETAIL
        SET ADDR = :receiverIssi,
          INTERVALTIME = :NumParam2, INTERVALDIST = :NumParam1
        UPDATE DUPLICATES;
    END
    ELSE IF (MsgId = 31) THEN    /* Increment TalkGroup Req */
      SET NumParam1 = statusTalkGroupInc
    ELSE IF (MsgId = 32) THEN    /* Decrement TalkGroup Req */
      SET NumParam1 = statusTalkGroupDec
    ELSE IF (MsgId = 40) THEN    /* Switch QuickGroup Req */
    BEGIN
      DECLARE QUICKGROUPSTATUS_CUR CURSOR FOR
        SELECT NUM (VALUE)
          FROM ADMIN.SYSINFO
          WHERE NAME LIKE '_StatusQuickGroup' || CHR (:NumParam1);
      FETCH QUICKGROUPSTATUS_CUR INTO :statusQuickGroup;
      IF ($rc <> 0) THEN
        RETURN;
      CLOSE QUICKGROUPSTATUS_CUR;
      SET NumParam1 = statusQuickGroup;
    END
    ELSE IF (MsgId = 100) THEN    /* Accept alarm */
    BEGIN
      UPDATE ADMIN.RADIO SET ALARMSTATE = 0
        WHERE ISSI = :receiverIssi;
    END;
  END;

  IF (MsgId >= 300) THEN  /* voice call */
  BEGIN
    IF (MsgId IN (302, 303)) THEN /* group calls */
      SET timeConnect = Timestamp
    ELSE
      SET timeConnect = '1899-12-30 00:00:00';

    /* insert logfile - voice */
    INSERT ADMIN.LOGVOICE
      SET SENDERADDR = :SenderAddr, RECEIVERADDR = :ReceiverAddr,
        MESSAGE_ID = :MsgId, ALARM = :Alarm, APPUSER_ID = :UsrId,
        DATETIMENUM = :DateTimeNum, REFERENCE = :Reference,
        REFERENCESEQ = :ReferenceSeq, SERVER_ID = :ServerId,
        TIMECONNECT = :timeConnect;

    /* actual inserted log id */
    SET logVoiceId = ADMIN.LOGVOICE.CURRVAL;
  END
  ELSE                    /* SDS message */
  BEGIN
    /* insert logfile - SDS */
    INSERT ADMIN.LOGSDS
      SET SENDERADDR = :SenderAddr, RECEIVERADDR = :ReceiverAddr,
        MESSAGE_ID = :MsgId, ALARM = :Alarm, APPUSER_ID = :UsrId,
        REFERENCE = :Reference, SERVER_ID = :ServerId,
        STATUS_ID = :statusId, Text = :sdsText, ENDSTATE = :endState;

    /* actual inserted log id */
    SET logSdsId = ADMIN.LOGSDS.CURRVAL;

    IF (MsgId < 255) THEN /* msg is request */
    BEGIN
      /* code message bytes */
      CALL ADMIN.CODE_REQ (:logSdsId, :MsgId, :ServerId, :senderIssi,
        :receiverIssi, :ReceiverMode, :MsgState, :SdsType,
        :NumParam1, :NumParam2, :NumParam3, :NumParam4, :ByteParam, :errorCode);

      /* update logfile */
      UPDATE ADMIN.LOGSDS SET MSGSTATE = :errorCode WHERE ID = :logSdsId;
    END;
  END;

  /* update actual logfile, color (in Radio or RadioGroup) */
  UPDATE ADMIN.RADIO
    SET MESSAGECOLOR = DECODE (:isColor, 1, :color, MESSAGECOLOR),
      LOGVOICE_ID = :logVoiceId, LOGSDS_ID = :logSdsId
    WHERE ISSI = :receiverIssi OR ISSI = :senderIssi;
  UPDATE ADMIN.RADIOGROUP
    SET MESSAGECOLOR = DECODE (:isColor, 1, :color, MESSAGECOLOR),
      LOGVOICE_ID = :logVoiceId, LOGSDS_ID = :logSdsId
    WHERE GSSI = :receiverIssi OR GSSI = :senderIssi;
END;
//
CREATE TRIGGER LOGVOICE_INSERT FOR LOGVOICE AFTER INSERT EXECUTE
(

  VAR
    parentId        Integer;
    imageId         Integer;
    issiTxt         Varchar(256);
    senderIssi      Integer;
    posDescr        Varchar(800);
    senderId        Integer;
    senderName      Varchar(255);
    receiverGroupId Integer;
    receiverRadioId Integer;
    receiverName    Varchar(255);
    descr           Varchar(255);

  /* if trigger disabled => exit */
  SELECT INSERTTRG FROM ADMIN.EVENTTABLE WHERE ID = 19 AND INSERTTRG = 0;
  IF ($rc <> 100) THEN
    RETURN;

  /* fill logfile */
  DECLARE POSDESCR_CUR CURSOR FOR
    SELECT APPUSER.NAME
      FROM ADMIN.APPUSER
      WHERE APPUSER.ID = :NEW.APPUSER_ID;
  FETCH POSDESCR_CUR INTO :posDescr;
  IF ($rc <> 0) THEN
    SET posDescr = '';

  DECLARE SENDER_CUR CURSOR FOR
    SELECT ID, NAME
      FROM ADMIN.RADIO
      WHERE CHR (RADIO.ISSI) = :NEW.SENDERADDR;
  FETCH SENDER_CUR INTO :senderId, :senderName;
  IF ($rc <> 0) THEN
  BEGIN
    SET senderId = -1;
    SET senderName = NEW.SENDERADDR;
  END;

  DECLARE RECEIVER_CUR CURSOR FOR
    SELECT GROUP_ID, RADIO_ID, NAME
      FROM ADMIN.TETRAOBJECT
      WHERE CHR (TETRAOBJECT.SSI) = :NEW.RECEIVERADDR;
  FETCH RECEIVER_CUR INTO :receiverGroupId, :receiverRadioId, :receiverName;
  IF ($rc <> 0) THEN
  BEGIN
    SET receiverGroupId = -1;
    SET receiverRadioId = -1;
    SET receiverName = NEW.RECEIVERADDR;
  END;
  
  DECLARE DESCR_CUR CURSOR FOR
    SELECT TEXT
      FROM ADMIN.MESSAGE
      WHERE MESSAGE.ID = :NEW.MESSAGE_ID;
  FETCH DESCR_CUR INTO :descr;
  IF ($rc <> 0) THEN
    SET descr = '';

  INSERT ADMIN.LOGFILE
    SET ID = :NEW.ID, ABSID = :NEW.ID,
      TSTAMP = :NEW.TSTAMP, LON = -1, LAT = -1,
      MESSAGE_ID = :NEW.MESSAGE_ID, SENDERRADIO_ID = :senderId,
      RECEIVERRADIOGROUP_ID = :receiverGroupId,
      RECEIVERRADIO_ID = :receiverRadioId,
      SENDERNAME = :senderName, RECEIVERNAME = :receiverName,
      DESCR = :descr, MSGSTATE = :NEW.MSGSTATE, APPUSER_ID = :NEW.APPUSER_ID,
      POSDESCR = :posDescr, ALARM = :NEW.ALARM, SENDERADDR = :NEW.SENDERADDR,
      RECEIVERADDR = :NEW.RECEIVERADDR, SENDTSTAMP = '1899-12-30 00:00:00',
      DELIVERYTSTAMP = '1899-12-30 00:00:00', 
      CONSUMETSTAMP = '1899-12-30 00:00:00';

  /* generate user events LogVoice */
  CALL ADMIN.GENERATE_EVENTS (19, :NEW.ID, 1, 0, -1);

  /* add new radio */
  IF (NEW.MESSAGE_ID IN (300, 302)) THEN  /* incomming voice call */
  BEGIN
    /* convert addresses */
    SET senderIssi = TEXT2NUMERIC (NEW.SENDERADDR);
    IF (senderIssi IS NULL) THEN
      SET senderIssi = -1;

    SELECT ID FROM ADMIN.RADIO WHERE ISSI = :senderIssi;
    IF ($rc = 100) THEN
    BEGIN
      /* find radio parent */
      DECLARE PARENTGROUP_CUR CURSOR FOR
        SELECT ID FROM ADMIN.RADIOGROUP WHERE GSSI IN (-4, -5)
        ORDER BY GSSI ASC;
      FETCH FIRST PARENTGROUP_CUR INTO :parentId;
      IF ($rc <> 0) THEN
        SET parentId = -1;
      CLOSE PARENTGROUP_CUR;

      /* find radio image */
      DECLARE RADIOIMAGE_CUR CURSOR FOR
        SELECT ID FROM ADMIN.IMAGE WHERE IMGTYPE = 1;
      FETCH RADIOIMAGE_CUR INTO :imageId;
      IF ($rc <> 0) THEN
        SET imageId = -1;
      CLOSE RADIOIMAGE_CUR;

      SET issiTxt = CHR (NEW.SENDERADDR);

      INSERT ADMIN.RADIO
        SET CREATENAME = 'Tetra', CREATETSTAMP = Timestamp, IMAGE_ID = :imageId,
          RADIOGROUP_ID = :parentId, NAME = :issiTxt, ISSI = :senderIssi;
    END;
  END;

  /* set alarm state */
  IF (NEW.ALARM = 1) THEN /* message state = alarm */
    UPDATE ADMIN.RADIO SET ALARMSTATE = DECODE (:NEW.MESSAGE_ID, 300, 2, 3)
      WHERE ISSI = :senderIssi;
) 
//
CREATE TRIGGER LOGFILE_INSERT FOR LOGFILE AFTER INSERT EXECUTE
(
  /* if trigger disabled => exit */
  SELECT INSERTTRG FROM ADMIN.EVENTTABLE WHERE ID = 18 AND INSERTTRG = 0;
  IF ($rc <> 100) THEN
    RETURN;

  /* generate user events Logfile */
  CALL ADMIN.GENERATE_EVENTS (18, :NEW.ID, 1, 0, -1);
) 
//
CREATE DBPROC GENERATE_EVENTS (IN TableId Integer, IN ObjId Integer, 
  IN ActionId Integer, IN ColChng Fixed(38), IN ConnTypeId Integer) AS

  VAR 
    strSet     Varchar(100);
    byteSet    Varchar(100) BYTE;
    stmt       Varchar(300);
    sessionId  Integer;
    id         Integer;
    chng       Fixed(38);

BEGIN
  /* if new event is insert or delete then remove old row events */
  IF (ActionId IN (1, 3)) THEN
    DELETE ADMIN.EVENTUSER WHERE EVENTTABLE_ID = :TableId AND OBJID = :ObjId;
  /* if new event is reread all then remove old table events */
  IF (ActionId = 4) THEN
    DELETE ADMIN.EVENTUSER WHERE EVENTTABLE_ID = :TableId;

  /* find connected users */
  IF ActionId = 5 THEN             /* event = user restart */
  BEGIN
    DECLARE GENSESSION_CUR CURSOR FOR
      SELECT ID
        FROM ADMIN.APPUSERSESSION
        WHERE APPUSER_ID = :ObjId AND CONNTYPE_ID = :conntypeId;
  END
  ELSE IF ActionId IN (1,2,3) THEN /* event = insert, update, delete */
  BEGIN
    DECLARE GENSESSION_CUR CURSOR FOR
      SELECT ID
        FROM ADMIN.APPUSERSESSION
        WHERE (APPUSER_ID, CONNTYPE_ID, :TableId) =
          ANY (SELECT APPUSER_ID, CONNTYPE_ID, EVENTTABLE_ID FROM ADMIN.EVENTSTATEMENT);
  END
  ELSE
    DECLARE GENSESSION_CUR CURSOR FOR
      SELECT ID
        FROM ADMIN.APPUSERSESSION;

  /* generate */
  WHILE $rc = 0 DO
  BEGIN
    FETCH GENSESSION_CUR INTO :sessionId;
    IF $rc = 0 THEN
    BEGIN
      IF (ActionId = 2) THEN   /* update action */
      BEGIN
        /* if there is insert event continue */
        SELECT ID FROM ADMIN.EVENTUSER
          WHERE EVENTTABLE_ID = :TableId AND OBJID = :ObjId
            AND ACTIONID = 1 AND APPUSERSESSION_ID = :sessionId;
        IF ($rc <> 100) THEN
          CONTINUE;

        /* if there is update event correct it */
        SELECT ID, COLUMNSCHNGNUM INTO :id, :chng FROM ADMIN.EVENTUSER
          WHERE EVENTTABLE_ID = :TableId AND OBJID = :ObjId
            AND ACTIONID = 2 AND APPUSERSESSION_ID = :sessionId;
        IF ($rc = 0) THEN
        BEGIN
          SET ColChng = BITWISE_OR (chng, ColChng);
          CALL ADMIN.NUMERIC2SETHEX (:ColChng, :strSet);
          SET byteSet = HEXTORAW (strSet);
          UPDATE ADMIN.EVENTUSER
            SET COLUMNSCHNG = :byteSet, COLUMNSCHNGNUM = :ColChng
            WHERE ID = :id;
          CONTINUE;
        END;
      END;

      /* insert new event */
      CALL ADMIN.NUMERIC2SETHEX (:ColChng, :strSet);
      SET byteSet = HEXTORAW (strSet);
      INSERT ADMIN.EVENTUSER
        SET EVENTTABLE_ID = :TableId , OBJID = :ObjId, ACTIONID = :ActionId,
          COLUMNSCHNG = :byteSet, COLUMNSCHNGNUM  = :ColChng,
          APPUSERSESSION_ID = :sessionId;
    END;
  END;
  CLOSE GENSESSION_CUR;
END;
//

process (PID=3720):

Call GET_EVENT (107)
//
CREATE DBPROC GET_EVENT (IN SessionId Integer)
  RETURNS CURSOR AS

  VAR 
    stmt    Varchar(7000);
    tmp     Varchar(2000);
    id      Integer;
    tableId Integer;
    objId   Integer;
    actId   Integer;
    i       Integer;

BEGIN
  /* initialization */
  SET i   = 0;
  SET tmp = '';

  /* keep connection */
  UPDATE ADMIN.APPUSERSESSION SET TSTAMP = Timestamp WHERE ID = :SessionId;

  /* create result cursor */
  $CURSOR = 'GETEVENT_CURSOR';

  /* find oldest event */
  DECLARE EVENT_CUR CURSOR FOR
    SELECT ID, EVENTTABLE_ID, OBJID, ACTIONID
      FROM ADMIN.EVENTUSER
      WHERE APPUSERSESSION_ID = :SessionId AND ROWNO < 2;
  FETCH FIRST EVENT_CUR INTO :id, :tableId, :objId, :actId;
  IF ($rc <> 0) THEN
  BEGIN
    DECLARE :$CURSOR CURSOR FOR
      SELECT EVENTTABLE_ID, OBJID, ACTIONID, COLUMNSCHNG
        FROM ADMIN.EVENTUSER WHERE ID = -1;
    RETURN;
  END;
  CLOSE EVENT_CUR;

  IF (actId IN (1, 2)) THEN     /* insert, update event */
  BEGIN
    /* find events from same table */
    DECLARE MOREEVENT_CUR CURSOR FOR
      SELECT OBJID
        FROM ADMIN.EVENTUSER
        WHERE APPUSERSESSION_ID = :SessionId AND EVENTTABLE_ID = :tableId 
          AND ACTIONID IN (1, 2);
    WHILE  (i < 10) DO
    BEGIN
      SET i = i + 1;
      FETCH MOREEVENT_CUR INTO :id;
      IF ($rc <> 0)THEN
        BREAK;
      SET tmp = tmp || CHR (id) || ', ';
    END;
    CLOSE MOREEVENT_CUR;
    SET tmp = '(' || RTRIM (tmp, ', ') || ')' ||
              ' AND EVENTUSER.APPUSERSESSION_ID = ' || SessionId ||
              ' AND EVENTUSER.EVENTTABLE_ID = ' || tableId || ' ';

    /* find event statement */
    DECLARE USERSTATEMENT_CUR CURSOR FOR
      SELECT STATEMENT FROM ADMIN.EVENTSTATEMENT
        WHERE APPUSERSESSION_ID = :SessionId AND EVENTTABLE_ID = :tableId;
    FETCH FIRST USERSTATEMENT_CUR INTO :stmt;
    IF ($rc = 100) THEN
    BEGIN   /* no statement found */
      DECLARE :$CURSOR CURSOR FOR 
        SELECT EVENTTABLE_ID, ID, ACTIONID
          FROM ADMIN.EVENTUSER WHERE ID = -1;
    END
    ELSE
    BEGIN
      SET stmt = REPLACE (stmt, '*Id*', tmp);
      SET stmt = 'DECLARE ' || $CURSOR || ' CURSOR FOR ' || stmt;
      
      EXECUTE stmt;
    END;
    CLOSE USERSTATEMENT_CUR;

    /* delete from eventuser */
    SET stmt = 'DELETE ADMIN.EVENTUSER ' ||
                 'WHERE OBJID IN ' || tmp ||
                   ' AND APPUSERSESSION_ID = ' || SessionId ||
                   ' AND EVENTTABLE_ID = ' || tableId;
    EXECUTE stmt;
  END
  ELSE         /* delete event */
  BEGIN
    DECLARE DELETE_CUR CURSOR FOR
      SELECT ID, :tableId AS EVENTTABLE_ID, OBJID, :actId AS ACTIONID
        FROM ADMIN.EVENTUSER
        WHERE EVENTTABLE_ID = :tableId AND ACTIONID = :actId
          AND APPUSERSESSION_ID = :SessionId
        FOR REUSE;
    DECLARE :$CURSOR CURSOR FOR
      SELECT EVENTTABLE_ID, OBJID AS ID, ACTIONID
        FROM ADMIN.DELETE_CUR;

    /* delete from eventuser */
    DELETE ADMIN.EVENTUSER WHERE ID IN
      (SELECT ID FROM ADMIN.DELETE_CUR);
    CLOSE DELETE_CUR;
  END;
END;
//

I know a lot of code but I hope you can imagine what I want to do.

Regards

Dusan

[lbreddemann]

Hi Dusan,

the reason for that you did not get an answer immediately is:

1) with the information given, the problem cannot be reproduced.

Where are the CREATE TABLE statements? Where are the Statements to fill the data into the tables (or does the deadlock occur also on empty tables?)

2) everybody in this forum writes back when time is available and an answer is possible. Nobody gets paid for this - so bring a little patience...

So: give me something I can just put into DB Studio and reproduce the error and I will have a look.

KR Lars