CODEWITHSUNDEEP
sqlsql-server
JD.
JD.

Reputation: 15541

Inserting into a table with sequence number

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.

Upvotes: 1

Views: 4753

Answers (5)

Heinzi
Heinzi

Reputation: 172270

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:

Upvotes: 2

usr
usr

Reputation: 171178

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

Upvotes: 1

Santosh Chaurasia
Santosh Chaurasia

Reputation: 113

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);

Upvotes: 1

Xardax99
Xardax99

Reputation: 181

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

Upvotes: 1

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

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.

Upvotes: 1

Related Questions