Row Level Locking For Queue Table To Get Rid Of Concurrency Problem
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
ADVERTISEMENT
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
View Related
May 1, 2008
I have several osql processes running piped sql transactions, each of these transactions call pretty much the same update statements with joins to table(s) (table2 in this case) that, though large, are pretty much created by each process so they are not shared - the destination table has about 2.5M rows:
UPDATE t1 SET
t1.a= t2.a,
t1.b = t2.b
t1.c = 'U'
FROM table1 t1 WITH (ROWLOCK)
INNER JOIN table2 t2 WITH (NOLOCK) ON
(t2.d = t1.d AND
t2.e = t1.e AND
t2.f = t1.f AND
t2.g = t1.g)
Initially I couldn't get the processes to run concurrently, but after creating an index on fields d, e, f, g and using the NOLOCK and ROWLOCK hints, the processes will run for a while and then hang on the UPDATE - it took me a while to trace it & invariably the system stops processing (no errors!) when the above and similar UPDATE's to the same 'table1' are attempted. The UPDATE's are embedded in transactions with each having a TRY and CATCH clause for error handling - the weird thing is the processes hang without error...
I really need help cuz I've been banging my head against a wall on this! Since a lot of rows are affected I imagine the ROWLOCK's are being ignored and pagelocks are occurring affecting other processes and their updates; I'm not getting a deadlock or a lock timeout (though I set that absurdly high--will reduce it an try again now).
I could really use help on even approaching this another way - I know since 'table2' has a lot a rows a single UPDATE is probably not a good idea so i might break that up into single UPDATE's - help on iterating through 'table2' and updating 'table1' one row at a time would be appreciated - and any other thoughts, comments, solutions, etc...
Many Thanks!
-p
View 4 Replies
View Related
Jun 8, 2007
Hi,
We have a batch job that runs for about 45 min and reference couple of
tables in queries but on one of the table DD (dummy name) we want to
take a shared lock so that other sessions can read the table but no
session should be able to write to table, so at the beginning of
transaction/batch job we take a SHARED level table lock on this table.
Now the locking behavior is strange, If someone want to read the table
(IS on table DD) they are working fine but as soon as one of the DML
wants to modify the table and get blocked to get IX lock on DD other
sessions who just wants to read the table and need to get the IS lock
gets blocked so system is kind of hung after that. I tried the same
scenario on SQL Server 2005 and it works fine so this looks like kind
of bug in SQL Server 2000 unless Microsoft consider this as "AS
Designed" locking strategy. We can the following queries to reproduce
the issue:
>From QA window1 run the following code:
create table dd (dd int primary key, dd1 int,dd2 int)
set nocount on
declare @i int
begin
set @i=1
while (@i < 10000)
begin
insert into dd values(@i,@i+10000,@i+100000)
set @i=@i+1
end
end
Now we have a table with about 26 pages and 10k rows.
Now we run the following code from same window:
Begin Tran
select 1 from dd with(tablock,holdlock)
Don't commit at this point
And we will see the following using sp_lock:
57 7 850818093 0 TAB S GRANT
Now on Window2 in QA run the following code:
begin tran
insert into dd values(10000,10001,100001)
And we will see that session in window2 is waiting for IX lock:
56 7 850818093 0 TAB IX WAIT
57 7 850818093 0 TAB S GRANT
Now we run the following code from Windows3, we are just reading the
table and it still gets blocked:
select * from dd where dd=99
56 7 850818093 0 TAB IX WAIT
57 7 850818093 0 TAB S GRANT
66 7 850818093 0 TAB IS WAIT
Since this is fixed in SQL Server 2005 we are wondering if this is
known issue and there is any hotfix available or this is considered by
Microsoft as "as designed?
What is the other alternative to block writers on this table and allow
readers when this batch job is running?
Thanks
--Harvinder
View 1 Replies
View Related
May 15, 2007
Using VS05 pro SP1...
(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)
This is for a telephony application using Last In Wins concurrency...
Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.
We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...
- Fill table A and table B identically
- change column1 value in table A
- Update table A
- change column2 value in table B
- Update table B - this will generate a concurrency error.
Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.
So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.
I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.
Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.
Thanks!
Bob
View 1 Replies
View Related
May 3, 2007
Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database.
Also what is the name of process which converts low level locking into high level locking and vise versa.
-Sanjeev
View 2 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
Feb 24, 2006
I have a busy transactional table , I wanna use row level locking mechanism in msSQL.
SELECT * FROM PARTY WITH (UPDLOCK ROWLOCK)
where LastName ='Clinton'
is there any downsides of this approach?
View 1 Replies
View Related
Sep 19, 2002
I used sp_indexoption to allow row locks and disallow page locks on all indexes of a heavily contented table (lots of concurrent selects/inserts/updates). The first error I saw was "The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.". I restarted the sql server and everything is ok (the server is not very powerful and will be beefed up).
My question is that with the above settings, will the select statements acquire row-level shared locks as well? Since our select queries are fairly complicated, my main concern is that we may frequently run of of resource even with a more powerful machine. Is my concern valid or I got everything wrong?
Thanks a lot.
View 1 Replies
View Related
Apr 28, 1999
Hi there,
yes, There is a 'insert row level locking' but
Is there anything for delete or update row level locking?
We keep geting deadlock problem with our application with multi-users. First we ran into the deadlock on INSERT. By setting 'insert row level locking' help us to go a little bit further but later we run into deadlock with DELETE. For my knowlege, DELETE and UPDATE requires exclusive page lock. Might be I am out of date.
Thanks in advance for your help
Tung Nguyen
View 1 Replies
View Related
Jul 20, 2005
How do I do row-level locking on SQL Server?Thanks,Nid
View 16 Replies
View Related
Jan 3, 2000
I am writing a VB program, which needs to update record A in one table using transaction control (BeginTrans & CommitTrans). But due to the page-level locking, before the "CommitTrans", other users couldn't not read other records in the same table.
Is there any way that I can do the record/row level locking? If possible, could you provide me some VB source code? Such as how to use "DBCC ROWLOCK" or how to set for row-level locking.
Thanks for your kind help.
View 1 Replies
View Related
Sep 17, 1998
SQL Server 6.5 on-line help states that IRL is only effective if the table has a unique clustered index defined on the table. IF this true and if so does anyone know why.
thanks,
Stu.
View 1 Replies
View Related
Jul 20, 2005
Hi,We have encountered deadlock on a table which is used to generatesequential numbers for different categories eg typical entriesCategory ValueTRADE_NO 1456JOB_NO 267.....The applications reference the relevant category applicable to themand updatethe Value accordingly. This is table is very small, occupying 1 page.However, it has no index as it was not seen to be appropriate for atable this size.However, can someone please advise whether1. An index is required for row level locking2. If an index on a table as small as above is likely to reduce thedeadlock rate.Also, please consider the following but which I am not sure isrelevant for above query.We noted that when we migrated the database concerned from SQL 6.5 toSQL 2000, using DTS, that the database was NOT strictly in SQL 2000format for non clustered indexes (NC) ie the clustered key was notpart of the NC index until the clustered index was rebuilt.Given this should I just rebuild this table with a fake index and dropit thereafter.We are aware of the different techniques used to avoid deadlocks (egtables accessed in same order etc) and have , as much as possible,implemented those practices.I thank you in advance for any help you may be able to offer.ThanksPuvendran
View 12 Replies
View Related
Apr 22, 2015
We have one table.We have updated the status for one column to 1 and we did not committed the transaction. Can we do an another update on another row.
In below example i am updating GEO_D and transaction is not committed. Now my requirement is we have to update other records (not Geo_D). If try to update GEo_D it should wait.
IF OBJECT_ID('TEMPDB..##TEMP_STSTUS') IS NOT NULL
DROP TABLE ##TEMP_STSTUS
CREATE TABLE ##TEMP_STSTUS
(
ID INT IDENTITY(1,1)
,NAME VARCHAR(10)
,STSTUS VARCHAR(10)
)
INSERT INTO ##TEMP_STSTUS SELECT 'GEO_D','0'
[Code] ....
View 6 Replies
View Related
Jan 5, 2001
I'm running SQL Server 7.0. I have a DB running with 6.5 compatibility mode.
Do INSERT, UPDATE or DELETE queries use row level locking in this DB ?
(I know if I set the db compatibility mode to 7.0 row level locking will be enabled)
Thanks in advance for your help.
View 1 Replies
View Related
Apr 20, 2007
I m using sql server 2005
i have got one request ,to apply page level locking on database
can nyone how it is done
i can do that for a single script and for session(transaction isolation level)
but dont know about database level locking scheme
thanks in advance
View 2 Replies
View Related
Jul 20, 2005
HiIs it possible to force row level locking in one or more tables insome database. We have some problems when SQL Server decides to choosepage- or table-level locking.We are using SQL Server 2000.Best regardsAarno
View 1 Replies
View Related
May 9, 2008
I am running one maintenance plan which includes just "Chech DB Integrity" and "Reorganised Index". But it failed and I am getting following error:-
Failed-1073548784) Executing the query "ALTER INDEX [CgiExclusion_ProfileId] ON [dbo].[CgiExclusion] REORGANIZE WITH ( LOB_COMPACTION = ON )" failed with the following error: "The index "CgiExclusion_ProfileId" (partition 1) on table "CgiExclusion" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 16 Replies
View Related
Dec 27, 2007
I have a question on locking pattern of read committed with snapshot isolation level that when two transaction update two different records then why do they block to each other even if they have previous committed value (old version of record).
I executed the below batch from a query window in SSMS
--Session 1:
use adventureworks
create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Black')
insert marbles values(2, 'White')
alter database adventureworks set read_committed_snapshot on
set transaction isolation level read committed
begin tran
update marbles set color = 'Black' where color = 'White'
--commit tran
Before committing the first transaction I executed below query from second query window in SSMS
--Session 2:
use adventureworks
set transaction isolation level read committed
begin tran
update marbles set color = 'White' where color = 'Black'
commit tran
Here the first session blocks to second session. These same transactions execute simultaneuosly in snapshot isolation level. So my question is why this blocking is required in read committed with snapshot isolation level?
Thanks & Regards,
Subhash Chandra
View 1 Replies
View Related
Nov 11, 2015
Is it possible to force row level locking in Sql server 2015 before inserting the data and release the same afterwords..find the code for which we would like to impliment the same
DECLARE @TravelAgentid Varchar(20)
DECLARE @Date DATETIME2(7)
DECLARE @InsDate DATETIME2(7)
set @TravelAgentid ='A101'
[code]....
View 11 Replies
View Related
Nov 29, 2006
When you utilize transactions in ADO.NET are the locks put on the entire TABLE used or at the row level?
For instance if you do a SELECT within a transaction if you only pull 5 rows out of a 1000 row table can you just make it lock the rows that have been pulled? It seems like it locks the entire table?
Thanks
View 6 Replies
View Related
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
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
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
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
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