Duplicate key issue in a transaction block

Hi Everyone,

I have a hard to reproduce scenario for a production site....I tried to simplify the code and at the end I could get a similar problem with the following table and java code.
The problem is that I have a transaction that deletes all the records in a group and inserts the new records for that group. If that transaction is called from 2 different clients for the same groupid it happens to get a duplicate key violation which it should never happen on my opinion!!!!!!!!

Example:

begin transaction
delete from infotest where groupid = 1;
insert into infotest (groupid, subgroupid, datemodified) values (1,1,'2009-01-01 12:00');
insert into infotest (groupid, subgroupid, datemodified) values (1,2,'2009-01-01 12:00');
insert into infotest (groupid, subgroupid, datemodified) values (1,3,'2009-01-01 12:00');
...
commit transaction;

I am running postgres 8.3.1 on a SUSE LINUX 10.1 (X86-64) VERSION = 10.1

This is the postgres log sequence:

2009-05-31 19:05:49.235 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,1,"BEGIN",2009-05-31 19:05:49 CST,2/8,0,LOG,00000,"execute S_1: BEGIN",,,,,,,,
2009-05-31 19:05:49.236 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,2,"DELETE",2009-05-31 19:05:49 CST,2/8,0,LOG,00000,"execute <unnamed>: delete from infotest where groupid = $1","parameters: $1 = '1'",,,,,,,
2009-05-31 19:05:49.257 CST,"postgres","test",25305,"172.20.23.16:36748",4a23296d.62d9,104,"SELECT",2009-05-31 19:05:49 CST,1/58,0,LOG,00000,"execute <unnamed>: select groupid, subgroupid, datemodified from infotest where groupid = 1",,,,,,,,
2009-05-31 19:05:49.258 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,3,"INSERT",2009-05-31 19:05:49 CST,2/8,884974,LOG,00000,"execute <unnamed>: insert into infotest (groupid, subgroupid, datemodified) values ($1,$2,$3)","parameters: $1 = '1', $2 = '1', $3 = '2009-06-08 11:33:19.667-04'",,,,,,,
2009-05-31 19:05:49.258 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,4,"INSERT",2009-05-31 19:05:49 CST,2/8,884974,ERROR,23505,"duplicate key value violates unique constraint ""pk_infotest""",,,,,,"insert into infotest (groupid, subgroupid, datemodified) values ($1,$2,$3)",,
2009-05-31 19:05:49.297 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,5,"idle in transaction",2009-05-31 19:05:49 CST,2/0,884974,LOG,08P01,"unexpected EOF on client connection",,,,,,,,

This is the script to create the table:

create table infotest (groupid integer, subgroupid integer, datemodified timestamp with time zone);
alter table infotest add constraint pk_infotest primary key (groupid,subgroupid);

And this is the java code I used in a loop:

DbTest1.java file:

import java.sql.Connection;
import java..sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;

public class DbTest1
{
private Connection connection;
private Statement sql;;
private DatabaseMetaData dbmd;

private static final String pgClassName = "org.postgresql.Driver";
private static final String pgUrl = "jdbc:postgresql://172.20.23.18/test";

private static final String seqClassName = "org.continuent.sequoia.driver.Driver";
private static final String seqUrl = "jdbc:sequoia://172.20.23.18/abrazo";

private String login = "postgres";
private String password = "testpassword";

public void doTest(String conn) throws Exception
{
try
{
String localURL;
String localDriver;
System.out.println("Initializing Driver for " + conn);
if (conn.toLowerCase().equals("pg"))
{
new org.postgresql.Driver();
localDriver = pgClassName;
localURL = pgUrl;
}
else
{
new org.continuent.sequoia.driver.Driver();
localDriver = seqClassName;
localURL = seqUrl;
}

System.out..println("Getting Connection using [" + localDriver + "] from [" + localURL + "]");
connection = DriverManager.getConnection(localURL, login, password);
System.out.println("Connection established!");

dbmd = connection.getMetaData(); //get MetaData to confirm connection
System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
dbmd.getDatabaseProductVersion()+" successful.\n");

sql = connection.createStatement(); //create a statement that we can use later

connection.setAutoCommit(false);

String sqlDel = "delete from infotest where groupid = ?";
String sqlIns = "insert into infotest (groupid, subgroupid, datemodified) values (?,?,?)";
PreparedStatement psDel = connection.prepareStatement(sqlDel);
PreparedStatement psIns = connection.prepareStatement(sqlIns);

Calendar c = Calendar.getInstance();
int GroupId = 1;
int LoopCount = 100;

System.out.println("Begin transaction...");

// Delete
psDel.setInt(1,GroupId);
psDel.executeUpdate();
System.out.println("Finished the delete...");

// Insert

int SubGroupID;
for ( SubGroupID=1; SubGroupID<=LoopCount; SubGroupID++ ) {
psIns.setInt(1,GroupId);
psIns.setInt(2,SubGroupID);
psIns.setTimestamp(3, new Timestamp(c.getTimeInMillis()));
psIns.executeUpdate();
}
System.out.println("Finished the inserts...");

psDel.close();
psIns.close();

connection.commit();
System.out.println("Commit transaction...");

connection.setAutoCommit(true);
ResultSet results = sql.executeQuery("select groupid, subgroupid, datemodified from infotest where groupid = "+GroupId);
while (results.next())
{
System.out.println("groupid = "+results.getInt(1)+"; subgroupid = "+results.getInt(2)+"; datemodified = "+results.getTimestamp(3) );
}
results.close();

connection.close();
}
catch (Exception cnfe)
{
cnfe.printStackTrace();
}
}

public static void main (String args[])
{
if (args == null || args.length != 1 || (!args[0].toLowerCase().equals("pg") && !args[0].toLowerCase().equals("seq")))
{
System.out.println("Usage: " + DbTest1.class.getName() + " pg|seq");
System.exit(1);
}

try
{
DbTest1 demo = new DbTest1();
demo.doTest(args[0]);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}

Thank you very much,

Ioana Danes

__________________________________________________________________
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. Optimized for Yahoo! Get it Now for Free! at http://downloads.yahoo.com/ca/internetexplorer/