Inserting into a table with sequence number

Question

If I have the following:

Begin transaction
 numberOfRecords = select count from table where foreignKey = "some value" 

 Insert into table (set SequenceNumber = numberOfRecords + 1)
End Transaction

and multiple users are executing the above code, would each insert have a unique increasing number?

In other words, does the begin transaction queue up other transactions even reads so that each insert will have the correct sequence number? or do I require Insert into..Select statement to achieve what I want?

Thanks.

I'm a bit confused what you're trying to achieve here. Are you trying to create an auto incrementing ID column? could you not just use an identity column? — dougajmcdonald
– dougajmcdonald, Commented Jul 15, 2012 at 8:20
Count is a peculiar name for a column. If you were planning on using the count of rows in the table then you don't expect any rows to ever be deleted. — HABO
– HABO, Commented Jul 15, 2012 at 11:43
sorry it was suppose to be pseudo code but got a bit too close to sql. — JD.
– JD., Commented Jul 15, 2012 at 19:15

Community · Accepted Answer · 2017-05-23 12:27:53Z

2

No, a transaction with the default SQL Server isolation level (READ COMMITTED) is not sufficient. Putting it into one INSERT...SELECT statement won't fix it either. You basically have two options to fix this:

Option 1: Set your isolation level to SERIALIZABLE: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. This will ensure that transactions from two different users occur as if they occurred in sequence. This, however, might create deadlocks if many such transactions occur in parallel.

Option 2: Exclusively lock the table at the beginning of the transaction (SELECT * FROM table WITH (TABLOCKX, HOLDLOCK) WHERE 1=0). Note that this might impact performance if table is used frequently.

Both approaches have been analyzed in detail in my answer to the following SO question:

In tsql is an Insert with a Select statement safe in terms of concurrency?

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Jul 15, 2012 at 10:29

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JD. Over a year ago

What about optimistic locking, is there a way I can throw any concurrent transactions out due to optimistic locking and then retry again?

Heinzi Over a year ago

@JD: Optimistic locking usually uses some kind of version number on row level. I'm not sure how it could be applied here. Can you elaborate?

Albin Sunnanbo · Accepted Answer · 2012-07-15 08:17:59Z

1

No, transactions does not queue up commands, it is not like a lock.

Usually you want to use an identity column, but in some cases when you want to generate SequenceNumber without gaps you need to use the above code with a unique constraint on the SequenceNumber column and be prepared to retry if the commit transaction throws an exception.

answered Jul 15, 2012 at 8:17

Albin Sunnanbo

47.2k8 gold badges72 silver badges110 bronze badges

4 Comments

JD. Over a year ago

Thanks for the reply. What if I was doing just inserts in the transaction (not selects), also would Insert ...Select as one statement do what I want?

JD. Over a year ago

Sorry the psuedo code above missed out numberOfRecords = select count from table where foreignKey = "somevalue", in this case would identity column work?

Heinzi Over a year ago

@JD: No. In terms of concurrency, SELECT + INSERT and INSERT...SELECT behave similarly. See this question for more details.

Anders Abel Over a year ago

As @Heinzi writes, the behaviour is dependent of the transaction isolation level, but the answer is correct for the default level (READ COMMITED)

Xardax99 · Accepted Answer · 2012-07-15 08:50:28Z

1

I once used a SQL DB as a logger for a massive data export, to get a sequential "identity" I created a "on insert" trigger that dealt with issuing the next number. Worked well for me, however it was only a single user DB so not sure if there's any issues with multiple users and what I did.

Now that I've re-read the question, this may not be what your looking for but I think you could also do a trigger for a select?

USE [ExportLog]
GO

/****** Object:  Trigger [dbo].[Migration_Update_Event_Date]    Script Date: 02/10/2011                17:06:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Migration_Update_LogState]
ON [dbo].[MigrationLog] FOR INSERT NOT FOR REPLICATION
AS
UPDATE [MIGRATION-DATA].dbo.MIGRATIONSTATE
SET LASTPROCESSID = ID
WHERE MACHINENAME IN (SELECT MACHINENAME FROM INSERTED)

GO

answered Jul 15, 2012 at 8:50

Xardax99

1811 silver badge6 bronze badges

1 Comment

usr Over a year ago

I think the basic issue is the same here. It is just two lines stuffed into the same statement. Not sure they are atomic.

Santosh Chaurasia · Accepted Answer · 2012-07-15 09:53:26Z

1

if you want to insert the record in unique ,then first you create on sequence then record should be insert thorugh sequance .like

create sequnce seq_num ;

now use seq_num to insert the rcords .

insert into <table name>(col1) values(seq_num.nextval);

answered Jul 15, 2012 at 9:53

Santosh Chaurasia

1131 gold badge1 silver badge6 bronze badges

2 Comments

Charleh Over a year ago

Isn't this Oracle only? Didn't think sql server supported sequences

Martin Smith Over a year ago

@Charleh - SQL Server 2012 does.

usr · Accepted Answer · 2012-07-15 10:23:13Z

1

You need to set the transaction isolation level to a level that provides the right isolation level. In your code, two processes could execute the first line, then the second line. Both will insert the same value.

Set the isolation level to serializable and perform the select statement WITH (UPDLOCK). This will reduce concurrency in your system but it will be safe.

Other strategies are possible, but they are more time-consuming to implement (and test!).

answered Jul 15, 2012 at 10:23

usr

172k38 gold badges251 silver badges380 bronze badges

Collectives™ on Stack Overflow

Inserting into a table with sequence number

5 Answers 5

2 Comments

4 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

4 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related