February 8, 2019 at 2:04 am
I'm working with a 3rd party database where rows in each table are given a surrogate key (integer) by calling a stored procedure called nextSID; all this procedure does is increment a column called Last_Key on a control table and return the new value. I need to insert sets of rows into some of the tables during the day when other processes may be using nextSID. I know I could use a cursor to loop through the rows I want to insert and call the stored procedure for each row, but that doesn't seem ideal. I can access the control table directly, so I'm considering doing a one-off update of Last_Key to its current value plus 1 plus the number of rows I want to add at the beginning of my process. If I do this, I need to lock the table until all my rows have been added (we're talking about hundreds rather than thousands). I think I should do the update using the TABLOCK hint so that other processes can't use it until I've finished. Is this true and, if so, is it the best way to approach the problem or are there better alternatives?
TIA
February 8, 2019 at 3:39 am
Chris Wooding - Friday, February 8, 2019 2:04 AMI'm working with a 3rd party database where rows in each table are given a surrogate key (integer) by calling a stored procedure called nextSID; all this procedure does is increment a column called Last_Key on a control table and return the new value. I need to insert sets of rows into some of the tables during the day when other processes may be using nextSID. I know I could use a cursor to loop through the rows I want to insert and call the stored procedure for each row, but that doesn't seem ideal. I can access the control table directly, so I'm considering doing a one-off update of Last_Key to its current value plus 1 plus the number of rows I want to add at the beginning of my process. If I do this, I need to lock the table until all my rows have been added (we're talking about hundreds rather than thousands). I think I should do the update using the TABLOCK hint so that other processes can't use it until I've finished. Is this true and, if so, is it the best way to approach the problem or are there better alternatives?
TIA
Add a new optional param to nextSID (@NumSids int = 1). Then in nextSID, replcae the "Last_Key + 1" with "Last_Key + @NumSids".
Now use nextSID as normal. Except, when you get your next number back, remember that it is the biggest value in the range, and you need to calc all the numbers from NextSid - @NumSids to NextSid.
February 8, 2019 at 3:51 am
Some sample SQL to find the list of SIDs that you can useDECLARE @NumSids int = 5; -- This is the number of new SIDs that you want to generate (MAX 10,000)
DECLARE @NextSid int = 103; -- This is the SID that nextSID returned
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
cteTally(N) AS (SELECT TOP (ISNULL(@NumSids, 1)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
SELECT ThisSid = @NextSid - ISNULL(@NumSids, 1) + N -- This is a list of numbers that you can use
FROM cteTally;
February 8, 2019 at 3:55 am
Thanks, but I don't think I can amend the 3rd party procedure and I'm not sure this is significantly different from updating the table directly with +1 +@NumSids. With either of these methods, should I use TABLOCK to hold the values until my inserts are complete?
February 8, 2019 at 4:13 am
Chris Wooding - Friday, February 8, 2019 3:55 AMThanks, but I don't think I can amend the 3rd party procedure and I'm not sure this is significantly different from updating the table directly with +1 +@NumSids. With either of these methods, should I use TABLOCK to hold the values until my inserts are complete?
You can just update the table directly. But no need for TABLOCK, as you are only doing a single update. You then use the numbers as needed for the insert into your destination table
February 8, 2019 at 4:24 am
DesNorton - Friday, February 8, 2019 4:13 AMChris Wooding - Friday, February 8, 2019 3:55 AMThanks, but I don't think I can amend the 3rd party procedure and I'm not sure this is significantly different from updating the table directly with +1 +@NumSids. With either of these methods, should I use TABLOCK to hold the values until my inserts are complete?You can just update the table directly. But no need for TABLOCK, as you are only doing a single update. You then use the numbers as needed for the insert into your destination table
The thing that bothers me is that my insert could fail after I've updated the Last_Key, but between my update and my insert, someone else could have used the nextSID procedure for an insert of their own. They'd have received my last value +1 and used it, then my transaction would roll back. I don't know whether gaps in the SID values affect anything in the database adversely.
February 8, 2019 at 4:53 am
Chris Wooding - Friday, February 8, 2019 4:24 AMDesNorton - Friday, February 8, 2019 4:13 AMChris Wooding - Friday, February 8, 2019 3:55 AMThanks, but I don't think I can amend the 3rd party procedure and I'm not sure this is significantly different from updating the table directly with +1 +@NumSids. With either of these methods, should I use TABLOCK to hold the values until my inserts are complete?You can just update the table directly. But no need for TABLOCK, as you are only doing a single update. You then use the numbers as needed for the insert into your destination table
The thing that bothers me is that my insert could fail after I've updated the Last_Key, but between my update and my insert, someone else could have used the nextSID procedure for an insert of their own. They'd have received my last value +1 and used it, then my transaction would roll back. I don't know whether gaps in the SID values affect anything in the database adversely.
If gaps are not allowed, then you have to process your records one by one within a transaction. Alternatively, you can do all your processing in a single transaction.
Either way, that is going to have terrible performance.
February 8, 2019 at 8:34 am
Chris Wooding - Friday, February 8, 2019 2:04 AMI'm working with a 3rd party database where rows in each table are given a surrogate key (integer) by calling a stored procedure called nextSID; all this procedure does is increment a column called Last_Key on a control table and return the new value. I need to insert sets of rows into some of the tables during the day when other processes may be using nextSID. I know I could use a cursor to loop through the rows I want to insert and call the stored procedure for each row, but that doesn't seem ideal. I can access the control table directly, so I'm considering doing a one-off update of Last_Key to its current value plus 1 plus the number of rows I want to add at the beginning of my process. If I do this, I need to lock the table until all my rows have been added (we're talking about hundreds rather than thousands). I think I should do the update using the TABLOCK hint so that other processes can't use it until I've finished. Is this true and, if so, is it the best way to approach the problem or are there better alternatives?
TIA
Why not use a sequence? It's always one by one, but you don't really need a stored procedure to get the next value, and gaps are really irrelevant. Such ID values are rarely particularly relevant to any other data in the database, so why care about gaps? If the only purpose that number serves is as a primary key, then gaps are irrelevant.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 8, 2019 at 8:43 am
sgmunson - Friday, February 8, 2019 8:34 AMChris Wooding - Friday, February 8, 2019 2:04 AMI'm working with a 3rd party database where rows in each table are given a surrogate key (integer) by calling a stored procedure called nextSID; all this procedure does is increment a column called Last_Key on a control table and return the new value. I need to insert sets of rows into some of the tables during the day when other processes may be using nextSID. I know I could use a cursor to loop through the rows I want to insert and call the stored procedure for each row, but that doesn't seem ideal. I can access the control table directly, so I'm considering doing a one-off update of Last_Key to its current value plus 1 plus the number of rows I want to add at the beginning of my process. If I do this, I need to lock the table until all my rows have been added (we're talking about hundreds rather than thousands). I think I should do the update using the TABLOCK hint so that other processes can't use it until I've finished. Is this true and, if so, is it the best way to approach the problem or are there better alternatives?
TIAWhy not use a sequence? It's always one by one, but you don't really need a stored procedure to get the next value, and gaps are really irrelevant. Such ID values are rarely particularly relevant to any other data in the database, so why care about gaps? If the only purpose that number serves is as a primary key, then gaps are irrelevant.
I can't change the way the database works. All its other processes use nextSID/Last_Key, so I have to follow suit. I suspect that gaps are irrelevant, but can't be certain because it's a 3rd party product.
February 8, 2019 at 8:47 am
Chris Wooding - Friday, February 8, 2019 8:43 AMsgmunson - Friday, February 8, 2019 8:34 AMChris Wooding - Friday, February 8, 2019 2:04 AMI'm working with a 3rd party database where rows in each table are given a surrogate key (integer) by calling a stored procedure called nextSID; all this procedure does is increment a column called Last_Key on a control table and return the new value. I need to insert sets of rows into some of the tables during the day when other processes may be using nextSID. I know I could use a cursor to loop through the rows I want to insert and call the stored procedure for each row, but that doesn't seem ideal. I can access the control table directly, so I'm considering doing a one-off update of Last_Key to its current value plus 1 plus the number of rows I want to add at the beginning of my process. If I do this, I need to lock the table until all my rows have been added (we're talking about hundreds rather than thousands). I think I should do the update using the TABLOCK hint so that other processes can't use it until I've finished. Is this true and, if so, is it the best way to approach the problem or are there better alternatives?
TIAWhy not use a sequence? It's always one by one, but you don't really need a stored procedure to get the next value, and gaps are really irrelevant. Such ID values are rarely particularly relevant to any other data in the database, so why care about gaps? If the only purpose that number serves is as a primary key, then gaps are irrelevant.
I can't change the way the database works. All its other processes use nextSID/Last_Key, so I have to follow suit. I suspect that gaps are irrelevant, but can't be certain because it's a 3rd party product.
And what if, under the covers, that sproc is actually using a sequence? You would never succeed with a method that ever tried to use any number other than the one provided. Given that this is a third-party application, and they expect you to use the sproc, I think you have to respect that process or risk failure. How would you ensure that the sproc would know about your use of more than one number?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 8, 2019 at 9:16 am
The code in the sproc just does an update of column Last_Key on the one row in that table and returns the new value. That's not a real sequence is it?
February 8, 2019 at 9:26 am
Chris Wooding - Friday, February 8, 2019 9:16 AMThe code in the sproc just does an update of column Last_Key on the one row in that table and returns the new value. That's not a real sequence is it?
No. It's not. It's also not necessarily a "serializable" action. A genuine sequence solves that problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 8, 2019 at 9:33 am
Chris Wooding - Friday, February 8, 2019 9:16 AMThe code in the sproc just does an update of column Last_Key on the one row in that table and returns the new value. That's not a real sequence is it?
Chris... Post the existing code in the spoc and I'll show you two things... 1) is how to prevent the deadlocks that such things normally cause and 2) how to write a separate proc to reserve a contiguous set of IDs, also without the chance for deadlocks.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 11, 2019 at 1:34 am
Here's the next key table.create table SEQ_SYSTEMKEY
(LASTKEY int null) on [PRIMARY];
and here's the current stored procedure.create procedure nextSID
as
declare @sid int;
update SEQ_SYSTEMKEY
set LASTKEY = LASTKEY + 1;
select @sid = LASTKEY
from DOCSADM.SEQ_SYSTEMKEY;
return @sid;
Bear in mind that I don't know whether gaps in the IDs affect the application at all, ie. if my transaction rolls back after someone else has taken the next ID. I'm probably worrying about that unnecessarily, but I can't be sure.
February 11, 2019 at 8:05 am
Chris Wooding - Monday, February 11, 2019 1:34 AMHere's the next key table.create table SEQ_SYSTEMKEY
(LASTKEY int null) on [PRIMARY];
and here's the current stored procedure.create procedure nextSID
as
declare @sid int;update SEQ_SYSTEMKEY
set LASTKEY = LASTKEY + 1;select @sid = LASTKEY
from DOCSADM.SEQ_SYSTEMKEY;return @sid;
Bear in mind that I don't know whether gaps in the IDs affect the application at all, ie. if my transaction rolls back after someone else has taken the next ID. I'm probably worrying about that unnecessarily, but I can't be sure.
That's going to be prone to the occasional problem of the proc doing an update and someone else also doing an update between the time the original update and select is done. It'll be a bitch when it happens. 😉
Now, a lot of well meaning folks will say that all you need to do to fix that problem is to encapsulate the UPDATE and SELECT in an explicit transaction. Don't even consider doing such a thing. Been there and done that with a 3rd party system in the past and I can guarantee that you'll have hundreds and sometimes thousands of deadlocks in an 8 hour period. As you've also noticed, it doesn't allow you to reserve a range of IDs.
I 'll take a look in my archives for the code that fixes all of that. If I can't find it, I'll try to remember to rewrite it and post it after work tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply