db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English ...@brighton.ac.uk>
Subject Locking problem
Date Wed, 06 Oct 2010 11:40:40 GMT
I am using Derby 10.5.3 and am having occasional locking problems which
result in a RollbackExpection ("a lock could not be obtained within the
time requested") when deleting registration information from one of my
tables. Each user can be a member of multiple groups, and the table of
registrations is a many-to-many join table between the users and the
groups. What I am doing is a series of deletes to remove users from a
particular group, like this:

   DELETE FROM registrations WHERE username=x AND group=y

Associated with the registrations table is a trigger to remove users who
no longer have any registrations:

   CREATE TRIGGER delete_user
   AFTER DELETE ON registrations
   FOR EACH ROW MODE DB2SQL
   DELETE FROM users
     WHERE username NOT IN (SELECT username FROM registrations)

I suspected that the regsitrations table is being locked by the next
delete in the series and that the trigger can't read from registrations.
However, the lock table seems to be telling me that the problem is with
the users table:

*** The following row is the victim ***
188504168 |ROW |S   |0 |(2,6)   |WAIT |T|NULL |USERS 
             *** The above row is the victim ***
188504167 |ROW |X   |1 |(6,123) |GRANT|T|NULL |USERS 

188504167 |ROW |X   |1 |(6,118) |GRANT|T|NULL |USERS 

188504167 |ROW |X   |1 |(6,130) |GRANT|T|NULL |USERS 

188504167 |ROW |X   |1 |(6,126) |GRANT|T|NULL |USERS
... and so on for another 413 rows ...

Now, I could modify my schema to add a column to the users table to
keep a reference count of the number of registrations, use triggers
on the registrations table to maintain the reference count, and add
a trigger to the users table to delete the user when the reference
count reaches zero. But this assumes the problem is really as I
explained it above.

My big worry is that I don't really understand what is actually
causing the problem here, and I don't want to apply a fix to the
wrong problem. I don't really understand the details of what the
lock table dump is telling me, and would appreciate any help anyone
could give me to explain what is really happening here (rather than
my half-baked misconceptions about it all).

TIA,

----------------------------------------------------------------------
  John English              | mailto:je@brighton.ac.uk
  Senior Lecturer           | http://www.it.bton.ac.uk/staff/je
  School of Computing & MIS | "Those who don't know their history
  University of Brighton    |  are condemned to relive it" (Santayana)
----------------------------------------------------------------------

Mime
View raw message