jQuery in Action, 2nd edition*
The moose likes Servlets and the fly likes Synchronizing issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Synchronizing issue" Watch "Synchronizing issue" New topic
Author

Synchronizing issue

Michael Rosenthal
Greenhorn

Joined: Nov 17, 2003
Posts: 27
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


Sun Certified Java2 Programmer - 1.4 - SCJP<br />Sun Certified Web Component Developer for J2EE Platform - SCWCD
Michael Rosenthal
Greenhorn

Joined: Nov 17, 2003
Posts: 27
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
Ranch Hand

Joined: Jan 22, 2004
Posts: 185
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
Ranch Hand

Joined: Jan 22, 2004
Posts: 185
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
Greenhorn

Joined: Apr 28, 2004
Posts: 5
put the keydb and the insert statement in synchronized block
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Synchronizing issue
 
Similar Threads
How do I prevent duplicate keys on insert?
CMP 2.0 problem
BMP
executing Servlet using AJAX
Referential integrity error in Child table even when Parent table has values