February 28, 2008 at 10:29 pm
Here is the problem that I need to solve.I want user a to be able to query 100 records and update a column so that the next user grabs the next set of 100 and not have both users get the same records.Can and how is this to be done??I have seen update locks in a select as well as readpast.....Can anyone shed some light.....
February 28, 2008 at 11:37 pm
Hi
Cud u give more details ...
2 ways come to my mind ... may not be correct since we dont have the details from you.
1 - Keep a flag whihc tells that those records have been selected (seen) by a user and then u can use "Top" to select the next 100 records.
2 - If you have identity column in the table you can keep the maximum of this column in a table and use "Top" to select the next 100 records greater than the maximum value.
Hope I have understood you... otherwise this may be way off what's required.
"Keep Trying"
February 28, 2008 at 11:52 pm
I believe you are close....It is a concurrency issue.I have a flag or status that I can flip.I must prevent the second and any other queries from pulling the same data as their predassor.For example if we have 1000 rows I grab records 1-100 and do update well 2nd query can only get 101 to what ever.Will I not need to lock the table or what
February 29, 2008 at 7:01 am
You'll need to set a flag on the table itself, marking the records that no one else can access and include that check in your queries.
Otherwise, you have maintain an open connection to the database while holding an update lock on the rows and keep this in place while Peggy Sue does her typing on the client end of the application and pray to the gods that Peggy Sue doesn't get to chatting while you're holding the locks open. To quote Egon, it would be bad.
"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
February 29, 2008 at 1:25 pm
I have seen lots of advice from everyone here how to take care of the Dead Locks and concurrency issues. How come no one ever advices to use sp_GetAppLock . It works fine if the updates are being done by stored procs. is there any draw back to using this?
-Roy
February 29, 2008 at 1:38 pm
Roy,
I am in favor of using AppLocks for locking certain application level resources. I have not used it to lock a range of rows like what Allen is trying to do. There would be some added overhead with having the application have to check each of the 100 rows for an AppLock. In this case, it may be easier and less resource intensive to use the column/flag approach. I say may because I don't know for sure.
Allen,
The solution here may be simpler than it sounds. Is your application sticking with the default isolation level, or are you using any of the row versioning isolation levels?
March 1, 2008 at 7:52 am
I am unsure of what settings are set on this server.I was tasked to come up with a set of records locking and updating query.IF I use the falg column approach.How do I prevent 2 queries at the same time geting the same data.
March 3, 2008 at 2:06 am
Hi
Assuming you have a identity column
take the max value of the identity column for those records where the "flag is set".
Then query the next 100 records whose identity value is greater than the max value you queried earlier.
"Keep Trying"
March 3, 2008 at 5:37 am
Make sure you do the initial read to get the next 100 unflagged records with UPDLOCK (update lock) so that those records are no longer available for the next user coming in, even before you get the chance to mark them as being read. Even when two requests are "simultaneous" one is processed before the other and those locks will save you.
"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
March 4, 2008 at 2:25 am
Yes use the UpdateLock (UPDLOCK ) .
"Keep Trying"
March 4, 2008 at 8:12 pm
Could you give me a little example that would work on like a Northwind or something.Likethe tsql statement so I can test the theroy.
March 5, 2008 at 3:19 am
Hi
I think you can easily create a small table and try out the sql statements. If you can use a table with the same or similar structure as that of your orginal table that much better.
It will give you a first hand feel of things.
"Keep Trying"
March 5, 2008 at 6:25 am
allenrlewis (3/4/2008)
Could you give me a little example that would work on like a Northwind or something.Likethe tsql statement so I can test the theroy.
I'd have to experiment a bit to come up with an optimal method, but off the top of my head, mayb this:
SELECT TOP ( 100 )
*
FROM MyTable WITH ( UPDLOCK )
WHERE LockedStatus = 0
ORDER BY Id DESC
UPDATE MyTable
SET LockedStatus = 1
FROM ( SELECT TOP ( 100 )
Id
FROM MyTable
WHERE LockedStatus = 0
ORDER BY Id DESC
) x
WHERE MyTable.Id = x.Id
"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
March 6, 2008 at 5:04 am
Why use 2 statements? If the isolation level is read committed, why not just run the update? By filtering on the LockStatus column, SQL Server should lock other processes out from attempting to update the same rows. Once the update to LockStatus is complete, the rows no longer match the filter on LockStatus and should not be picked up by subsequent attempts by competing processes.
March 6, 2008 at 6:22 am
Only because he wanted to return the values and lock them down from other users. So I did the SELECT with the update lock first, then udpate the table to lock down the rows. Actually, I know another way that might work. You could do the UPDATE statement with an OUTPUT clause into a temp table and then select that. That will probably be more effecient. I think this is right:
CREATE TABLE #Table
(
Id int
,LockedStatus bit
)
UPDATE MyTable
SET LockedStatus = 1
OUTPUT INSERTED.Id
,INSERTED.LockedStatus
INTO #Table
FROM ( SELECT TOP ( 100 )
Id
FROM MyTable
WHERE LockedStatus = 0
ORDER BY Id DESC
) x
WHERE MyTable.Id = x.Id
SELECT *
FROM #Table
"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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply