- Hi, all. -

- I have two tables (COMMANDS and IDS_VMS) in my RDB 7.1 database. -

- When I insert a row in COMMANDS, a trigger raises, invoking a DCL
command (via LIBSPAWN). -
- This DCL gets a list of identifiers and inserts them in the table
IDS_VMS. -

- My problem is: -

* When I insert a new row in COMMANDS, the SQL which inserts the row
in IDS_VMS gets locked
and returns the error: -

%RDB-E-LOCK_CONFLICT, request failed due to locked resource

- I have tested the DCL in the command line and it works well. -

- I attach sql and dcl code. -

- I have tried to set transactions which reserve only the needed
tables, but it fails. -

- Any help will be appreciated. -

- Thanks in advance. -

Javi "Desperado"


============================
Definition of table COMMANDS
============================

Columns for table COMMANDS:
Column Name Data Type Domain
----------- --------- ------
COMMAND_TYPE CHAR(1)
USERNAME CHAR(12)
RETVAL INTEGER

================================
Definition of trigger NEWCOMMAND
================================

Note: This trigger invokes getIdentifiers.com via LIBSPAWN, with the
username as a parameter.


NEWCOMMAND AFTER INSERT ON COMMANDS
WHEN COMMANDS.COMMAND_TYPE IN ('I')
(
UPDATE COMMANDS O SET RETVAL=
LIB_SPAWN('@DGA3:[USUARIOS.PAS.SCIDES21.BBDD]getIdentifiers '
WHERE COMMANDS.DBKEY=O.DBKEY AND COMMANDS.COMMAND_TYPE='I';
)
FOR EACH ROW

============================
Definition of table IDS_VMS
============================

Columns for table IDS_VMS:
Column Name Data Type Domain
----------- --------- ------
ID_TYPE CHAR(1)
USERNAME CHAR(12)
IDS_LIST VARCHAR(1024)


============================
Definition of DCL Command
============================

Note: This is the DCL getIdentifiers.com. It's invoked each time a row
is inserted in COMMANDS
with COMMAND_TYPE='I'
I have tested this DCL via command line and it works perfectly. The
generated SQL files are OK.
Furthermore, the DCL command is invoked with correct parameters from
the trigger via LIBSPAWN

$!
$! getIdentifiers.com
$!
$! The DCL creates an SQL file which deletes info in table IDS_VMS
$!
$! ************************************************** *************
$! Also I have tried to include the following line after attach
sentence
$! SET TRANSACTION READ WRITE RESERVING IDS_VMS FOR SHARED WRITE
$! And also I have tried this one:
$! SET TRANSACTION READ WRITE ISOLATION LEVEL READ COMMITTED RESERVING
IDS_VMS FOR SHARED WRITE
$!
$ open/write fdelete global_delete.sql
$ write fdelete "attach 'filename admin_admin.rdb';"
$ write fdelete "DELETE FROM IDS_VMS WHERE TIPO_ID='G';"
$ write fdelete "COMMIT;"
$ write fdelete "EXIT;"
$ close fdelete
$ sql @global_delete.sql
$!
$! Then the DCL creates an SQL file which inserts a row in table
IDS_VMS
$!
$ globalIds="IdentG1|IdentG2|IdentG3|IdentG4"
$ open/write finsert global_insert.sql
$ write finsert "attach 'filename admin_admin.rdb';"
$ write finsert "INSERT INTO IDS_VMS
VALUES('G','GLOBAL','"'globalIds'"');"
$ write finsert "COMMIT;"
$ write finsert "EXIT;"
$ close finsert
$ sql @global_insert.sql
$ EXIT

===============================
To test my database
===============================

SQL> attach 'filename admin_admin.rdb';
SQL> insert into COMMANDS (COMMAND_TYPE,USERNAME) values
('I','Javier');

....
The DCL command is executed and the first SQL file executed via DCL
lock.
....

I have tried to lock only the tables I need, before the insert command

SQL> SET TRANSACTION READ WRITE RESERVING COMMANDS FOR SHARED WRITE
SQL> SET TRANSACTION READ WRITE ISOLATION LEVEL READ COMMITTED
RESERVING COMMANDS FOR SHARED WRITE