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?
ROW_NUMBER
when you select? What do you want to happen if a row gets deleted?Max(L.Id)
in my View?left join
returning no rows. And just remove theLineitems.id
column completely, just calculate it on the flysequence
can never guarantee that there are no holes (e.g. because of rollbacks)