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, Jul 15 '12 at 08: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, Jul 15 '12 at 11:43
sorry it was suppose to be pseudo code but got a bit too close to sql. — JD., Jul 15 '12 at 19:15

score 2 · Answer 1 · edited May 23 '17 at 12:27

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?

What about optimistic locking, is there a way I can throw any concurrent transactions out due to optimistic locking and then retry again? — JD., Jul 15 '12 at 19:17
@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? — Heinzi, Jul 19 '12 at 14:58

score 1 · Answer 2 · answered Jul 15 '12 at 08:17

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 '12 at 08:17

Albin Sunnanbo

46,430
8
69
108

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. Jul 15 '12 at 08:18
Sorry the psuedo code above missed out numberOfRecords = select count from table where foreignKey = "somevalue", in this case would identity column work? – JD. Jul 15 '12 at 08:30
1

@JD: No. In terms of concurrency, `SELECT` + `INSERT` and `INSERT...SELECT` behave similarly. See [this question](http://stackoverflow.com/q/1994771/87698) for more details. – Heinzi Jul 15 '12 at 10:31
As @Heinzi writes, the behaviour is dependent of the transaction isolation level, but the answer is correct for the default level (READ COMMITED) – Anders Abel Jul 15 '12 at 17:15

score 1 · Answer 3 · answered Jul 15 '12 at 08:50

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

I think the basic issue is the same here. It is just two lines stuffed into the same statement. Not sure they are atomic. — usr, Jul 15 '12 at 10:20

score 1 · Answer 4 · answered Jul 15 '12 at 09:53

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 '12 at 09:53

Santosh Chaurasia

113
1
6

Isn't this Oracle only? Didn't think sql server supported sequences – Charleh Jul 15 '12 at 10:14
@Charleh - SQL Server 2012 does. – Martin Smith Jul 15 '12 at 12:23

score 1 · Answer 5 · answered Jul 15 '12 at 10:23

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!).

Inserting into a table with sequence number

5 Answers5