TransactionScope Instead of using a lock

4
votes

I currently have a method which reads data to determine if an update is needed, and then pushes the update to the database (dependency injected). The method is hit very hard, and I found concurrency related bugs, namely, multiple updates since several threads read the data before the first update.

I solved this using a lock, it works quite nicely. How may I instead use a TransactionScope to do the same thing? Can I? Will it block another thread as a lock would? Further, can I 'lock' an a specific 'id' as I am doing with a lock (I keep a Dictionary that stores an object to lock on for each id)?

I am using Entity Framework 5, though its hidden by a repository and unit of work pattern.

1
c#entity-framework-5transactionscope
What about a ReadWriterLockSlim instead? If you have multiple reads and few writes it's much better than a lock. (how TransactionScope would make it thread-safe?) - Adriano Repetti
That would be better than just the lock (+1). But, it wouldn't allow me to safely work on the data elsewhere, or work from another machine on the same data. - ccook
ReaderWriterLockSlim is generally recommended to be used without recursive access allowed, and allows one to write still problematic code if it is allowed, a perilous situation. Just as perilous, if not more so, as the one he solved. - Grant Thomas
Perhaps if I could make the EF context (again behind an interface) have a transactionscope preventing read/writes from convoluting? - ccook

1 Answers

2
votes

Application level locking may not be a solution for this problem. First of all you usually need to lock only single record or range of records. Next you may later need to lock another modifications and get into quite complex code.

This situation is usually handled with either optimistic or pessimistic concurrency.

  • Optimistic concurrency - you will have additional database generated column (database usually have special type for that like timestamp or rowversion). Database will automatically update that column every time you update the record. If you configure this column as row version EF will include the column in the where condition of the update => the executed update will search for the record with given key and row version. If the record is found it will be updated. If the record is not found it means either record with the key doesn't exist or someone else has updated the record since current process loaded its data => you will get exception and you can try to refresh data and save changes again. This mode is useful for records which are not updated too much. In your case it can cause just another troubles.
  • Pessimistic concurrency - this mode uses database locking instead. When you query the record you will lock it for update so no one else can also lock it for update or update directly. Unfortunately this mode currently doesn't have direct support in EF and you must execute it through raw SQL. I wrote an article explaining the pessimistic concurrency and its usage with EF. Even pessimistic concurrency may not be a good solution for database under heavy load.

If you really build a solution where a lot of concurrent processes tries to update same data all the time you may end up with redesign because there will be no reliable high performing solution based on locking or rerunning failed updates.