Duplicate key insertion error when concurrent users insert same data into table

[Subrat Das]

Hi,
I have a java application which calls a stored procedure to insert data into a table.Multiple threads of java call the same procedure at the same time. Sometimes it happens that few threads send the same data as parameter to the procedure. In that case i get the error : "Cannot insert duplicate key row in object ...."

The functionality of the proc is - It checks if a given data (Based on Unique key) is present in the table or not. If present it doesnt insert.It simply updates. If no row present, it will insert a new row.

So when both the concurrent processes search for the existence of data, both of them dont find the data. Both go on to insert the same data which gives the problem.

I tried using WITH(HOLDLOCK) and WITH(NOLOCK), but still get the same error.

Can anyone please help in this.

Regards,
Subrat

[almaz]

I tried using WITH(HOLDLOCK) and WITH(NOLOCK), but still get the same error

Surround the checking for existence & insertion/update with BEGIN TRANSACTION/COMMIT TRANSACTION. Also it looks like you'll have to set SERIALIZABLE isolation level (SET ISOLATION LEVEL SERIALIZABLE) before BEGIN TRANSACTION.

[Subrat Das]

Surround the checking for existence & insertion/update with BEGIN TRANSACTION/COMMIT TRANSACTION. Also it looks like you'll have to set SERIALIZABLE isolation level (SET ISOLATION LEVEL SERIALIZABLE) before BEGIN TRANSACTION.

I am using IF NOT EXISTS(select.. .) then insert.Any idea about sp_getapplock. I saw in a site http://sqlblogcasts.co m/blogs/tonyrogerson/archive/2006/06/30/855.aspx
a solution for it, but unable to implement it.

If i change the way i am checking for the existence of row, will it help.Waiting for your valuable suggestions.

[almaz]

Locks should be obtained using transactions. Your stored procedure should look like:

create procedure dbo.RefreshData(@ID int, @Data varchar(100))
as
set nocount on
set isolation level serializable
begin transaction
  if not exists(select * from YourTable where ID = @ID)
    insert YourTable (ID, Data) values (@ID, @Data)
  else
    update YourTable 
    set Data=@Data 
    where ID = @ID
commit transaction

By the way, if your primary key is a single int field, it is recommended to make it IDENTITY field, and refactor your code so that primary key value is generated on SQL Server and than returned to client. This way will allow you to omit serializable isolation level.