Search code examples
c#sql-servermultithreadingrowlocking

Lock a row for a while in one transaction and release lock in other transaction

It is a .Net application which works with an external device. When some entity (corresponds to the row in a table) wants to communicate with device, the corresponding row in the SQL Server table should be locked until the device return a result or SQL Server times out.

I need to:

  • lock a specific row in a table so that row could be read, but could not be deleted or updated.
  • locking mechanism should run in a separate thread so that application's main thread works as usual
  • lock should be released if a response has made
  • lock should be released after a while if no response is received

What is the best practice?

Is there any standardize way to accomplish this?

Should I:

  • run a new thread (task) in my C# code and begin a serializable transaction, select desired row transactionally and wait to either time is up or cancellation token is received?

  • or use some combination of sp_getapplock and ...etc?

Solution

  • You cannot operate on locks across transactions or sessions. That approach is not feasible.

    You need to run one transaction and keep it open for the duration that you want the lock to persist.

    The kind of parallelism technology you use is immaterial. An async method with async ADO.NET IO would be suitable. So would be a separate LongRunning task.

    You probably need to pass a CancellationToken to the transaction code that when signaled makes the transaction shut down. That way you can implement arbitrary shutdown conditions without cluttering the transaction code.