CODEWITHSUNDEEP
c#linq-to-sql
KallDrexx
KallDrexx

Reputation: 27833

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

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.

Upvotes: 4

Views: 4352

Answers (3)

Andyz Smith
Andyz Smith

Reputation: 708

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.

Upvotes: 1

devio
devio

Reputation: 37225

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.

Upvotes: 2

Bob Horn
Bob Horn

Reputation: 34335

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.

Upvotes: 1

Related Questions