SQL Server Performance Forum – Threads Archive
Queue functionalityI am working on Queue functionality for my application. Queue is nothing but table where same record should not be processed by 2 different people/machines. To simplify consider table CREATE TABLE [dbo].[Table_1]( [Col1] [int] NULL, [Enabled] [bit] NULL ) ON [PRIMARY] I have procedure that picks up records and stores in table passed as input. Different apps running on different machines specify their local tables Create Procedure [dbo].[spTestQueue] @Tbl as varchar(100) AS Declare @No varchar(10) Select Top 1 @No= Cast(Col1 as varchar(10)) from Table_1(nolock) Where Enabled = 0 Update Table_1 Set Enabled =1 Where Col1 = Cast(@No as int) EXEC (‘Insert ‘ + @Tbl + ‘ values(‘ + @No + ‘)’) It worked fine during testing but there is nothing to prevent 2 machines to pick up same records.
This is very hghe transactional table.
How to efficiently implent locking or transcation to ensure that same record dose not get processed by 2 machines
How would you like to handle the concurrency? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Why not use a real queue via Service Broker? Or MSMQ?
I would also recommend using either service broker or MSMQ but if you don’t have the authority to install either of those then this table based solution is your best bet. You can modify your SP simply by adding BEGIN TRAN, COMMIT TRAN and the relevant locking type you want for the table:- Create Procedure [dbo].[spTestQueue] @Tbl as varchar(100) AS Declare @No varchar(10) BEGIN TRAN
Select Top 1 @No= Cast(Col1 as varchar(10)) from Table_1 (tablockx)
Where Enabled = 0 Update Table_1 Set Enabled =1 Where Col1 = Cast(@No as int)
EXEC (‘Insert ‘ + @Tbl + ‘ values(‘ + @No + ‘)’) GO But be warned, there are serious locking issues here that you must be aware of – read up on tablockx and other relevant lock types. You may be better with a less stringent locking mechanism – you’ll need to study up in BOL to figure this out – i think in your case you might be best with SERIALIZABLE level locking but with ROWLOCK applied though you might have to pull some trick to ensure they don’t always try and grab the same item. e.g. you could order by NEWID() to get a random row – this could reduce lock contention for that row. Depends if you care about the order of the queueing mechanism. NB. This wouldn’t be super-efficient if you had a large table as it will apply a NEWID() to every row, then sort, then give you the top 1 – so solution depends on number of factors.
MCSD, MCSE, MCDBA, MCITP, MCTS Add flight search to your website for free
You can use this lock combination in Queue functionality (XLOCK,READPAST) instead of using SERIALIZABLE isolation level. It worked on my case. Thanks, Name
Dilli Grg (1 row(s) affected)
SERIALIZABLE will be the best approach in this case…
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.