Multi-Thread Insert - Duplicate Key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multi-Thread Insert - Duplicate Key

Author

Topic

pomela
Starting Member

15 Posts

Posted - 2006-09-04 : 04:34:46

Hi,
I have multi-thread application that runs the same stored procedure parallelly.
Here is an example:

IF OBJECT_ID('dbo.CustomerTotals', 'U') IS NOT NULL
DROP TABLE dbo.CustomerTotals
GO

CREATE TABLE CustomerTotals
(
CustomerId int,
Total float
)
GO

IF OBJECT_ID('dbo.UpdateCustomerTotal') IS NOT NULL
DROP PROCEDURE dbo.UpdateCustomerTotal
GO

CREATE PROCEDURE dbo.UpdateCustomerTotal
(
@CustomerId int,
@Amount float
)
AS

-- Update existing record for CustomerId
UPDATE CustomerTotals
SET Total = Total + @Amount
WHERE CustomerId = @CustomerId

-- In case there is no record for this @CustomerId we generate a new record
INSERT INTO CustomerTotals (CustomerId, Total)
SELECT @CustomerId, @Amount
WHERE NOT EXISTS
(SELECT * FROM CustomerTotals WHERE CustomerId = @CustomerId)
GO

-- EXEC dbo.UpdateCustomerTotal @CustomerId = 999, @Amount = 888.34

The problem happens when two threads gets the same @CustomerId and both trying to insert a new record for this customer.
CustomerId is the primary-key for the table. Using WHERE NOT EXISTS is not helping, I guess that both threads gets "true" answer if they run simultaneously.
The error we get is for trying to insert duplicate-key of course.

I'd like to hear any ideas or solutions for this issue.
Do you think a transaction can help here? Lock hints? What is the meaning in performance side?

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:37:04

try this

CREATE PROCEDURE dbo.UpdateCustomerTotal
(
	@CustomerId int,
	@Amount float
)
AS

-- Update existing record for CustomerId
UPDATE	CustomerTotals
SET	Total = Total + @Amount
WHERE	CustomerId = @CustomerId

IF @@ROWCOUNT = 0
	-- In case there is no record for this @CustomerId we generate a new record
	INSERT	CustomerTotals
		(
			CustomerId,
			Total
		)
	VALUES	(
			@CustomerId,
			@Amount
		)
GO

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:38:09

Also, are you using client side TRANSACTIONS?

Peter Larsson
Helsingborg, Sweden

pomela
Starting Member

15 Posts

Posted - 2006-09-04 : 05:40:38

I'm not using any kind of transaction.
I don't understand how querying @@ROWCOUNT will help? if both threads get's @@ROWCOUNT=0 they will try to insert a record with the same key..

any other solutions are welcome.
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 05:44:14

quote:
Originally posted by pomela

I don't understand how querying @@ROWCOUNT will help? if both threads get's @@ROWCOUNT=0 they will try to insert a record with the same key..

any other solutions are welcome.

They can't both get @@ROWCOUNT = 0 since SQL Server inserts records in order from a queue of actions to do!

Whenever first thread runs the sp, the records get either updated or inserted. Whenever second thread runs the sp, the record gets updated only.

In my suggestion, I have use no lock hints, so the suggested code should work. If you are not confident in this, try to use the TABLOCK hint.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 05:49:39

Are there more to this exam query that we do nothing about yet?

Peter Larsson
Helsingborg, Sweden

pomela
Starting Member

15 Posts

Posted - 2006-09-04 : 09:55:56

Hi Peso
Thanks for your help, but I'm sorry, I tested it and the check of @@ROWCOUNT is not helping.
If you execute this procedure simultaneously for the same @CustomerId, and a record with this CustomerId still not exists in the table, both threads will pass the update phase with @@Rowcount=0 and will try to insert a record, one of the threads will get "Violation of PRIMARY KEY" exception..

TABLOCK is not considerable, because there will be no meaning to run multi-thread application with TABLOCK, it will have the same effect as running one thread at a time.

Thanks again for your help, I appreciate it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 10:04:35

I would like to look at the code you made to test "threading insert" into SQL Server.
I use this code for 400+ simultaneous users, and it have worked for years.

Even though the application is multithreaded, it doesn't mean that SQL server can execute the sp simultanesously. The SQL Server has to form a queue for the stored procedures to run, or SQL statements to run.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-04 : 16:10:33

I dont agree with Peso's posts here.
The @@rowcount can most certainly lead to PK violations.
I actually prefer the NOT EXISTS method, it feels more "atomic" to me.

You could do:
INSERT INTO CustomerTotals (CustomerId, Total)
SELECT @CustomerId, @Amount
WHERE NOT EXISTS
(SELECT * FROM CustomerTotals(HOLDLOCK) WHERE CustomerId = @CustomerId)

which can/will lead to deadlock instead of PK violations.
from my standpoint that is better, only insert correct data, or none at all.

Here are 2 great links:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/15/945.aspx
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

If you use IDENTITY, then you will not have this issue, of course then the client does not provide the key, which might mean other issues iyc.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources