471,086 Members | 1,102 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,086 software developers and data experts.

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

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
May 23 '07 #1
3 5357
almaz
168 Expert 100+
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.
May 23 '07 #2
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.com/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.
May 23 '07 #3
almaz
168 Expert 100+
Locks should be obtained using transactions. Your stored procedure should look like:
Expand|Select|Wrap|Line Numbers
  1. create procedure dbo.RefreshData(@ID int, @Data varchar(100))
  2. as
  3. set nocount on
  4. set isolation level serializable
  5. begin transaction
  6.   if not exists(select * from YourTable where ID = @ID)
  7.     insert YourTable (ID, Data) values (@ID, @Data)
  8.   else
  9.     update YourTable 
  10.     set Data=@Data 
  11.     where ID = @ID
  12. 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.
May 23 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.