Explicit UNLOCK in procedure / trigger

[Former Member]

Hello,

how can I explicitly unlock my lock in stored procedure/trigger ?

When I try:

DROP DBPROC TEST_TMP
//
CREATE DBPROC TEST_TMP AS
BEGIN
  LOCK (WAIT) TABLE ADMIN.MOBILE
    IN EXCLUSIVE MODE;
  UNLOCK TABLE ADMIN.MOBILE
    IN EXCLUSIVE MODE;
END;
I obtain an error message:

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed Syntax error or access violation;-5015 POS(87) Missing keyword:LOCK,WHILE,UPDATE,SELECT,INSERT,IF,EXECUTE,DELETE,CLO ...

What am I doing incorrect ?

What is the correct syntax ?

Thank you, Dusan

[Former Member]

Hi,

you surely know, that deadlock-prevention can be done if every transaction uses a set of tables in the same sequence and not one uses A and then B and the second B and then A.

May I ask which kind of deadlocks you have, meaning: if a deadlock occurs, I would like to see the result of systemview lock_waits.

Then the guy handling locks could check.

Elke

[Former Member]

Hello Elke,

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

[lbreddemann]

Hi Dusan,

please provide all DDL /DML Statements to reproduce the deadlock.

From the description it's not visible to me why a deadlock should occur here nor why it should be necessary to lock whole tables.

BTW:

Using triggers is not the best choice when you want to keep your application maintainable.

Whenever you do something on your database - something "magical" happens and you cannot easily explain why.

KR Lars

[Former Member]

Hi,

the reference manual tells us that no unlock table is possible at any place:

Syntax

<unlock_statement> ::=

UNLOCK <row_spec> ... IN SHARE MODE

| UNLOCK <row_spec> ... IN EXCLUSIVE MODE

| UNLOCK <row_spec> ... OPTIMISTIC

<row_spec> ::=

ROW <table_name> KEY <key_spec>,...

| ROW <table_name> CURRENT OF <result_table_name>

Do you really want to lock table(s) exclusively and cause no parallel access to this/these table(s) during your dbproc? Not even read-access?

That seems to be a hard restriction.

And did you check if one of the isolation levels available will do what you want?

Elke

[Former Member]

Hello Elke,

I'm using MaxDb 7.6.00.16 on Win2003 (32bit).

Yes you are right, may be we need SHARE LOCK instead of EXCLUSIVE. EXCLUSIVE locks slow down database performance, but i'm not sure that SHARE LOCK will solve all my problems.

We need some explicit locks because the database has deadlocks (sometimes). There is some DB parameter DEADLOCK_DETECTION but it seams that does not work (when I set it to 100, the database did not detected simple deadlock). Is there any other solution for deadlock prvention ?

Thank you for yuor response.

Dusan