Synchronizing issue

[Michael Rosenthal]

I am helping debug an application written by someone else. I am receiving the following error and cannot seem to resolve it. Any thoughts would be appreciated.
When more than one user attempts to add items to a list, which is saved to the db, some but not all items error on a primary key constraint. There is a localized method in the KeyDB class that queries the database for the next primary key value based on the table selected.
The error almost appears as if one process gets the primary key for its insert, and then the other process somehow accesses the same class and grabs the same primary key. Both processes are then set to insert with the same primary key and of course one fails. I have modified the code to make the getNextUniqueVal method synchronized, but I am still receiving the error below. Below is some of the code in the app. All of the code below is in a loop in the application going through all selected items.
CODE TO FOLLOW

[Michael Rosenthal]

SearchController extends HTTPServlet implements Serializable
{
public void DOPOST(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException
{
processAddToList(HttpServletRequest request,
HttpServletResponse response);
}
processAddToList(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException
{
comment Data bean that can also do inserts and updates of itself to db
KeyDB k = new KeyDB;

comment This method sets the table that the PK number is needed for
k.setTable(ListCONSTANT);
comment This determines the next PK number and sets it into k.id
k.getNextUniqueVal;

comment Data bean that can also do inserts-updates-deletes of itself to db
ListDB lst = new ListDB;
lst.setID(k.getID);
try
{
lst.insert;
}
catch(Exception e)
{
comment This throws a the following error periodically
One or more values in the INSERT statement, UPDATE statement, or foreign
key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by 1
constrains table TEST.LIST from having duplicate rows for those
columns. SQLSTATE 23505
}
}
}

[danny liu]

Mike:
There is a mistake in your code. You are using a *local* KeyDB to get a key.
If multiple requests are processed simultaneously, there may be duplicate keys. Cause there may be 2+ KeyDB instances are accessing the same table, retrieving the same *next* key.
The solution is to synchornize on the *KeyDB* ojbect.

SearchController extends HTTPServlet implements Serializable
{
public void DOPOST(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException
{
processAddToList(HttpServletRequest request,
HttpServletResponse response);
}
processAddToList(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException
{
comment Data bean that can also do inserts and updates of itself to db
KeyDB k = new KeyDB;
comment This method sets the table that the PK number is needed for
k.setTable(ListCONSTANT);
comment This determines the next PK number and sets it into k.id
k.getNextUniqueVal;
comment Data bean that can also do inserts-updates-deletes of itself to db
ListDB lst = new ListDB;
lst.setID(k.getID);
try
{
lst.insert;
}
catch(Exception e)
{
comment This throws a the following error periodically
One or more values in the INSERT statement, UPDATE statement, or foreign
key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by 1
constrains table TEST.LIST from having duplicate rows for those
columns. SQLSTATE 23505
}
}
}

[danny liu]

My suggestion is:

rewrite the KeyDB class, make its getNextID() method synchronized.
add a contextlistner, which add an instance of KeyDB into *application* scope when the application is started

Hope it helps.
Dan

[wally fer]

put the keydb and the insert statement in synchronized block