How To Prevent Table-locking With Multiple Queue Readers
Apr 26, 2007
In a situation where messages are coming in faster than they can be processed, at what point will service broker start up another queue_reader? Also, how do you prevent table locking if part of the processing of that message involves inserting or updating data in a table? We are experiencing this problem because of the high number of messages coming through, and I'm not sure what the best solution is - does service broker have some built-in support for preventing contention on a table when multiple readers are running? Or maybe a pattern that can be used to get around it?
View 1 Replies
ADVERTISEMENT
Jan 11, 2006
Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
View 5 Replies
View Related
Apr 7, 2004
Hi all
We are writing a web-based multi-user call centre application application.
we are getting concurrency problems as you would expect with a multiuser application.
the application is made for callers who will bring up a different contact to call based on some predefined priority. now because the algorithm that prioritises the contacts takes a good 2 seconds to run, if 2 different caller request for the next prioritised contact, they will retrieve the same contact.
The only way that we think can resolve this problem is by building a queue. The queue would be implemented as a table, the particular implementation of this queue would be, when ever someone retrieves an entry from the queue, a background process will go on and generate a new queued item, i.e. in a FIFO manner. So that's how we think we should implement the queue.
Now come the question how to implement it. My idea is to have row level locking and a trigger to remove queue items from the queue. so that once one caller have looked at one of the item in the queue, another user can't look at the same item.
Any suggestions as to how i might be able to avoid concurrency problems?
What do you all think of my idea of implementing the FIFO queue?/
Is it possible to do row level locking in such a way that other users won't even be able to read the locked entry??
James :)
View 2 Replies
View Related
May 2, 2008
Hi,
I found out that executing the procedure SP_INDEXOPTION and setting 'AllowRowLocks' to false i can prevent the sql server from locking rows in a table and 'AllowPageLocks' prevents from pages being locked. I need to preform same operation
in case of tables. I need to perform insertion operations concurrently and acquire required locks manually. Is there a way to stop sqlserver from acquiring locks on the table. I need to disable all the locks (row, page and table).
Thank you in advance.
View 9 Replies
View Related
May 26, 2008
Hello All,
Greetings!!!
Due to certain constraints on my Table, I am not able to place unique Key constraint on it.
So I have take care about the uniqueness in my T-SQL code. I don't want to use the serializable transaction isolation level as it will result in frequent deadlocks.
I want some means by which one select will block other select.
Can this be done in SQL Server. I tried using TablockX along with holdlock as Table hint, but still the Selects does not block other select.
Will appreciate if you can resolve this issue
Thanks in Advance,
Mitesh Shah
MCTS- SQL Server 2005
View 4 Replies
View Related
Jul 20, 2005
Need some information regarding SQL Server locking.Requirement is to run SQL Statements for reports (read-only) that willnot lock-out other people trying to update (write lock). For this, wetried the following to make the db connection not deny write locks(our report code will not have a read-only lock).ADODB.ConnectionObj.Mode =adModeShareDenyNoneBut this made our connection bumped whenever someone tried to update.Tried enabling dirty reads by doing:ADODB.ConnectionObj.IsolationLevel =adXactBrowseEven this made our connection bumped whenever someone tried to update.Is there a way to achieve what is needed without having a seperatedatabase for reports?
View 1 Replies
View Related
Apr 25, 2008
This article instructed me on how to process rows from a table used as a data queue for multiple processes.
http://www.mssqltips.com/tip.asp?tip=1257
I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio).
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
COMMIT TRANSACTION
This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue. However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want)
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --is blocked until connection 1 commits transaction
COMMIT TRANSACTION
How do I prevent blocking when using these locking hints with ORDER BY?
thanks
View 6 Replies
View Related
Oct 11, 2006
Hi There
I am guessing defining multiple services on the same queue is basically for providing different services to outside sources while using 1 queue, obviously as long as the contracts and activated sp logic applies to all services defined on that queue.
I am defining a queue per service, is this right or wrong or irrelevant ? It just works better for me in terms of manageability.
I just want to make sure there is no "best practice" reason for doing either or ? Is it just a matter of preference?
Any comments?
Thanx
View 3 Replies
View Related
Mar 13, 2002
I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?
Many thanks for advice.
View 2 Replies
View Related
Aug 14, 2007
Hi,
I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().
I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.
My dequeue SP is as follows:
CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;
GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END
if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END
Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?
View 11 Replies
View Related
Jan 9, 2007
When multiple readers are waiting on a message from the same queue, I would expect that the reader that has been waiting the longest would be the first to pick up a message. However, I'm shocked to discover that the opposite seems to be true; in my tests I'm showing that the reader that has been waiting the least time picks up a message first! This seems totally counter-intuitive, and I'd like to know why it's working this way. This implementation will cause a lot more reader timeouts to occur than a properly queued method. For instance, assume that I have two readers, each using a one minute timeout. Reader #1 starts waiting, and reader #2 is busy for another 20 seconds before it starts waiting. 39 seconds later a message comes in and reader #2 will pick it up, leaving reader #1 to time out one second later! I would much rather have reader #1 pick up the message and reader #2 continue to wait for 20 more seconds.
I'm considering filing a bug on Connect about this, but I thought I'd post here first and see if I can get an answer...
Following is the script I'm using to test:
---Setup / Window #1---
--------------------------------------
CREATE DATABASE SimpleSSB
GO
USE SimpleSSB
GO
--Create a database master key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'onteuhoeu'
GO
--Create a message type
CREATE MESSAGE TYPE Simple_Msg
VALIDATION = EMPTY
GO
--Create a contract based on the message type
CREATE CONTRACT Simple_Contract
(Simple_Msg SENT BY INITIATOR)
GO
--create a queue
CREATE QUEUE Simple_Queue
GO
--Create a service
CREATE SERVICE Simple_Service
ON QUEUE Simple_Queue
(Simple_Contract)
GO
--------------------------------------
---Go start the other windows now---
---Readers: Windows #2-n ---
--------------------------------------
USE SimpleSSB
GO
WAITFOR
(
RECEIVE *
FROM Simple_Queue
), TIMEOUT 300000
--------------------------------------
---Start at least two readers, then do---
--------------------------------------
--send a message...
DECLARE @h UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @h
FROM SERVICE Simple_Service
TO SERVICE 'Simple_Service'
ON CONTRACT Simple_Contract
WITH ENCRYPTION=OFF;
SEND ON CONVERSATION @h
MESSAGE TYPE Simple_Msg
GO
--------------------------------------
... the last reader you've started will pick up the message first. Note I'm testing on 9.0.3033, in case that matters.
Thanks!
View 7 Replies
View Related
Nov 22, 2002
I have a table that I want to act as a queue.
It has no indexes and no key. Just one column.
Basically I want a stored procedure that will pull / return the first record off the queue (table) and delete it. I'd rather not use MSMQ for this.
There will be about 10 users trying to do this at the same time and will be trying to pull of about 15 times every second.
How can I do this and ensure that no two requests pull off the same row?
Thanks,
Kevin
View 1 Replies
View Related
Jul 20, 2005
I am trying to implement a very fast queue using SQL Server.The queue table will contain tens of millions of records.The problem I have is the more records completed, the the slower itgets. I don't want to remove data from the queue because I use thesame table to store results. The queue handles concurrent requests.The status field will contain the following values:0 = Waiting1 = Started2 = FinishedAny help would be greatly appreciated.Here is a simplified script to demonstrate what has been done.CREATE TABLE [dbo].[Queue] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[JobID] [int] NOT NULL ,[Status] [tinyint] NOT NULL) ON [PRIMARY]GOCREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]GOCREATE PROCEDURE dbo.NextItem@JobID integer,@ID integer outputASSELECT TOP 1 @ID = [ID]FROM Queue WITH (READPAST, XLOCK)WHERE (Status = 0) AND (JobID = @JobID)RETURNGO
View 6 Replies
View Related
Jul 26, 2006
I know we are not allowed to benchmark SQL Server but..... It would be nice to have material to present which demonstrates the performance gains using a queue compared to insert/delete in a SQL table.
Logically it seems faster to use a queue due to the conversation grouping locking and the service broker itself. But there seems to be some overhead involved just to manage these queues that the service broker has to perform.
I am sure we are not unique with the choice to figure out if we will get a boost in performance using SQL a queue between services rather than a table to queue data. What is available to help understand the performance gains of using a queue?
View 2 Replies
View Related
Nov 29, 2005
I have created a queue with automatic execution of a stored proc with the attribute Max_Queue_Readers = 5. While processing data, I can select from the queue and see that messages are backed up in the queue. I have two questions:
View 6 Replies
View Related
Nov 11, 2003
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.
The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.
Here is a sample of some data:
User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3
Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:
User1 Group1
User2 Group2
User1 Group1
I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.
Is there a simpler way to prevent duplicate entries in a table using sql?
Thanks a lot,
Chris
View 3 Replies
View Related
Feb 21, 2001
hi, If I have a table which contains customer names. I want to have some kind of process to validate any new insert record into the customer table. so if the inserted new customer already exist in the table I will get a message that say " Sorry , this customer name is already in the system " .... how can I do that,
I am using sql server 2000. thanks
Ali
View 2 Replies
View Related
Jul 10, 2015
I'm trying to flesh out a good queue table design with our dev team.So here is a general overview of the scenario. First an application will hit a WebAPI and grab any updates to Content and store those ID's in SQL (queue table). Next is the fun part, different multi threaded apps will process ID's from the queue. One app will make updates to the data in a different SQL DB while the other will update an index (likely Elastic).
Obviously, we don't want multiple threads working on the same items. One strategy could be to use UPDLOCK & READPAST query hints. However, I'm not sure about the reliability or performance of this solution. I just started looking into setting up a service broker but that would be completely unfamiliar territory for me. Also I can see how a broker might work well within the instance but how would that work with the application making updates to Elastic?
View 5 Replies
View Related
Sep 20, 2006
hi..
How do i prevent other users from changing the data of my tables? Means one can change data using only my login rest others cannot even DBA or also from server administrator
View 1 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Jun 18, 2015
Trying to create a report... Report should show * documents on hold then depending on the "on-hold type" look in the corresponding table and SELECT a few fields. Here is what I have. Where do I SET the @profile variable to return the profile from my queue table?
DECLARe
@profilevarchar(256)
SELECT
q.[profile],q.on_hold,q.on_hold_message,q.dbc_state
FROM
QueueASq
[code]...
View 5 Replies
View Related
Feb 14, 2007
hey all you database guru's hopefully someone can lend some insight as to a little table design problem I have.
Basically I've got a system in place to authorize users to access a website typical username password stuff. The table contains a list of users and there passwords plus the auth level and a few other tid bits that aren't really important enough to into detail about them here. What I want to do is add a messaging system to this, I think I could probably figure out a way to do this half decent if I setup a seperate table for each user to add a row to the table for every message entry than in my asp.net code have it delete everything but the last 10 entries every time a user logs on. However I would much prefer a way that I didn't have to setup a whole new table for each user just for messaging purposes, maybe store something like a list in one of the database cell's kind of like .nets generic.list or better yet generic.queue, I would also like a way if it's possible without too much work to have the table automatically delete the oldest message every time a new message is received if there's already 10 messages existing for the user.
Anyways hopefully someone has some experience in setting up a system like this, I don't really require any code samples I can code it all myself (other than the database code to automatically remove entry's, I'm not a database guy) if someone could just explain a way to accomplish what I'm trying to do, or if someone has a different more convenient way of doing this I would be up for suggestions
Thanks in advance for any help offered, I do appreciate it
View 3 Replies
View Related
Aug 5, 2014
I need to discover the actual order in which locks are acquired on a table during a query.
This with a goal of analyzing the lock order of queries against the same table to prevent deadlocks.
I'm using SQL Server 2008 R2.
From Management Studio I execute:
begin transaction
<my query>
exec sp_lock
rollback transaction
In the output I see interesting information about which locks are acquired, but:
- are this locks ordered by the time they're acquired? That is, can I be sure that lock at row n is acquired before lock at row n+1?
- if not, how can I get this information?
View 4 Replies
View Related
Jun 3, 2015
I know there's an option in the SSMS to remove the hook at the option "Prevent Saving changes that require table re-creation".
But how can I set the option silent. I mean for instance at the setup or by a secret T-SQL command or setting a value in a hidden file? I don't want the user to open the options dialog.
I logged the registry by a tool and alas there was it. So the thing is to set the registry entry by our own setup after installing the SSMS.
[HKEY_CURRENT_USERSoftwareMicrosoftSQL Server Management Studio12.0DataProject]
"WarnTablesReCreated"=dword:00000001
View 5 Replies
View Related
Feb 7, 2002
Gurus,
I am trying to execute this stored procedure when I try to change all occurences of a field in a table.
(
@Dept char(8),
@DDept char(8)
)
As
Set NoCOUNt On
Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO
The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.
The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.
Is there a way around it, without doing major surgery on the VB code?
Thanks.
Sam
View 3 Replies
View Related
May 24, 2002
Hello,
I am working on the implementation of a database for my company and I have
a simple (I hope) question to ask.
I have the following stored procedure that will be running when I want
(actually when I want to delete a customer).
Do not mind if you do not understand what this procedure does... ;-)
Actually the Level8View is a VIEW of a nested table
(CustomerData->CustomerData).
CREATE Procedure DeleteCustomer
@ClientID INT
AS
UPDATE Level8View
SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level1=@ClientID
UPDATE Level8View
SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level2=@ClientID
UPDATE Level8View
SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level3=@ClientID
UPDATE Level8View
SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level4=@ClientID
UPDATE Level8View
SET Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level5=@ClientID
UPDATE Level8View
SET Level6=Level7, Level7=Level8
WHERE Level6=@ClientID
UPDATE Level8View
SET Level7=Level8
WHERE Level7=@ClientID
DELETE FROM Customers
WHERE ClientID=@ClientID
GO
I checked this procedure and works perfectly.
What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
view (Level8View) before the Update statements and unlock it after the
delete statements.
I do not want to do a Row by Row lock, or Page by Page lock since the updates
in this table occur in the whole table and during that operation I do not
want other threads to issue SELECT, INSERT or UPDATE statements.
Can someone answer me how I can lock and unlock the whole view or table
please?
I will appreciate it for your answer. Thanks.
Yours, sincerely
Efthymios Kalyviotis
ekalyviotis@comerclub.gr
View 4 Replies
View Related
Nov 11, 2005
I have a SP for an e-commerce site that creates an order doing the following fashion:
begin
-Fill out some variables
*Critical Section
-Create Order number
-While loop select on order table and recreate if ordernumber already exists
-Insert into order table
*End Critical Section
-Insert into order lines table, address table, etc.
commit
The problem is the Create Order number and While loop used to be at the top and occasionally I would get duplicate order numbers if two submitted at the same time. I rearranged it to the above and it happens less now.
My question is if there is a way to put an XLOCK on the orders table only during the critical section lines. Also my understanding is that once the insert into orders is done that the server will hold a lock until the commit in case of rollback.? I don't want to XLOCK the whole SP if I don't have to. It is quick though.
Thanks
View 3 Replies
View Related
Apr 25, 2008
I need 2 unique sequential numbers to be stored in the same table. Obviously, for the first number I can use an auto-number field. For the second number, I would like to use a 2nd table with just 1 field that would only ever store 1 record. The idea being that if a user needs to generate a new unique number, they lock the 2nd table, lookup the current value, store the value in a variable for use, add 1 to the value of the number in the table, then unlock the table.
Presumably, I'd use something like this:
SELECT OrderNo
FROM tblUniqueOrderNumber (WITH TABLOCK)
Does this lock the table so no other users can read/write the table? If so, how do I unlock the table once I've updated OrderNo? What happens if another user then reads the table while it has a TABLOCK applied? I'm using SQL Server 2005 and the database is a multi-user database. I have a feeling I'm not going about this the best way possible, but I'd like to know how the table lock works eitherway.
View 12 Replies
View Related
Apr 30, 2004
hi
can i lock my table while doing an updation so that all others in n/w can insert into the table only after my operation is completed?
if a user attempts to save/update the 'locked'table, will he get an error message or will his application (using ADO) will wait till the table gets unlocked?
View 2 Replies
View Related
May 11, 2004
Can any one please tell me how to lock & unlock a table a from inside a store procedure using T-SQL
thanks
View 6 Replies
View Related
May 6, 2008
hi,
I am running a sql job that select data from one database (multiple table) and inserts into another database (1 table)
I have a problem with the way it is locking the tables I am selecting from !
my query is like :
insert into testdb.dbo.tabletest
select * from
livedb.dbo.tablex
inner join liveb.dbo.table1 on tablex.id=table1.xid
It seems to be locking the table and blocking other users from accessing the table.
suppose my question is why is the select locking the table ?
View 1 Replies
View Related
Jul 20, 2005
I want to insert values into a table and the same time select a valuefrom the same table.I insert a record of a parent type and use a function to create asubscriberType for the record.Next i insert a record of child type and want to select thesubscriberType from the parent record.This do not work when i useINSERT TABLESELECT ,Name,"function to generate subscribertype",.............I DO NOT WANT TO USE CURSOR!!
View 1 Replies
View Related
Aug 21, 2007
We have a MS SQL database with an Oracle linked server 'ALTTEST'
We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction?
I've tried applying the code below, but it doesn't work.
Any help appreciated.
BEGIN TRAN
SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX)
COMMIT
View 5 Replies
View Related
