473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 5915
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

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

Similar topics

4
by: Doo-Dah Man | last post by:
I hope this is the right group for this question, if it's not please direct me. I have a data-driven website that allows users to enter records for sales leads. It all works perfectly. The...
1
by: dbuchanan | last post by:
VB.NET 2003 / SQLS2K The Stored Procedure returns records within Query Analyzer. But when the Stored Procedure is called by ADO.NET ~ it produced the following error message. ...
4
by: KKramsch | last post by:
My code is generating this type of error: Security Error: Content at http://nonexistent.org/somepage.html may not load data from about:blank. The "about:blank" page mentioned in the error...
4
by: prasad | last post by:
I am getting sql error during binding a program which access a temporary table. The temporary table declaration and access methods are given below. EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEM88...
0
by: vsaraog | last post by:
Hi y'all, I am using a Created Global Temporary Table in DB2 OS/390 version 7.1.2. I am inserting some data in it and then updating the table using ODBC but while I try to update it, I am...
0
by: Alpha | last post by:
I have a dataset containing a table as a data source for a list box in the windows application depending on which radio button the user selects. The table is disposed when the user clicks on other...
2
by: Henrik | last post by:
im reciving an error when i tries to read multidimensional XML data, into my system. I'm receving the same errors discriped at: http://support.microsoft.com/default.aspx?scid=kb;en-us;325695...
0
by: babukgl | last post by:
Hi, We have a stored procedure which does a huge amount of transformation of data and it is running pretty slow. To avoid this we tried to create a cursor out of the SQL used in stored...
3
by: James Watson | last post by:
'Microsoft VB 6.3, Access 2002 'Syntax error in the INSERT INTO Statement when the query runs 'How can I make this work? Private Sub Command52_Click() On Error GoTo Err_Command52_Click ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.