Generate a Unique Column Sequence Value Based on a Query Handling Concurrency

Generate Next Sequence When No Value is Provided

Define a Sequence object in your database and when no value has been explicitly provided to the insert statement, retrieve the next value from the Sequence to insert instead. The logic for this can be implemented in a trigger.

https://msdn.microsoft.com/en-us/library/ff878058.aspx

As others have suggested, an Identity column would better though.

How to create a sequence number based on database registers with concurrent users

@Transactional
public void createB(informations){

A a = informations.getA()
session.get(A.class, a.getId(), LockMode.READ) //BD Lock here, no cluster, no Java

//Only who got the lock first get here, anyone else waits for the lock release

Long lastSequential = bService.findLastSequential()
B newB = informations.getB()
newB.setSequential(lastSequential + 1)
session.persist(newB)

//The lock is released when the transaction finishes and then the next
//thread awaiting on the session.get may get a new lock and resume its
//execution. That way only one thread at time will be able to execute
//the critical area where the sequential is being created.
}

How to get the next number in a sequence

If you do not maintain a counter table, there are two options. Within a transaction, first select the MAX(seq_id) with one of the following table hints:

  1. WITH(TABLOCKX, HOLDLOCK)
  2. WITH(ROWLOCK, XLOCK, HOLDLOCK)

TABLOCKX + HOLDLOCK is a bit overkill. It blocks regular select statements, which can be considered heavy even though the transaction is small.

A ROWLOCK, XLOCK, HOLDLOCK table hint is probably a better idea (but: read the alternative with a counter table further on). The advantage is that it does not block regular select statements, ie when the select statements don't appear in a SERIALIZABLE transaction, or when the select statements don't provide the same table hints. Using ROWLOCK, XLOCK, HOLDLOCK will still block insert statements.

Of course you need to be sure that no other parts of your program select the MAX(seq_id) without these table hints (or outside a SERIALIZABLE transaction) and then use this value to insert rows.

Note that depending on the number of rows that are locked this way, it is possible that SQL Server will escalate the lock to a table lock. Read more about lock escalation here.

The insert procedure using WITH(ROWLOCK, XLOCK, HOLDLOCK) would look as follows:

DECLARE @target_model INT=3;
DECLARE @part VARCHAR(128)='Spine';
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @max_seq INT=(SELECT MAX(seq) FROM dbo.table_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE model=@target_model);
IF @max_seq IS NULL SET @max_seq=0;
INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@max_seq+1,@target_model);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

An alternative and probably a better idea is to have a counter table, and provide these table hints on the counter table. This table would look like the following:

CREATE TABLE dbo.counter_seq(model INT PRIMARY KEY, seq_id INT);

You would then change the insert procedure as follows:

DECLARE @target_model INT=3;
DECLARE @part VARCHAR(128)='Spine';
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @new_seq INT=(SELECT seq FROM dbo.counter_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE model=@target_model);
IF @new_seq IS NULL
BEGIN SET @new_seq=1; INSERT INTO dbo.counter_seq(model,seq)VALUES(@target_model,@new_seq); END
ELSE
BEGIN SET @new_seq+=1; UPDATE dbo.counter_seq SET seq=@new_seq WHERE model=@target_model; END
INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@new_seq,@target_model);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

The advantage is that fewer row locks are used (ie one per model in dbo.counter_seq), and lock escalation cannot lock the whole dbo.table_seq table thus blocking select statements.

You can test all this and see the effects yourself, by placing a WAITFOR DELAY '00:01:00' after selecting the sequence from counter_seq, and fiddling with the table(s) in a second SSMS tab.


PS1: Using ROW_NUMBER() OVER (PARTITION BY model ORDER BY ID) is not a good way. If rows are deleted/added, or ID's changed the sequence would change (consider invoice id's that should never change). Also in terms of performance having to determine the row numbers of all previous rows when retrieving a single row is a bad idea.

PS2: I would never use outside resources to provide locking, when SQL Server already provides locking through isolation levels or fine-grained table hints.

Test the sequentiality of a column with a single SQL query

Look for sequences where max - min + 1 > count:

IF EXISTS (SELECT set_ID
FROM mytable
GROUP BY set_ID
HAVING MAX(n) - MIN(n) + 1 > COUNT(n)
)
ROLLBACK

If the sequence must start at 1, do this instead:

IF EXISTS (SELECT set_ID
FROM mytable
GROUP BY set_ID
HAVING MIN(n) = 1 AND MAX(n) > COUNT(n)
)
ROLLBACK

You also need to avoid duplicate sequence numbers. But this can be done by creating a unique key on set_ID and n.

SQL Server Custom Identity Column

Try this:

BEGIN TRAN

INSERT INTO TBLKEY
VALUES((SELECT MAX(ID) + 1 AS NVARCHAR) FROM TBLKEY WITH (UPDLOCK)),'EHSAN')

COMMIT

When selecting the max ID you acquire a U lock on the row. The U lock is incompatible with the U lock which will try to acquire another session with the same query running at the same time. Only one query will be executed at a given time. The ids will be in order and continuous without any gaps between them.

A better solution would be to create an extra table dedicated only for storing the current or next id and use it instead of the maximum.

You can understand the difference by doing the following:

Prepare a table

CREATE TABLE T(id int not null PRIMARY KEY CLUSTERED)
INSERT INTO T VALUES(1)

And then run the following query in two different sessions one after another with less than 10 seconds apart

BEGIN TRAN
DECLARE @idv int
SELECT @idv = max (id) FROM T
WAITFOR DELAY '0:0:10'
INSERT INTO T VALUES(@idv+1)
COMMIT

Wait for a while until both queries complete. Observe that one of them succeeded and the other failed.

Now do the same with the following query

BEGIN TRAN
DECLARE @idv int
SELECT @idv = max (id) FROM T WITH (UPDLOCK)
WAITFOR DELAY '0:0:5'
INSERT INTO T VALUES(@idv+1)
COMMIT

View the contents of T

Cleanup the T Table with DROP TABLE T

Best way to generate unique and consecutive numbers in Oracle

The gaps appear if a transaction uses a sequence number but is then rolled back.

Maybe the answer is not to assign the invoice number until the invoice can't be rolled back. This minimizes (but probably does not eliminate) the possibilities of gaps.

I'm not sure that there is any swift or easy way to ensure no gaps in the sequence - scanning for MAX, adding one, and inserting that is probably the closest to secure, but is not recommended for performance reasons (and difficulties with concurrency) and the technique won't detect if the latest invoice number is assigned, then deleted and reassigned.

Can you account for gaps somehow - by identifying which invoice numbers were 'used' but 'not made permanent' somehow? Could an autonomous transaction help in doing that?


Another possibility - assuming that gaps are relatively few and far between.

Create a table that records sequence numbers that must be reused before a new sequence value is grabbed. Normally, it would be empty, but some process that runs every ... minute, hour, day ... checks for gaps and inserts the missed values into this table. All processes first check the table of missed values, and if there are any present, use a value from there, going through the slow process of updating the table and removing the row that they use. If the table is empty, then grab the next sequence number.

Not very pleasant, but the decoupling of 'issuing invoice numbers' from 'scan for missed values' means that even if the invoicing process fails for some thread when it is using one of the missed values, that value will be rediscovered to be missing and re-reissued next time around - repeating until some process succeeds with it.



Related Topics



Leave a reply



Submit