1

I have a table in SQL Server having ID column, which is also set as unique clustered index.

I need to determine which records among given IDs are locked as being removed in another transaction.

If I use READPAST I get only those which are not locked, but there is no assurance that the rest of records exist or are being removed in another transaction. NOLOCK with deleted records will not return any data.

The only workaround I found was to execute SELECT with NOWAIT on those IDs which were not retrieved by READPAST and detect lock timeout error, but I need to do that for each record separately.

Is there any faster alternative for that without using system tables or stored procedures?

Rauf
  • 312
  • 3
  • 16
  • System tables are a step above machine code...what exactly do you want? Use ISOLATION LEVELS to achieve this like SNAPSHOT. You can read about this on MSDN [Understanding Isolation Levels](https://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx) – clifton_h Jan 29 '17 at 04:50
  • @clifton_h, I need to determine which of the given records are locked no matter whether they are currently being updated or deleted in another transaction – Rauf Jan 29 '17 at 19:12

0 Answers0