July 31, 2014 at 2:11 pm
I have a table to store int primary keys. Where the value nextID is the value to use.
I need to get the value and update it (nextID + 1) before the next person gets it.
I thought using tran would work but it doesn't stop a select.
How do I get a value (lock the table for the update), update the table (preventing any selects until done) and release the table.
BEGIN TRAN
UPDATE TableIds
SET NextId = NextId + 1
WHERE TableName = 'Users'
WAITFOR DELAY '00:00:20'
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
COMMIT TRAN
I really want to do this in the reverse order but it doesn't work.
But if I do this and have another query to just do a select:
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
This query never returns. Doesn't the COMMIT TRAN release the lock on the table?
Thanks,
Tom
July 31, 2014 at 2:34 pm
This should do what you need.
Why do you have WAITFOR DELAY '00:00:20' in your sample code?
declare @NextId_output table ( NextId int not null )
UPDATE TableIds
SET
NextId = NextId + 1
output
inserted.NextId
into
@NextId_output
WHERE
TableName = 'Users'
select NextId from @NextId_output
July 31, 2014 at 2:52 pm
I was putting the waitfor in the query to test the locking and to see when it releases.
The only problem is that the 2nd query (select) never comes back.
If I run:
select cmd,* from sys.sysprocesses
where blocked > 0
It shows the 2nd query as being blocked and will stay blocked until I kill the blocking query (the first one). Why is it blocking the 2nd query after it finishes?
Thanks,
Tom
August 1, 2014 at 2:04 pm
Presumably because you still have an open transaction. Run SELECT @@trancount in the first window. Presumably you already had an open transaction when you issued the BEGIN TRANSACTION in your script. In this case BEGIN and COMMIT transactions only increase trancount; nothing is actually committed until trancount reaches zero.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 1, 2014 at 3:17 pm
A couple of questions.
You're in SQL 2014, and want to select an INT. Have you looked at SEQUENCE? It does exactly that.
If you want to lock rows for the duration of a transaction from being read, or better keep anyone else from getting into the table at all until you're done, SELECT * FROM tbl (TABLOCKX) WHERE NextID = 'Yes'. That'll open an exclusive table lock on the table for the transaction. Noone else is getting in there at that point until you commit or rollback.
If this is just a giant IDENTITY() table stuffed off to the side, why don't you simply put IDENTITY() on the tables that need it? What purpose is this table of IDs serving?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 1, 2014 at 3:25 pm
Evil Kraig F (8/1/2014)
You're in SQL 2014, and want to select an INT. Have you looked at SEQUENCE? It does exactly that.
Beware that sequence can give you gaps. Sometimes there are business requirements that calls for an contiguous series of numbers. In this case, you must roll your own.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 2, 2014 at 12:14 am
tshad (7/31/2014)
I have a table to store int primary keys. Where the value nextID is the value to use.I need to get the value and update it (nextID + 1) before the next person gets it.
I thought using tran would work but it doesn't stop a select.
How do I get a value (lock the table for the update), update the table (preventing any selects until done) and release the table.
BEGIN TRAN
UPDATE TableIds
SET NextId = NextId + 1
WHERE TableName = 'Users'
WAITFOR DELAY '00:00:20'
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
COMMIT TRAN
I really want to do this in the reverse order but it doesn't work.
But if I do this and have another query to just do a select:
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
This query never returns. Doesn't the COMMIT TRAN release the lock on the table?
Thanks,
Tom
If you insist on using a home-grown sequence table, the following will keep others from "getting in" at the same time and will also help you avoid a total world of hurt when it come to deadlocks.
DECLARE @NextId INT
UPDATE TableIds
SET @NextId = NextId = NextId + 1
WHERE TableName = 'Users'
;
SELECT @NextID
;
Unfortunately, that also relegates you to a single row at a time just as your original code did, Will you ever be doing inserts on, say, the "Users" table in multi-row batches?
Be aware that if the code that uses this has an explicit transaction around it, fails, and rolls back, you could also experience gaps.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2014 at 1:37 am
Just a quick thought on the problem, why not use the ROWLOCK hint?
To try it out, run the create script first and then the Update and the Select code simultaneously.
😎
Create table and insert sample data
USE tempdb;
GO
CREATE TABLE dbo.TableIds
(
TableIds_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TABLEIDS_TABLEIDS_ID PRIMARY KEY CLUSTERED
,NextId INT NOT NULL
,TableName NVARCHAR(128) NOT NULL
);
INSERT INTO dbo.TableIds (NextId,TableName)
VALUES
(1000,'Users')
,(1000,'Contacts')
,(1000,'Employees')
,(1000,'Addresses');
Update code
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @ITER INT = 0 ;
DECLARE @COUNT INT = 100000;
DECLARE @NextId INT = 0 ;
WHILE @ITER < @COUNT
BEGIN
UPDATE T WITH (ROWLOCK)
SET T.NextId = T.NextId + 1
FROM dbo.TableIds T
WHERE TableName = 'Users';
SELECT @NextId = NextId FROM dbo.TableIds;
SET @ITER += 1;
END
Select code
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @ITER INT = 0 ;
DECLARE @COUNT INT = 10000000;
DECLARE @BUCKET INT = 0 ;
WHILE @ITER < @COUNT
BEGIN
SELECT @BUCKET = NextId FROM dbo.TableIds;
SET @ITER += 1;
END
August 2, 2014 at 8:06 am
Evil Kraig F (8/1/2014)
If you want to lock rows for the duration of a transaction from being read, or better keep anyone else from getting into the table at all until you're done, SELECT * FROM tbl (TABLOCKX) WHERE NextID = 'Yes'. That'll open an exclusive table lock on the table for the transaction. Noone else is getting in there at that point until you commit or rollback.
Using tablockx in something like this can sometimes be a pain - it blocks all access to the table, including reads. Using a table hint to put the statement (not the transaction) into REPEATABLEREAD (or even SERIALIZABLE) mode as far as the locks taken on rows or pages in this table are concerned might be better, because it will only block access using locks that the engine decides of its own accord to take.
Tom
August 2, 2014 at 8:24 am
TomThomson (8/2/2014)
Evil Kraig F (8/1/2014)
If you want to lock rows for the duration of a transaction from being read, or better keep anyone else from getting into the table at all until you're done, SELECT * FROM tbl (TABLOCKX) WHERE NextID = 'Yes'. That'll open an exclusive table lock on the table for the transaction. Noone else is getting in there at that point until you commit or rollback.Using tablockx in something like this can sometimes be a pain - it blocks all access to the table, including reads. Using a table hint to put the statement (not the transaction) into REPEATABLEREAD (or even SERIALIZABLE) mode as far as the locks taken on rows or pages in this table are concerned might be better, because it will only block access using locks that the engine decides of its own accord to take.
Quick question, how about adding a covering index?
😎
August 2, 2014 at 9:11 am
Eirikur Eiriksson (8/2/2014)
Update codeGO
SET NOCOUNT ON;
DECLARE @ITER INT = 0 ;
DECLARE @COUNT INT = 100000;
DECLARE @NextId INT = 0 ;
WHILE @ITER < @COUNT
BEGIN
UPDATE T WITH (ROWLOCK)
SET T.NextId = T.NextId + 1
FROM dbo.TableIds T
WHERE TableName = 'Users';
SELECT @NextId = NextId FROM dbo.TableIds;
SET @ITER += 1;
END
Gosh... be careful. Without a transaction around the UPDATE and the SELECT, there is the possibility of someone getting in between which would cause the wrong ID to be returned. With such a transaction, you're asking for and average of hundreds of deadlocks per day with spikes in the thousands per day. I know this because the code above is almost exactly what a company that I previously worked for had. There were and average of 460 deadlocks per day with spikes to 4000. We fixed it by using the 3 part update like the one I posted.
Of course, we also included an "increment" to reserve large numbers of IDs to keep batch runs from being RBAR in nature but that also requires some tricks in the external code using Temp Tables to be both successful and fast.
The best thing to do is to NOT try to write your own NextID sequencer. Use IDENTITY or SEQUENCE They won't let you down except for gaps after a reboot or a rollback and you shouldn't expect otherwise there.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2014 at 10:20 am
Jeff Moden (8/2/2014)
Eirikur Eiriksson (8/2/2014)
Update codeUSE tempdb;GO
SET NOCOUNT ON;
DECLARE @ITER INT = 0 ;
DECLARE @COUNT INT = 100000;
DECLARE @NextId INT = 0 ;
WHILE @ITER < @COUNT
BEGIN
UPDATE T WITH (ROWLOCK)
SET T.NextId = T.NextId + 1
FROM dbo.TableIds T
WHERE TableName = 'Users';
SELECT @NextId = NextId FROM dbo.TableIds;
SET @ITER += 1;
END
Gosh... be careful. Without a transaction around the UPDATE and the SELECT, there is the possibility of someone getting in between which would cause the wrong ID to be returned. With such a transaction, you're asking for and average of hundreds of deadlocks per day with spikes in the thousands per day. I know this because the code above is almost exactly what a company that I previously worked for had. There were and average of 460 deadlocks per day with spikes to 4000. We fixed it by using the 3 part update like the one I posted.
Of course, we also included an "increment" to reserve large numbers of IDs to keep batch runs from being RBAR in nature but that also requires some tricks in the external code using Temp Tables to be both successful and fast.
The best thing to do is to NOT try to write your own NextID sequencer. Use IDENTITY or SEQUENCE They won't let you down except for gaps after a reboot or a rollback and you shouldn't expect otherwise there.
I get your point Jeff, in fact this was me on the first espresso:-) in the morning. Not suggesting that this is in any way, shape or form a good solution, rather an effort in gathering feedback like yours regarding using ROWLOCK. I tried this out using the script I posted and only got minor blocking, nothing serious, but then again the testing method is seriously flawed as it doesn't test for identical simultaneous operations. I also noticed that the update locks held where all escalated to a PAGELOCK.
In my opinion, which I should have stated earlier, I agree with NOT rolling your own sequencer, leave that to the SQL Server. Itzik Ben-Gan did a two part article on the subject, "Sequences, Part 1" and "Sequences, Part 2", well worth reading.
😎
August 2, 2014 at 10:26 am
Jeff Moden (8/2/2014)
The best thing to do is to NOT try to write your own NextID sequencer. Use IDENTITY or SEQUENCE They won't let you down except for gaps after a reboot or a rollback and you shouldn't expect otherwise there.
If you have a business requirements that there must not be gaps, they are out of the question.
The chief reason to use IDENTITY/SEQUENCE is that you need to support high concurrency. If that is on your concern, I see little point in using IDENTITY which seems to cause people more trouble than benefit. Sequence are less problematic.
If you need a range of values, there is of course little reason to iterate, but you can have code like:
BEGIN TRANSACTION
UPDATE idbtl
SET @firstid = nextid,
nextid = nextid + @no_of_ids_needed
-- Use @firstid to @firstid + @no_of_ids_needed - 1 here
COMMIT TRANSACTION
This is only in the case you have a separate id table. But you can also use the table itself:
BEGIN TRANSACTION
SELECT @nextid = coalesce(MAX(id), 0)
FROM tbl WITH (UPDLOCK)
INSERT tbl(id, ....)
SELECT @nextid + row_number() OVER(...), ....
FROM
COMMIT TRANSACTION
Both these approaces avoids gaps, but cause serilaisation and are bad for concurrency. If you have a requirement to support high concurrency and have contiguous numbers, you have a challenge!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 2, 2014 at 2:36 pm
I agree that SEQUENCE has the advantage of being able to cover multiple tables and that IDENTITY requires SET IDENTITY INSERT if you want to update the identity column but, other than that, how is SEQUENCE any less problematic than IDENTITY?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2014 at 2:43 pm
Jeff Moden (8/2/2014)
I agree that SEQUENCE has the advantage of being able to cover multiple tables and that IDENTITY requires SET IDENTITY INSERT if you want to update the identity column but, other than that, how is SEQUENCE any less problematic than IDENTITY?
You cannot update an IDENTITY column at all. You can set explicit values on insert, but if you for some reason want to update the values, you have a headache.
Also, if you find out later that you want gaps, and decide to roll your own, you can just drop the default that says NEXT VALUE FOR. Whereas with IDENTITY, you need to do the create-new-table/copy-over/drop-old/rename dance.
With sequences, you don't run the risk to have this pain.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply