[
https://issues.apache.org/jira/browse/DERBY-7042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16811617#comment-16811617
]
Rick Hillegas commented on DERBY-7042:
--------------------------------------
I am trying to understand how to trip this bug, but so far I have not been able
to make it occur. I have extracted the following script from your repro and I
have run the script against both the development trunk and the latest 10.15.1.3
release. It runs without error for me. Could you take a look at this script and
suggest how to make it demonstrate the problem you are seeing?
Thanks,
-Rick
Here is the script:
{noformat}
connect 'jdbc:derby:memory:db1;create=true';
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.rowLocking',
'false');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.deadlockTimeout', '5');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.waitTimeout', '5');
-- create schema
CREATE TABLE ZooAnimals
(
Name VARCHAR(255),
Amount INT,
Feed BOOLEAN,
Zoo VARCHAR(255)
)
;
CREATE PROCEDURE helloWorld()
LANGUAGE JAVA
EXTERNAL NAME 'Derby7042.helloWorld'
PARAMETER STYLE JAVA
NO SQL
;
CREATE TRIGGER ZooUpdateTrigger
AFTER UPDATE ON ZOOANIMALS FOR EACH STATEMENT MODE DB2SQL
CALL helloWorld()
;
CREATE TRIGGER ZooInsertTrigger
AFTER INSERT ON ZOOANIMALS FOR EACH STATEMENT MODE DB2SQL
CALL helloWorld()
;
CREATE TRIGGER ZooDeleteTrigger
AFTER DELETE ON ZOOANIMALS FOR EACH STATEMENT MODE DB2SQL
CALL helloWorld()
;
-- populate table
INSERT INTO ZOOANIMALS (Name, Amount, Feed, Zoo) VALUES ('Gorilla', 1, true,
'Cincinnati');
SELECT * FROM ZooAnimals;
-- reboot just in case the documentation lies about the properties being dynamic
connect 'jdbc:derby:memory:db1;shutdown=true';
connect 'jdbc:derby:memory:db1';
-- update table
UPDATE ZooAnimals SET Name = 'Aardvark' WHERE Zoo = 'Cincinnati';
SELECT * FROM ZooAnimals;
{noformat}
Here is the output of the script when run against 10.15.1.3:
{noformat}
ij version 10.15
ij> connect 'jdbc:derby:memory:db1;create=true';
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.rowLocking',
'false');
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.deadlockTimeout',
'5');
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.waitTimeout', '5');
0 rows inserted/updated/deleted
ij> -- create schema
CREATE TABLE ZooAnimals
(
Name VARCHAR(255),
Amount INT,
Feed BOOLEAN,
Zoo VARCHAR(255)
)
;
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE helloWorld()
LANGUAGE JAVA
EXTERNAL NAME 'Derby7042.helloWorld'
PARAMETER STYLE JAVA
NO SQL
;
0 rows inserted/updated/deleted
ij> CREATE TRIGGER ZooUpdateTrigger
AFTER UPDATE ON ZOOANIMALS FOR EACH STATEMENT MODE DB2SQL
CALL helloWorld()
;
0 rows inserted/updated/deleted
ij> CREATE TRIGGER ZooInsertTrigger
AFTER INSERT ON ZOOANIMALS FOR EACH STATEMENT MODE DB2SQL
CALL helloWorld()
;
0 rows inserted/updated/deleted
ij> CREATE TRIGGER ZooDeleteTrigger
AFTER DELETE ON ZOOANIMALS FOR EACH STATEMENT MODE DB2SQL
CALL helloWorld()
;
0 rows inserted/updated/deleted
ij> -- populate table
INSERT INTO ZOOANIMALS (Name, Amount, Feed, Zoo) VALUES ('Gorilla', 1, true,
'Cincinnati');
Hello World
1 row inserted/updated/deleted
ij> SELECT * FROM ZooAnimals;
NAME
|AMOUNT |FEED |ZOO
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gorilla
|1 |true |Cincinnati
1 row selected
ij> -- reboot just in case the documentation lies about the properties being
dynamic
connect 'jdbc:derby:memory:db1;shutdown=true';
ERROR 08006: Database 'memory:db1' shutdown.
ij> connect 'jdbc:derby:memory:db1';
ij(CONNECTION1)> -- update table
UPDATE ZooAnimals SET Name = 'Aardvark' WHERE Zoo = 'Cincinnati';
Hello World
1 row inserted/updated/deleted
ij(CONNECTION1)> SELECT * FROM ZooAnimals;
NAME
|AMOUNT |FEED |ZOO
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aardvark
|1 |true |Cincinnati
1 row selected
{noformat}
> Multiple triggers with rowLocking = false causes deadlock
> ---------------------------------------------------------
>
> Key: DERBY-7042
> URL: https://issues.apache.org/jira/browse/DERBY-7042
> Project: Derby
> Issue Type: Bug
> Components: Documentation, JDBC
> Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1, 10.10.2.0,
> 10.11.1.1, 10.12.1.1, 10.13.1.1, 10.14.1.0, 10.14.2.0, 10.15.1.3
> Environment: ------------------ Java Information ------------------
> Java Version: 1.8.0_201
> Java Vendor: Oracle Corporation
> OS name: Linux
> OS architecture: i386
> OS version: 4.15.0-46-generic
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.8
> java.runtime.version: 1.8.0_201-b09
> --------- Derby Information --------
> [.../TriggerRepro/derby/derby-10.14.1.0.jar] 10.14.1.0 - (1808820)
> Reporter: Michael Schuetze
> Priority: Minor
> Labels: documentation, performance
> Attachments: Derby7042.java
>
>
> Repro for the bug can be found here:
> [https://github.com/mjschuetze102/TriggerRepro]
> Includes a detailed README of steps that show effect of bug as well as two
> versions of the Derby database. 10.8.1.2, the last version where the bug was
> not present and version 10.14.1.0, for easy access.
> This may just be an issue of not having enough documentation on database
> Triggers (see Conclusions Based on Results)
> h2. Summary of Issue
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> # Executing Update trigger causes deadlock when there is an Insert or Delete
> trigger
> # Executing Insert trigger causes deadlock when there is a Delete trigger
> # Executing Delete trigger does not cause deadlock
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> # While executing issue above, none of the triggers were terminated and
> waitTimeout time was hit
> h2. Conclusions Based on Results
> *Having multiple triggers with {{'derby.storage.rowLocking', 'false'}} causes
> issues with deadlocking*
> Triggers seem to get into deadlock scenarios with any trigger defined after
> itself. If this is the case, it should be documented somewhere that
> rowLocking needs to be enabled to use the trigger feature if multiple
> triggers would be used on the same database table.
> *{{'derby.locks.deadlockTimeout'}} does not seem to work in above case*
> Based on documentation, I could not find any concrete evidence of whether
> this is intended functionality.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
