CODEWITHSUNDEEP
sqlsql-server-2005sql-order-by
user2358523
user2358523

Reputation: 33

Using (UPDLOCK, READPAST) and ORDER BY - Not Working?

I am trying to SELECT codes from my database, but I do not need another person coming along and selecting the same codes before I have a chance to update them with taken. I have tried using only updlock and this solves my duplicate sales problem BUT it adds the problem of deadlocks. So now I am using (UPDLOCK, READPAST), this works perfectly fine until I try to use an ORDER BY within my statement.

Microsoft SQL 2005

BEGIN TRANSACTION

SELECT Top 10 ID FROM dbo.CODES 
with (UPDLOCK, READPAST)
where ItemNo = 'type-2' AND Sold = 0 
order by cast(NowStamp as DateTime) ASC

COMMIT TRANSACTION

I feel like my problem is going to come from the indexes.

Upvotes: 2

Views: 2239

Answers (1)

John Tseng
John Tseng

Reputation: 6352

This is a very similar issue to ORDER BY and WITH(ROWLOCK, UPDLOCK, READPAST)

The answer describes that you also need ROWLOCK and an index. Unfortunately, you can't create an index on that column since it's considered non-deterministic because of the CAST (see the first note on http://msdn.microsoft.com/en-us/library/ms189292(v=sql.90).aspx).

You should use CONVERT and specify a deterministic style from: http://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx

Upvotes: 1

Related Questions