Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the next number in a sequence

I have a table like this:

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 2    | 29    |  |
| 4  | Shoulders | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
+----+-----------+------+-------+--+

How can I insert another record with the next seq after Stomach for Model 3. So here is what the new table suppose to look like:

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 2    | 29    |  |
| 4  | Shoulders | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
| 6  | Groin     | 6    | 3     |  |
+----+-----------+------+-------+--+

Is there a way to craft an insert query that will give the next number after the highest seq for Model 3 only. Also, looking for something that is concurrency safe.

like image 667
Luke101 Avatar asked Feb 08 '16 01:02

Luke101


People also ask

What is the next number in the sequence 1 2 3 4?

Step by step solution of the sequence is Series are based on square of a number 1 = 12, 4 = 22, 9 = 32, 16 = 42, 25 = 52 ∴ The next number for given series 1, 2, 3, 4, 5 is 6 ∴ Next possible number is 62 = 36

What is a sequence in math?

A Sequence is a set of things (usually numbers) that are in order. Each number in the sequence is called a term (or sometimes "element" or "member"), read Sequences and Series for a more in-depth discussion. To find a missing number, first find a Rule behind the Sequence. Sometimes we can just look at the numbers and see a pattern:

How do you find a missing number in a sequence?

Each number in the sequence is called a term (or sometimes "element" or "member"), read Sequences and Series for a more in-depth discussion. To find a missing number, first find a Rule behind the Sequence. Sometimes we can just look at the numbers and see a pattern:

Is it normal to use n in a sequence of numbers?

Its normal to use n in sequences. Questions will usually ask ‘find the nth’ term?’ We say we the first term in the sequence is n=1, then n=2 for the second, and so on. The general term then uses n in a formula.


1 Answers

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.

like image 60
TT. Avatar answered Oct 13 '22 20:10

TT.