May 20, 2011 at 3:00 am
Hello,
One of our colleagues have a scenario where multiple threads will be accessing the same table.He wants to grant access to a particular set of records to one thread at a time. E.g. record number 1 to 10 is being accessed by one thread then the next thread trying to access the records should get record number 11 to 20.
Is it possible by setting the isolation level?
Thanks in advance.
Regards,
Rohit
May 20, 2011 at 3:25 am
Hi Rohit
This looks like a scheme for selecting and using preassigned numbers for use as identity values in a table - is this the case?
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 20, 2011 at 3:33 am
Hi Chris,
Thanks for the reply.
The records will not be actually preassigned. The records will be allocated sequentially& dynamically i.e. any top 10 records should be assigned which are not being accessed by any other thread.
May 20, 2011 at 3:44 am
SELECT TOP 10 Col1,Col2,Col3
FROM myTable WITH(READPAST)
WHERE Col1='XYZ'
AND Col2='ABC'
I believe this will get you ten records that are not locked for update.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 20, 2011 at 3:46 am
SQLRO (5/20/2011)
Hi Chris,Thanks for the reply.
The records will not be actually preassigned. The records will be allocated sequentially& dynamically i.e. any top 10 records should be assigned which are not being accessed by any other thread.
But you're picking blocks of values from a table, which are destined to be used as PK's, right?
This article by Paul White will help.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 20, 2011 at 3:47 am
Yes. You are right.
May 20, 2011 at 3:51 am
SELECT TOP 10 Col1,Col2,Col3
FROM myTable WITH(READPAST)
WHERE Col1='XYZ'
AND Col2='ABC'
I believe this will get you ten records that are not locked for update.
Can I lock the records / rows while selecting them for reading.
E.g. Thread 1 is accessing the table - Allocated 1 to 10
Thread 2 is accessing the table at the same time - Allocated 11 to 20.
Is it possible?
May 20, 2011 at 3:53 am
SQLRO (5/20/2011)
Yes. You are right.
Paul's article shows an efficient and rigorously tested method for doing this.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 20, 2011 at 4:00 am
This sounds like a high contention work queue scenario.
You could possibly manage it with the SERIALIZABLE isolation level and the READPAST hint, selecting 10 records from each thread and holding the transaction open while you processed them, but I tend to shy away from holding excessive locks.
You could accomplish the same in the READ COMMITTED isolation level by just updating a flag/status saying which thread owns that row, then selecting those rows to do the work. E.g.:
UPDATE TOP(10) dbo.myWorkTable WITH(READPAST)
SET Worker='Thread1'
WHERE Worker IS NULL
Then commit the transaction and read back to do the work.
May 20, 2011 at 4:06 am
You could possibly manage it with the SERIALIZABLE isolation level and the READPAST hint, selecting 10 records from each thread and holding the transaction open while you processed them
I tried the same which did not work
May 20, 2011 at 4:23 am
SQLRO (5/20/2011)
SELECT TOP 10 Col1,Col2,Col3FROM myTable WITH(READPAST)
WHERE Col1='XYZ'
AND Col2='ABC'
I believe this will get you ten records that are not locked for update.
Can I lock the records / rows while selecting them for reading.
E.g. Thread 1 is accessing the table - Allocated 1 to 10
Thread 2 is accessing the table at the same time - Allocated 11 to 20.
Is it possible?
Yes, you can use the UPDLOCK hint in addition to the READPAST hint.
I would definitely defer to anything Paul White recommends in his article referenced earlier though - that man knows his stuff!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 20, 2011 at 4:35 am
SQLRO (5/20/2011)
You could possibly manage it with the SERIALIZABLE isolation level and the READPAST hint, selecting 10 records from each thread and holding the transaction open while you processed them
I tried the same which did not work
As Mister Magoo said, you'd need to add a UPDLOCK hint as read locks are shared (and probably ROWLOCK as well to make sure it doesn't escalate to a table lock) - think you're better off just updating a status for 10 rows with READPAST in READ COMMITTED mode and holding locks for as short a period as possible.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply