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

Question

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?

Answer 1

Here's few points that you should consider:

• Sp_getapplock is not row based, so I would assume it's not something you can use
• "application's main thread works as usual." -- but if you're locking rows, any update / delete operation will get stuck, so is that working as usual?
• Once the locking ends, is it ok to do all the updates right after that that were blocked?
• Is your blocker thread going to do updates too?
• If the application and the external device are doing updates, how to be sure they are handled in correct order / way?

I would say you need to design your application to work properly in this situation, not just try to add this kind of feature as an add-on.

The title says about releasing in another transaction, but that's not really explained in the question.

Answer 2

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.