Geek Answers Handbook

ORDER BY and WITH (ROWLOCK, UPDLOCK, READPAST)

I need to configure the queue system using some SQL tables, for example, described here . This, and since I need to filter queues in different criteria, inside the stored procedure I use

BEGIN TRANSACTION

CREATE TABLE #Temp (ID INT, SOMEFIELD INT)

INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition1

INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition2

(...)

UPDATE TableB SET SomeField = 1 FROM TableB WITH (ROWLOCK, READPAST) WHERE ID IN (SELECT ID FROM #Temp)

COMMIT TRANSACTION

I use ROWLOCKin the first table and UPDLOCKin the second, because after that select I am going to update only TableB, although I need to make sure that these rows are not updated in TableAany other parallel query. Everything goes well until I need to insert a sentence ORDER BYin any of the SELECTabove, so only very specific identifiers will be selected (I have to do this really). What's happening:

1) ORDER BY , ; , , SELECT.

2) ORDER BY , . .

, , WITH (ROWLOCK, READPAST) ORDER BY , . , . ?

: , TestTable (TestID INT, Value INT) "(1,1), (2,2),..." ""

BEGIN TRANSACTION

SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST)

WAITFOR DELAY '00:00:05'

COMMIT TRANSACTION

(1,2), (3,4), . ,

BEGIN TRANSACTION

SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST) ORDER BY VALUE ASC

WAITFOR DELAY '00:00:05'

COMMIT TRANSACTION

(1, 2), . ?!

+3
1

  • SELECT ORDER BY ROWLOCK - / TOP 2. , - READPAST

  • SELECT ORDER BY 2 , , -, ( , ). , 2 , , .

SQL Server , . ( ), UPDLOCK,

,

  • 3 SELECT (ROWLOCK, UPDLOCK, READPAST) , concurrency.
    ROWLOCK - /.
  • Value INCLUDE TestID, SELECT . , , concurrency, .

( ) SQL Server, 3

+4

More articles:


All Articles