How do I lock a table from write operations until my insert is complete with Linq-to-sql

Question

I am working on an auction system and one of the issues I am trying to make sure I don't get affected by is a situation where 2 people put in a bid at the exact same time for the same item.

To do this I need to put a lock on the table, get the highest bid for the current item, make sure the entered bid is greater than that bid, add a new bid entry into the table, then unlock the table.

I need to lock this so a second webserver does not trigger a bid insert between when I check for the highest bid and when I insert my new bid into the table, as this would cause data issues.

How do I accomplish this with Linq-to-sql?

Note, I don't know if transactionscopes can do this but I can't use them, as they tend to trigger a distributed transaction due to our webfarm setup, and I can't use distributed transactions.

Have you considered moving the logic into a stored procedure (and protected with a transaction), and calling that from your data context? — Rowland Shaw, Jul 07 '12 at 20:35
I haven't actually, though I would prefer to keep it all in code but a stored proc probably isn't a bad idea. — KallDrexx, Jul 07 '12 at 20:41

score 2 · Accepted Answer · edited May 23 '17 at 12:23

There seem to be a couple of obstacles implementing a solution in pure Linq:

You should definitely avoid a table lock

A table lock would make it impossible for several items to be bid on during the processing of one single bid, thus severely harming performance

Linq to SQL does not seem to support pessimistic locking

as stated in other answers on SO.

If you cannot have transactions in your code, I suggest the following procedure:

generate a GUID for your operation

pseudo-lock the item's record using the guid:

UPDATE Items SET LockingGuid = @guid 
WHERE ItemId = @ItemId and LockingGuid IS NULL

SELECT @recordsaffected = @@ROWCOUNT

the lock succeeded if @@rowcount == 1
perform your bidding operation
UPDATE the record back to LockingGuid = NULL
if the lock fails, either raise the failure to the .Net client, or busy-wait using WAITFOR.

You should implement proper exception handling so that item records do not get locked indefinitely by a dying or failing process, probably by adding a datetime column storing the timestamp the lock occurred, and cleaning up orphaned locks.

If your architecture allows for separate backend operation, you might want to have a look and CQRS and Event Sourcing for processing such bidding operations.

I decided to use this, except using a datetime instead of a Guid, so I know that if a lock was over a minute ago it's 99.9% likely that it's a stale lock, and to clear the lock anyway. — KallDrexx, Jul 08 '12 at 04:08

Bob Horn · Answer 2 · 2012-07-07T23:04:31.517

You could use a separate table to store information when this processing occurs. For example, your second table could be something like:

Table name:
ItemProcessing

Columns:
ItemId (int)
ProcessingToken (guid)

When a process wants to check on a current bid, it writes the ID of the item and a token/guid to the ItemProcessing table. That tells other processes that this item is currently being inspected. If there is already a row in the ItemProcessing table for this item, the other process must wait or abort. When the original process is done, it removes the token (sets it to null), or removes the row from ItemProcessing altogether. Then other processes know they can process that item.

Of course, you'll need a way to make sure both processes don't write to this processing table at the same time. You could accomplish that by inserting into this table where ProcessingToken is null. If another table just beat a process to it, the second process won't be able to insert because the ProcessingToken will exist.

While not a full solution, in detail, that's the basic idea.

score 1 · Answer 3 · answered Jul 07 '12 at 23:21

You can manually begin a transaction and pass that transaction to the DataContext.

http://geekswithblogs.net/robp/archive/2009/04/02/your-own-transactions-with-linq-to-sql.aspx

I think it is necessary as well to manually control the opening and closing of the Connection to avoid an unwanted escalation to a distributed transaction. It seems that the DataContext will actually get in its own way and try to open two connections sometimes, thus causing a promotion to a distributed transaction.

How do I lock a table from write operations until my insert is complete with Linq-to-sql

3 Answers3