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,
