0

I want a – can I call it a sub-sequence ? – to generate gapless identities in tables with many rows per parentId.

A sequence per parent entity, starting at 1 with no holes. That is, each parent's childrens Ids should start at 1 and increment. What is a simple and reliable solution?

Detail, if it helps

In my case, the parent is a Job, the children are LineItems. (sometimes the parent is a Tenant, but it's the same problem).

  • I insert rows into LineItems for each Job.
  • The spec requires LineItems be numbered consecutively from 1
  • The app does not allow deletions
  • The LineItems table itself also has child tables, in this case Variation, and I will have to insert my gapless sequence id into that table too.
  • After insert, I return the inserted row with the new sequential LineItemId

Current code (the @ symbols marks input parameters):

Insert Into Lineitems (Jobid,Id,Stage, Costprice, Sellprice, Customdescription)
        Values (@JobId,
        -- *Calculate next line id for this jobid*
        @Stage,@Costprice,@Sellprice,@Customdescription)
;
Insert Into Variations (Lineitemjobid,Lineitemid,InstructedBy,InstructionDate,Type)
    Values (@jobId,
           *Use the just-inserted line id for this jobid*
           @Instructedby, @Instructiondate, @Variation_Type)
;
Select * from ...etc... 
Where (JobId,Id)=(@jobId, *Use the just-inserted line id for this jobid*)

The places marked *Calculate...* & *Use...* are where I want my 1-based, no-holes, per-parent, subsequence.

My First Effort

I create a view to get currentval/next val

Create View Lineitemidseq(Jobid, Currentval, Nextval) As
        Select Jobs.Id                    As Jobid,
               Coalesce(Max(L.Id), 0)     As Currentval,
               1 + Coalesce(Max(L.Id), 0) As Nextval
        From Jobs Left Join Lineitems L On Jobs.Id = L.Jobid
        Group By Jobs.Id

And then I can fill in my calculations for next lineid & last-inserted-in-this-session-lineid:

Next : (Select Nextval from Lineitemidseq Where JobId=@jobId)

Last-inserted-in-this-session : (Select Currentval from Lineitemidseq Where JobId=@jobId)

You can see the flaw, albeit at low probability. Because I have a child of the child, if 2 people add lines simultaneously, I could pick up a wrong CurrentVal in statements 2 & 3.

So:

  • Presumably I could fix this with an explicit lock for the 3-line transaction. Is there a better way?
  • If not, what is a reasonably minimalist lock that would provide correctness for such a small transaction?
Chris F Carroll
  • 214
  • 2
  • 10

1 Answers1

1

So the simplest thing I can see is to put the 3 lines inside:

Begin Transaction Isolation Level REPEATABLE READ
;
Insert ...
Insert ...
Select ...
;
Commit

Repeatable Read is the level that guarantees that

(Select ... From Lineitemidseq Where JobId=@jobId)

in statements 2 and 3 is consistent with

(Select ... From Lineitemidseq Where JobId=@jobId)

in statement 1.

Where “consistent” means, unaffected by any concurrent transaction.

This amount of locking seems like the bare minimum (or close to) that can do the job.

Sequences don't promise to create gapless sequences. Using a view does look to be the best-known way to do it.

Create View GaplessIdPerTenant(tenantId, currentVal, nextVal) As
        Select tenantId ,
               Coalesce(Max(Id), 0)     As currentVal,
               1 + Coalesce(Max(Id), 0) As nextVal
        From tenantedTable t
        Group By tenantId
Chris F Carroll
  • 214
  • 2
  • 10