Hello,
I have a test case for my JDBC DAO layer that runs 50 concurrent threads all
inserting the same data to ensure that the DAO does not throw an error if the
data is already in the table (more details on the app below). After working a
while Derby 10.4.2.0 stops making progress, the java process shows 0% CPU
utilization and Derby does not report a deadlock. Running kill -QUIT on java
shows all threads waiting on something. After a while, one transaction will
timeout.
Setting the lock timeout to -1 did not get the test to finish successfully. If
I reduce the number of threads in the test to 10, then Derby successfully
completes. The same exact code runs against PostgreSQL and Oracle all 50
threads complete successfully.
Connecting to the Derby server with ij and SELECTing on SYSCS_DIAG.LOCK_TABLE
shows that the transaction that has all the locks that other transactions are
waiting on is not in a WAIT state for any other lock. So according to this, it
should be making progress, but it's not. Are there locks that the transaction
can be waiting on that aren't reported by SYSCS_DIAG.LOCK_TABLE. Are there any
other things to look at?
I have YourKit if that'll make it easier to help look at some internal
structures to see what's happening. I can put a test case together that
replicates the behavior outside of our application and but it'll take a little
bit of coding and I can attach it later.
The application needs to treat a database more like a hash table than a database
for some of the data that needs to be stored. The primary key for the row is a
MD5 hash of the other columns, so if there are multiple clients connecting to
the same Derby server and are given the same data, which can happen due to load
balancing to the application servers, they'll all try to insert or update the
same row.
As background, below is the schema and code showing what I'm doing. The schema
has four tables, three of which represent a set of facilities and the fourth a
location.
CREATE TABLE facility
(
facility_id int primary key,
code char(3)
);
CREATE TABLE facility_set
(
facility_set_id int primary key
)
CREATE TABLE facility_set_membership
(
facility_id int,
facility_set_id int
)
CREATE TABLE location
(
location_id int primary key,
facility_set_id int,
path varchar(256)
)
So I have something like this to ensure the data is there and it'll update a
foreign key reference to the set of facilities. The actual code is a little
different.
public class LocationJdbcDao
{
// INSERT the location into the database or if the location is already
// in the database, then ensure that the set of facilities the location
// is associated with includes the input set of facilities.
public merge(Location l, FacilitySet fs)
{
try {
INSERT INTO
location (location_id, facility_set_id)
VALUES (l.id, fs.id)
}
catch (Throwable e) {
// Get the primary key for the facility set.
fs_id = "SELECT facility_set_id
FROM location where location_id = ? FOR UPDATE", l.id
// Get the actual FacilitySet object from its ID.
current_fs = lookup(fs_id)
// Merge the FacilitySet from the database with the fs argument.
FacilitySet union = fs.union(current_fs)
// Update the location's facility set if the union is different.
if (union != current_fs) {
// The union may not be in the database, so update facility_set
// and facility_set_membership.
try {
"INSERT facility_set (facility_set_id) VALUES (?)", union.id
for (facility : union) {
"INSERT INTO
facility_set_membership (facility_set_id,
facility_id)
VALUES (?, ?)", u.id, facility.id
}
}
catch (Throwable) {
// The union set should be there, but double check it. Count
// the two primary keys and check that they are consistent.
"SELECT
COUNT(facility_set.facility_set_id),
COUNT(facility_set_membership.facility_set_id)
FROM
facility_set
LEFT OUTER JOIN
facility_set_membership
ON
facility_set.facility_set_id =
facility_set_membership.facility_set_id
WHERE
facility_set.facility_set_id = ?", union.id
}
// Update the location's foreign key to the facility set.
"UPDATE location SET facility_set_id = ?", union.id
}
}
}
}
If there's anything else I can provide, please let me know.
Regards,
Blair
--
Blair Zajac, Ph.D.
CTO, OrcaWare Technologies
<[email protected]>
Subversion training, consulting and support
http://www.orcaware.com/svn/
