Using Sql Db Column As A Lock For Concurrent Operations In Entity Framework

We have a long running user operation that is handled by a pool of worker processes. Data input and output is from Azure SQL. The master Azure SQL table structure columns are appro

Solution 1:

What we usually do is this:

At the beginning of a long operation we start a transaction:

BEGIN TRANSACTION

Then we select a row from the table we would like to update/delete using these hints:

SELECT*FROMTableWITH (ROWLOCK, NOWAIT) Where ID =123;

Then we check that we have the row. If the row is locked by another process there will be an SQL Error. In this case we rollback the transaction and advise the user. If the record is locked we process the record, and do the required updates, using the same transaction object we used to lock the record:

UPDATETableSET Col1='value'WHERE ID =123;

Then we COMMIT the transaction.

COMMIT;

This is just the Pseudo-code of the process. You will have to implement it in your program.

One small note regarding the above process. When you lock the record in SQL Server (or Azure), use the primary key in your WHERE Clause, otherwise the SQL Server will decide to use a Page lock, or Table lock