0

In a c# program, I have 2 threads which launches a stored procedure. This store procedure reads and writes data in some tables.

When I start my program, I have sometimes a SQL server exception (lock trouble).

To avoid deadlock, I tried to add a lock(this){ ... } in my program to avoid simultaneous calls of this stored procedure but without success (same exception)

How can fix that ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Patrice Pezillier
  • 4,476
  • 9
  • 40
  • 50

2 Answers2

2

lock(this) will not solve your concurrency problems, if more than one instance of the class is running, as the locks will refer to different this references, i.e.

public class Locker
{
   public void Work()
   {
      lock (this)
      {
         //do something
      }
   }
}

used as (assume these codes are run in parallel)

Locker first = new Locker();                Locker second = new Locker();
first.Work() // <-- locks on first          second.Work() // <-- locks on second

will lock on different objects and not really lock at all.

Using this pattern

public class Locker
{
   private static object lockObject = new object(); 
   // a static doodad for locking

   public void Work()
   {
      lock (lockObject)
      {
         //do something
      }
   }
}

will lock on the same thing in both cases, and make the second call wait.

However, in most cases from my experience, lock problems in SQL Server procedures were the fault of the procedure itself, holding transactions open longer than neccessary, opening unneeded transactions, having suboptimal queries, etc. Making your sp calls wait in line in the C# code, instead of waiting in line at the SQL Server, does not solve those problems.

Also, deadlocks are a specific category of concurency issues that almost always can be solved by refactoring the solution with data access in mind. Give us more info about the problem, there might be a solution that does not need application-level locks at all.

SWeko
  • 30,434
  • 10
  • 71
  • 106
  • 1
    You are right, but I would not use lock in my application code to prevent deadlocking on the database, as it is too blunt an instrument. The SP probably needs tweaking instead. – Polyfun Apr 06 '11 at 13:20
  • 1
    @ShellShock I agree, and I added a clarification about that, but it's also important not to use the `lock` statement incorrectly, since that tends to produce code that "*looks* right". – SWeko Apr 06 '11 at 13:23
0

As explained by @SWeko, C#'s lock will only resolve concurrency issue among threads of the current AppDomain, so if more than one AppDomains are running, let us say two desktop clients for simplicity, then they will run into deadlock. See Cross-Process Locking in C# and What is the difference between lock and Mutex? for more details.

It would be much better, even in case of desktop application, that you deal with deadlock issue within your stored procedure. The default behavior would be that your second request will wait till timeout for the first to finish and if you don't want to wait then use WITH(NOWAIT). Explore more

Community
  • 1
  • 1
bjan
  • 2,000
  • 7
  • 32
  • 64