Re: Help needed to understand deadlock
On 12/02/2024 20:31, John English wrote: On 12/02/2024 20:02, Rick Hillegas wrote: With that change, the script fails further on with the creation of the registrations table: Argh. The owner of this DB asked me to strip it down rather than publish it in full. Apologies. In the end I decided to bite the bullet and merge active_list and current_list into a single table (which wasn't as hard as I had feared). In the process I realised that the trigger which seems to be the cause of the deadlock is not actually needed... the situation that it was created to deal with was fixed about five years ago! So dropping the trigger would seem to solve the problem. It's amazing how much crap piles up in old DB schemas -- this one is close to 20 years old and appears to have been bashed around a lot in the past by people with quite different ideas of how to do things. And now it's my turn to bash it some more. -- John English
Re: Help needed to understand deadlock
On 12/02/2024 20:02, Rick Hillegas wrote: With that change, the script fails further on with the creation of the registrations table: Argh. The owner of this DB asked me to strip it down rather than publish it in full. Apologies. -- John English
Re: Help needed to understand deadlock
With that change, the script fails further on with the creation of the registrations table: ERROR X0Y46: Constraint 'REGISTRNS_1' is invalid: referenced table USERS does not exist. On 2/11/24 3:48 AM, John English wrote: Either delete the constraint, or add "studentid integer" to the table definition. On Sat, 10 Feb 2024, 23:13 Rick Hillegas, wrote: I get the following error when I run this DDL: ERROR 42X93: Table 'ACTIVE_LIST' contains a constraint definition with column 'STUDENTID' which is not in the table. On 2/10/24 6:03 AM, John English wrote: CREATE TABLE active_list ( usernameVARCHAR(15) NOT NULL, surname VARCHAR(255) NOT NULL, initialsVARCHAR(255) NOT NULL DEFAULT '', email VARCHAR(255) NOT NULL DEFAULT '', settingsCLOB, CONSTRAINT active_pk PRIMARY KEY (username), CONSTRAINT active_1 UNIQUE (studentid) )
Re: Help needed to understand deadlock
Either delete the constraint, or add "studentid integer" to the table definition. On Sat, 10 Feb 2024, 23:13 Rick Hillegas, wrote: > I get the following error when I run this DDL: > > ERROR 42X93: Table 'ACTIVE_LIST' contains a constraint definition with > column 'STUDENTID' which is not in the table. > > > On 2/10/24 6:03 AM, John English wrote: > > CREATE TABLE active_list ( > > usernameVARCHAR(15) NOT NULL, > > surname VARCHAR(255) NOT NULL, > > initialsVARCHAR(255) NOT NULL DEFAULT '', > > email VARCHAR(255) NOT NULL DEFAULT '', > > settingsCLOB, > > CONSTRAINT active_pk PRIMARY KEY (username), > > CONSTRAINT active_1 UNIQUE (studentid) > > ) > > >
Re: Help needed to understand deadlock
I get the following error when I run this DDL: ERROR 42X93: Table 'ACTIVE_LIST' contains a constraint definition with column 'STUDENTID' which is not in the table. On 2/10/24 6:03 AM, John English wrote: CREATE TABLE active_list ( username VARCHAR(15) NOT NULL, surname VARCHAR(255) NOT NULL, initials VARCHAR(255) NOT NULL DEFAULT '', email VARCHAR(255) NOT NULL DEFAULT '', settings CLOB, CONSTRAINT active_pk PRIMARY KEY (username), CONSTRAINT active_1 UNIQUE (studentid) )
Re: Help needed to understand deadlock
On 05/02/2024 20:11, Rick Hillegas wrote:
I would need to see your full schema before speculating about why these
statements grab these locks.
Here are the relevant tables. One thing that strikes me as odd is the
separation of active_list and current_list -- probably there was a
reason for this in the dim distant past (or maybe not!) -- and I wonder
if merging the two would make any difference? (If not, it's something
I'd prefer not to try and do -- it would probably require a number of
code changes, and "if it ain't broke, don't fix it".)
CREATE TABLE active_list (
usernameVARCHAR(15) NOT NULL,
surname VARCHAR(255) NOT NULL,
initialsVARCHAR(255) NOT NULL DEFAULT '',
email VARCHAR(255) NOT NULL DEFAULT '',
settingsCLOB,
CONSTRAINT active_pk PRIMARY KEY (username),
CONSTRAINT active_1 UNIQUE (studentid)
);
CREATE TABLE current_list (
usernameVARCHAR(15) NOT NULL,
properties CLOB NOT NULL,
CONSTRAINT current_pk PRIMARY KEY (username),
CONSTRAINT current_1 FOREIGN KEY (username)
REFERENCES active_list(username)
ON DELETE CASCADE
);
CREATE TABLE activities (
activityVARCHAR(15) NOT NULL,
title VARCHAR(255) NOT NULL,
selfreg CHAR(1) DEFAULT NULL,
CONSTRAINT activities_pk PRIMARY KEY (activity)
);
CREATE TABLE registrations (
usernameVARCHAR(15) NOT NULL,
activityVARCHAR(15) NOT NULL,
roleSMALLINT DEFAULT 0,
CONSTRAINT registrns_pk PRIMARY KEY (username,activity),
CONSTRAINT registrns_1FOREIGN KEY (username)
REFERENCES users(username)
ON DELETE CASCADE,
CONSTRAINT registrns_2FOREIGN KEY (activity)
REFERENCES activities(activity)
ON DELETE CASCADE
);
CREATE TABLE actions (
id INTEGER GENERATED ALWAYS AS IDENTITY,
activityVARCHAR(15) NOT NULL,
title VARCHAR(255) NOT NULL,
durationINTEGER NOT NULL DEFAULT 0,
actionxml CLOB NOT NULL,
author VARCHAR(15) NOT NULL,
timeTIMESTAMP NOT NULL,
CONSTRAINT actions_pk PRIMARY KEY (id),
CONSTRAINT actions_1 UNIQUE (activity, title),
CONSTRAINT actions_2 FOREIGN KEY (activity)
REFERENCES activities(activity)
ON DELETE CASCADE
);
CREATE TABLE action_log (
id INTEGER GENERATED ALWAYS AS IDENTITY,
action INTEGER NOT NULL,
usernameVARCHAR(15) NOT NULL,
timeTIMESTAMP DEFAULT NULL,
started TIMESTAMP DEFAULT NULL,
endtime TIMESTAMP DEFAULT NULL,
status CHAR(1),
content CLOB DEFAULT NULL,
CONSTRAINT actionlog_pk PRIMARY KEY (id),
CONSTRAINT actionlog_1UNIQUE (action,username),
CONSTRAINT actionlog_2FOREIGN KEY (username)
REFERENCES active_list(username)
ON DELETE CASCADE,
CONSTRAINT actionlog_3FOREIGN KEY (action)
REFERENCES actions(id)
ON DELETE CASCADE,
CONSTRAINT actionlog_4CHECK (status IN
('A','B','E','F','N','P','Q','S'))
);
--
John English
Re: Help needed to understand deadlock
I would need to see your full schema before speculating about why these
statements grab these locks.
On 2/2/24 11:46 AM, John English wrote:
My system recently reported the following:
java.sql.SQLTransactionRollbackException: A lock could not be obtained
due to a deadlock, cycle of locks and waiters is:
Lock : ROW, ACTION_LOG, (2,4800)
Waiting XID : {255785240, U} , APP, SELECT * FROM action_log WHERE
status='A' AND endtime>=CURRENT_TIMESTAMP FOR UPDATE
Granted XID : {255785232, U}
Lock : TABLE, REGISTRATIONS, Tablelock
Waiting XID : {255785232, IS} , APP, SELECT * FROM registrations
WHERE activity=?
Granted XID : {255785263, IX}
Lock : TABLE, ACTION_LOG, Tablelock
Waiting XID : {255785263, X} , APP, DELETE FROM active_list
WHERE username=CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(1)
AS VARCHAR(15))
AND username NOT IN (SELECT username FROM current_list)
Granted XID : {255785232, IX} , {255785233, IX} , {255785236, IX} ,
{255785240, IX} , {255785247, IX} , {255785249, IX}
Lock : ROW, ACTION_LOG, (2,4800)
Waiting XID : {255785249, U} , APP, SELECT * FROM action_log WHERE
username=? AND action=? AND seed=? FOR UPDATE
. The selected victim is XID : 255785240.
Users can register to carry out particular activities, and there is an
action log which is updated about once a minute while an activity is
in progress. The deadlock happened when I tried to delete an activity
and the associated registrations.
The "DELETE FROM active_list" is fired by the following trigger:
CREATE TRIGGER delete_user
AFTER DELETE ON registrations
REFERENCING OLD AS del
FOR EACH ROW MODE DB2SQL
DELETE FROM active_list
WHERE username=del.username
AND username NOT IN (SELECT username FROM current_list)
I am trying to understand why there is a table lock on the action log
(updating this is much more important and urgent than purging inactive
users!). I am not clear what U, X, IS and IX are -- uodate, exclusive,
shared something, exclusive something?
And of course I'd like to prevent it happening again. Would changing
the isolation level for one of the queries perhaps help? Any ideas
gratefully received!
Thanks,
Re: Help needed to understand deadlock
On 04/02/2024 00:40, Rick Hillegas wrote:
The following information may be helpful:
o Developer Guide material on Derby locking:
https://db.apache.org/derby/docs/10.17/devguide/cdevconcepts30291.html
o Reference Guide material on the SYSCS_DIAG.LOCK_TABLE diagnostic
table:
https://db.apache.org/derby/docs/10.17/ref/rrefsyscsdiaglocktable.html
o A wiki page on debugging locking problems:
https://cwiki.apache.org/confluence/display/DERBY/LockDebugging
Thanks. I had already read the devguide chapter, the second reference
told me what IS and IX meant, and the third only told me what I would
see if a deadlock occurred (and I posted what I saw). The IRC chat
referenced in the wiki page also didn't help me see what was going on in
my case.
The bit that puzzles me is this:
Lock : TABLE, ACTION_LOG, Tablelock
Waiting XID : {255785263, X} , APP, DELETE FROM active_list...
Why is it waiting for ACTION_LOG (and an exclusive table lock at that)
when the trigger doesn't refer to it and neither does the delete on
REGISTRATIONS that triggered it?
I am using the default isolation level (read committed). Would changing
the delete on REGISTRATIONS to read uncommitted make any difference?
I haven't been able to reproduce the error on my test rig yet, so the
little gray cells seem to be the only debugging tool available...
--
John English
Re: Help needed to understand deadlock
The following information may be helpful:
o Developer Guide material on Derby locking:
https://db.apache.org/derby/docs/10.17/devguide/cdevconcepts30291.html
o Reference Guide material on the SYSCS_DIAG.LOCK_TABLE diagnostic
table:
https://db.apache.org/derby/docs/10.17/ref/rrefsyscsdiaglocktable.html
o A wiki page on debugging locking problems:
https://cwiki.apache.org/confluence/display/DERBY/LockDebugging
On 2/2/24 11:46 AM, John English wrote:
My system recently reported the following:
java.sql.SQLTransactionRollbackException: A lock could not be obtained
due to a deadlock, cycle of locks and waiters is:
Lock : ROW, ACTION_LOG, (2,4800)
Waiting XID : {255785240, U} , APP, SELECT * FROM action_log WHERE
status='A' AND endtime>=CURRENT_TIMESTAMP FOR UPDATE
Granted XID : {255785232, U}
Lock : TABLE, REGISTRATIONS, Tablelock
Waiting XID : {255785232, IS} , APP, SELECT * FROM registrations
WHERE activity=?
Granted XID : {255785263, IX}
Lock : TABLE, ACTION_LOG, Tablelock
Waiting XID : {255785263, X} , APP, DELETE FROM active_list
WHERE username=CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(1)
AS VARCHAR(15))
AND username NOT IN (SELECT username FROM current_list)
Granted XID : {255785232, IX} , {255785233, IX} , {255785236, IX} ,
{255785240, IX} , {255785247, IX} , {255785249, IX}
Lock : ROW, ACTION_LOG, (2,4800)
Waiting XID : {255785249, U} , APP, SELECT * FROM action_log WHERE
username=? AND action=? AND seed=? FOR UPDATE
. The selected victim is XID : 255785240.
Users can register to carry out particular activities, and there is an
action log which is updated about once a minute while an activity is
in progress. The deadlock happened when I tried to delete an activity
and the associated registrations.
The "DELETE FROM active_list" is fired by the following trigger:
CREATE TRIGGER delete_user
AFTER DELETE ON registrations
REFERENCING OLD AS del
FOR EACH ROW MODE DB2SQL
DELETE FROM active_list
WHERE username=del.username
AND username NOT IN (SELECT username FROM current_list)
I am trying to understand why there is a table lock on the action log
(updating this is much more important and urgent than purging inactive
users!). I am not clear what U, X, IS and IX are -- uodate, exclusive,
shared something, exclusive something?
And of course I'd like to prevent it happening again. Would changing
the isolation level for one of the queries perhaps help? Any ideas
gratefully received!
Thanks,
Help needed to understand deadlock
My system recently reported the following:
java.sql.SQLTransactionRollbackException: A lock could not be obtained
due to a deadlock, cycle of locks and waiters is:
Lock : ROW, ACTION_LOG, (2,4800)
Waiting XID : {255785240, U} , APP, SELECT * FROM action_log WHERE
status='A' AND endtime>=CURRENT_TIMESTAMP FOR UPDATE
Granted XID : {255785232, U}
Lock : TABLE, REGISTRATIONS, Tablelock
Waiting XID : {255785232, IS} , APP, SELECT * FROM registrations
WHERE activity=?
Granted XID : {255785263, IX}
Lock : TABLE, ACTION_LOG, Tablelock
Waiting XID : {255785263, X} , APP, DELETE FROM active_list
WHERE username=CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(1)
AS VARCHAR(15))
AND username NOT IN (SELECT username FROM current_list)
Granted XID : {255785232, IX} , {255785233, IX} , {255785236, IX} ,
{255785240, IX} , {255785247, IX} , {255785249, IX}
Lock : ROW, ACTION_LOG, (2,4800)
Waiting XID : {255785249, U} , APP, SELECT * FROM action_log WHERE
username=? AND action=? AND seed=? FOR UPDATE
. The selected victim is XID : 255785240.
Users can register to carry out particular activities, and there is an
action log which is updated about once a minute while an activity is in
progress. The deadlock happened when I tried to delete an activity and
the associated registrations.
The "DELETE FROM active_list" is fired by the following trigger:
CREATE TRIGGER delete_user
AFTER DELETE ON registrations
REFERENCING OLD AS del
FOR EACH ROW MODE DB2SQL
DELETE FROM active_list
WHERE username=del.username
AND username NOT IN (SELECT username FROM current_list)
I am trying to understand why there is a table lock on the action log
(updating this is much more important and urgent than purging inactive
users!). I am not clear what U, X, IS and IX are -- uodate, exclusive,
shared something, exclusive something?
And of course I'd like to prevent it happening again. Would changing the
isolation level for one of the queries perhaps help? Any ideas
gratefully received!
Thanks,
--
John English
