0

I have 2 instance of the same service running on different machines. This is to ensure high availability (if one machine falls, killing the first service, then the second is still available).

These two service update & delete the same rows in SQL Server table at the same time when a bus event is triggered. So, I usually get a LinqToSql ChangeConflictException on bus event raise.

To handle this, I currently surround the SubmitChanges with try catch and do nothing in case of ChangeConflictException.

Is there any cleaner way?

Is it possible to lock the whole table using Linq to SQL? (so that one of the service will wait for the lock to be released before trying to do the update).

Dale K
  • 25,246
  • 15
  • 42
  • 71
Lou
  • 277
  • 1
  • 5
  • 15
  • Create a separate table. Take a table lock on it. Run your original code. Release the table lock. – mjwills Jul 17 '20 at 08:10
  • How do you lock a table with linqtosql ? – Lou Jul 17 '20 at 08:12
  • https://stackoverflow.com/questions/25273157/t-sql-lock-a-table-manually-for-some-minutes – mjwills Jul 17 '20 at 08:13
  • thank you; this is very interesting. However this is t-sql, and not using linqtosql directly. I'm trying to avoid this (and stored procedure also) – Lou Jul 17 '20 at 08:17
  • You could use [this](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-directly-execute-sql-queries) to execute the locking statement and combine it with a dummy `SELECT 1` if you need a results set. But LinqToSql doesn't have any method to lock tables. – Preben Huybrechts Jul 17 '20 at 08:29
  • 1
    You could also use [isolation level](https://learn.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15#remarks) `serializable` if you are looking to lock tables. But this can generate a whole bunch of other issues. – Preben Huybrechts Jul 17 '20 at 08:32
  • But the real question is, if you want high availability, why write it yourself? And not use SQL Server Cluster. You can configure [active/active](https://dba.stackexchange.com/questions/135964/sql-server-clustering-active-active-or-active-passive) clusters. – Preben Huybrechts Jul 17 '20 at 08:43
  • Regarding the active active : my understanding is that it ensure availability of the whole SQLServer, whereas I just want to ensure the availability of my service (a few tables impacted then). However I agree it would work, but its not free of cost :) – Lou Jul 17 '20 at 08:55

1 Answers1

1

My approach for this kind of locking would be to use a distributed lock, rather than locking the target table itself. If anything, it would allow you to include more logic within the process. You can't use the standard c# lock method because you've got multiple instances of the same application that would need to share the same lock, so you'd have to use something centralised.

I've used SQL Server's sp_getapplock for this before - you just need a single SQL database that is accessible by each instance of the application. You can either call this SP manually, or I know there are a few nuget packages that wrap this process up to make it a bit easier to integrate with. But you've also got the option of doing the same kind of thing with distributed locks in Redis, and probably most other cache solutions, if you've got any of those involved in your solution.

Essentially you can just take a new lock around whatever action you want to protect from being run concurrently. In your case, it would avoid multiple updates to that table running at the same time.

That being said, you could also consider looking at how these events are raised and handled, and seeing if you could get the resiliency you're looking for at that level instead. Just off the top of my head, something like RabbitMQ (and other message queues) allows for a lot of the failure situations to be handled at the message delivery level. I don't know if that would be worth looking into as well (if anything, it might avoid these events from being processed multiple times if they only need to be processed once), but it might be helpful to check that.

Jamie Burns
  • 1,258
  • 9
  • 21