Incrementing a value on the same row...

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 2005 Forums

Transact-SQL (2005)

Incrementing a value on the same row...

Author

Topic

jburke521
Starting Member

5 Posts

Posted - 2010-04-16 : 01:29:58

I have a somewhat unorthodox request and there may be an obvious alternative way to do this.

We have the need to keep track of a 4-digit sequence per every unique combination of two other columns.

For example, the table we're using has three columns:
WorkCode, Julian, Sequence

WorkCode + Julian = a combined primary key.

We need to increment the sequence column value by 1 for each WorkCode + Julian combination.

We generate the requests for a new sequence value via Java application.

We're accomplishing this with one problem...threading. Multiple requests are occassionally resulting in the same sequence being generated from more than one thread's request on the same row. We've tried to find a way to stop the duplicates but are having a difficult time.

We've tried stored procedures in combination with the REPEATABLE_READ isolation level, but this has dramatically slowed down the process. Any help on this would be greatly appreciated and I can supply more information as needed.

JB

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-04-16 : 02:55:07

Can you post the stored procedure in question? Are the concurrency issues solved, but it is just slow?

In most cases, a transaction is safe, but the locks required to make the insert/update operation atomic are obviously blowing your performance. The one thing you can do is make sure your table is indexed to keep the 2 reads required as fast as possible.


begin tran
   update t with (serializable)
   set Sequence = Sequence + 1
   from YourTable as [t]
   where WorkCode = @WorkCode and Julian = @Julian
   if @@rowcount = 0
   begin
      insert into YourTable (WorkCode, Julian, Sequence)
      values (@WorkCode, @Julian, 0)
   end
commit tran

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-04-16 : 03:05:03

and get the seq back using output


declare @out (Sequence int)
begin tran
   update t with (serializable)
   set Sequence = Sequence + 1
   output inserted.Sequence into @Out
   from YourTable as [t]
   where WorkCode = @WorkCode and Julian = @Julian
   if @@rowcount = 0
   begin
      insert into YourTable (WorkCode, Julian, Sequence)
      output inserted.Sequence into @Out
      values (@WorkCode, @Julian, 0)
   end
commit tran

select Sequence from @out

jburke521
Starting Member

5 Posts

Posted - 2010-04-16 : 10:53:01

We've tried setting the isolation levels to repeatable read and serializable. In both cases, duplicates seemed to be eliminated but the process took 20 times longer.

There isn't much indexing in place but the table only holds 30k rows, would indexing still be a factor here?

Here is the stored procedure we're using:

ALTER PROCEDURE GetNextSequence
@InJulian varchar(7) ,
@InWrkCde smallint
AS
BEGIN
BEGIN TRAN
-- @ReturnSeq will contain either the next available sequence
-- or -1 if there is an error (past 9999, etc)

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ReturnSeq AS INT

-- Get the next 'New' work item and put the ID in @WorkID
SELECT TOP 1 @ReturnSeq = SEQ
FROM ICNSEQUENCES (ROWLOCK)
WHERE JULIAN = @InJulian AND
WRK_CDE = @InWrkCde
-- If @@RowCount is 0 then there are no current rows
-- insert initial record and return 0
IF @@RowCount = 0
BEGIN
INSERT INTO ICNSEQUENCES(WRK_CDE,JULIAN,SEQ) values (@InWrkCde,@InJulian,1)
SELECT 1 AS ReturnSeq
COMMIT TRAN
RETURN
END
ELSE
BEGIN
IF @ReturnSeq + 1 > 9999
BEGIN
SELECT -1 AS ReturnSeq
COMMIT TRAN
RETURN
END

SELECT @ReturnSeq + 1 AS ReturnSeq
-- Update the return sequence and table entry
UPDATE ICNSEQUENCES
SET SEQ = @ReturnSeq + 1
WHERE JULIAN = @InJulian AND
WRK_CDE = @InWrkCde
END
COMMIT TRAN
END
GO

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-04-16 : 14:12:04

I was able to reproduce the performance issue (and deadlock occurrence) when the table had no indexes or PK. Using a composite key greatly increased performance and eliminated the deadlocks (at least in my tests).

How do your tests fair with this setup:


if object_id('dbo.ICNSEQUENCES') is not null
	drop table dbo.ICNSEQUENCES
create table dbo.ICNSEQUENCES (JULIAN varchar(7), WRK_CDE smallint, SEQ int primary key clustered(Julian, WRK_CDE))
go
create procedure [dbo].[GetNextSequence2]
	(	@InJulian varchar(7), 
		@InWrkCde smallint
	)
as
begin
	set nocount on;

	declare @ReturnSeq as int;
	declare @out table (SEQ int);
	
	begin tran
	   update t with (serializable)
	   set SEQ = SEQ + 1
	   output inserted.SEQ into @Out
	   from dbo.ICNSEQUENCES as [t]
	   where WRK_CDE = @InWrkCde and Julian = @InJulian
	   if @@rowcount = 0
	   begin
		  insert into dbo.ICNSEQUENCES(WRK_CDE, JULIAN, SEQ)
		  output inserted.SEQ into @Out
		  values (@InWrkCde, @InJulian, 1)
	   end
	commit tran

	select SEQ as [ReturnSeq] from @out
	
end;

jburke521
Starting Member

5 Posts

Posted - 2010-04-16 : 16:54:55

A composite key was definitely a part of the design but it hadn't been created yet in our Development environment. That's been resolved.

Also, using your procedure as a template, I've go this in place:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE UpdateNextSequence
-- Add the parameters for the stored procedure here
@InJulian varchar(7) ,
@InWrkCde smallint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
BEGIN TRAN

DECLARE @OUT TABLE (tempSEQ INT)

UPDATE ICNSEQUENCES WITH (REPEATABLEREAD)
SET SEQ = SEQ + 1
OUTPUT Inserted.SEQ INTO @OUT
WHERE WRK_CDE = @InWrkCde AND JULIAN = @InJulian

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO ICNSEQUENCES (WRK_CDE, JULIAN, SEQ)
OUTPUT Inserted.SEQ INTO @OUT
VALUES (@InWrkCde, @InJulian, 1)
END

COMMIT TRAN
SELECT tempSEQ from @OUT
END
GO

So far the testing on this has been very successful. Thanks very much for your assistance. It never occurred to me to start the whole process with an update instead of a select.

JB

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-04-16 : 17:46:15

Glad to hear it. Mladen's article on upsert operation is very good walkthrough about whats going on under the hood.

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

Please post back if you run into issues.

Nathan Skerl

jburke521
Starting Member

5 Posts

Posted - 2010-04-19 : 10:56:51

Well we tested this procedure using an application with 10 seperate threads over the weekend. We triggered 14k ICN operations and 2% of the ICNs were duplicated. Any ideas as to how this happened? My guess I need to use serialization but I'm not sure.

JB

jburke521
Starting Member

5 Posts

Posted - 2010-04-19 : 16:46:06

Never mind my last post. The duplicates were being generated because of faulty logic in my application. Because we don't allow sequences of 10k, the code was throwing exceptions but also returning the last successfull ICN. It did this 4400 times which coincides with the number of ICNs over 10k that we tried to generate in test.

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-04-20 : 00:19:19

Such high concurrency is very interesting. Ive also used mutex locks in tsql for other purposes, but would be interesting to see how they performed in your situation.

Check out the link below. I think you might find it useful to hold an applock per Julian, allowing you to get very high performance between non overlapping Julian inputs (which Im assuming is the majority of the inputs).

http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources