0

I want a – can I call it a sub-sequence ? – to generate identity for child lines. 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

  • My application inserts rows into a child table for order LineItems.
  • The application requires LineItems be numbered consecutively from 1
  • The LineItems table itself also has child tables, in this case Variation.
  • After insert, I return the inserted row with the new sequential LineItemId
  • The app doesn't allow deletions

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?
5
  • Is ther any reason you don;t just use ROW_NUMBER when you select? What do you want to happen if a row gets deleted? Oct 26, 2021 at 0:32
  • Yes, good question: Rows may not be deleted, they can be moved to status cancelled, I'll add that. Row_number() would replace Max(L.Id) in my View? Oct 26, 2021 at 11:16
  • Yes it would, although you would have to take into account the left join returning no rows. And just remove the Lineitems.id column completely, just calculate it on the fly Oct 26, 2021 at 11:17
  • A sequence can never guarantee that there are no holes (e.g. because of rollbacks)
    – user1822
    Oct 26, 2021 at 14:53
  • indeed! So I need something else. A REPEATABLE READ transaction is the best I've got soo far? Oct 27, 2021 at 16:20

1 Answer 1

0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.