March 26, 2009 at 6:28 am
I am using a Table which contains only one row and i.e. of a Counter.
Users pick the value from the table and update table with an incriment
i.e.
BEGIN TRAN
SELECT Counter FROM CounterTable WITH (XLOCK)
UPDATE CounterTable
SET Counter = Counter+1
COMMIT
but when multiple users try to get values from it, they get blocked.
Which Locking mechanism is best in this scene ?
I am facing lots of blocking and the whole process gets too slow.
need help !
Musab
http://www.sqlhelpline.com
March 26, 2009 at 7:47 am
It may be best if you re-think your design.
If you really want to do this then:
1. Less Blocking
DECLARE @Counter int
-- No need for explicit transactions
UPDATE CounterTable
SET @Counter = Counter = Counter + 1
SELECT @Counter
2. No Blocking but may be gaps
CREATE TABLE dbo.CounterTable
(
-- initialize appropriately
Counter int IDENTITY(1,1) NOT NULL
)
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.GetNextCounter
(
@Counter int OUTPUT
)
AS
BEGIN TRAN
SAVE TRANSACTION Counter
INSERT INTO dbo.CounterTable DEFAULT VALUES
SELECT @Counter = SCOPE_IDENTITY()
ROLLBACK TRANSACTION Counter
-- Decrement @@TRANCOUNT
COMMIT
GO
DECLARE @Counter int
EXEC dbo.GetNextCounter @Counter OUTPUT
SELECT @Counter
ps There may be some slight blocking with option 2 but it will occur outside of any transactions you have open.
March 26, 2009 at 8:06 am
First of All I am really grateful to see your valuable reply.
If multiple users come at the same time and there is no explicit BEGIN so multiple users will get the same ID and I need to have unique ID for all the users. Means every user must get a unique ID.
As ids generated by this tables will be used to get some data from other tables. and If multiple users will get the same ID then there will be duplications. so i need a solution in which all the users get unique ids and they will access the table at the same time.
Please suggest me some solution.
Musab
http://www.sqlhelpline.com
March 26, 2009 at 8:17 am
musab (3/26/2009)
Please suggest me some solution.
I have.
March 26, 2009 at 8:20 am
Thankyou Very much ! I have got it. Its a great pleasure for me.
Thanks again.
Musab
http://www.sqlhelpline.com
March 26, 2009 at 8:47 am
Glad to be of help.
If this is an internet application it is common practice to assign each user an uniqueidentifier and use the NEWID() function.
If you really want an integer, another strategy is to pre-populate a list of counters and then get the MIN(Counter) with the READPAST locking hint. The counter is deleted once it has been used.
March 26, 2009 at 8:52 am
Even Better !
you are a really great Help for me. I will be glad to discuss more thing with you.
Thanks again !
Musab
http://www.sqlhelpline.com
March 27, 2009 at 6:23 am
Just a side note, it's almost always a last resort to start applying blocking through hints. SQL Server is generally much more capable at handling it's locking and blocking processes than any of us are. Focus on well designed structures and well formed queries and let the locking be handled by the data engine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2009 at 3:57 am
One other possibility if you really need to go down this road:
CREATE TABLE dbo.IDs
(
ApplicationName VARCHAR(30) PRIMARY KEY CLUSTERED,
Counter INT NOT NULL
)
-- If more than one counter is required...
INSERT dbo.Ids (ApplicationName, Counter) VALUES ('Test1', 1);
INSERT dbo.Ids (ApplicationName, Counter) VALUES ('Test2', 1);
INSERT dbo.Ids (ApplicationName, Counter) VALUES ('Test3', 1);
DECLARE @Value INT;
DECLARE @T TABLE (Counter INT NOT NULL)
-- inserted.Counter can be used if you want the 'after update' value
UPDATE dbo.Ids WITH (ROWLOCK)
SET Counter = Counter + 1
OUTPUT deleted.Counter INTO @T
WHERE ApplicationName = 'Test1';
SELECT @Value = Counter FROM @T
I just mention it for completeness. As far as I know, Ken's method is more efficient:
UPDATE CounterTable
SET @Counter = Counter = Counter + 1
WHERE ApplicationName = 'Test1';
OR obviously,
UPDATE CounterTable
SET @Counter = Counter, Counter = Counter + 1
WHERE ApplicationName = 'Test1';
... if you want the 'before update value'
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply