db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Blair Zajac <bl...@orcaware.com>
Subject 50 transactions timing out with no CPU usage and no deadlocks
Date Mon, 26 Jan 2009 01:27:32 GMT
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
<blair@orcaware.com>
Subversion training, consulting and support
http://www.orcaware.com/svn/



Mime
View raw message