0

I am experiencing problems with a MSSQL instance, where deadlocks occur from time to time. I have a Table A, which holds temperature measurements. My application contains 1-10 worker threads, which collect measurements via TCP from remote locations and then want to store them inside the database. Of course these workers use transactions to conduct their tasks. The IsolationLevel of the transactions is set to ReadCommitted. Still deadlocks occur and the CPU load of the database server is up at 100%. Can anyone tell me, what I have to consider to get this working? I thought the database system will do the multi-user-synchronization for me. At least this is, what I learned at university.

Coxer
  • 1,694
  • 2
  • 26
  • 44
  • The university also teaches that deadlocks are a *consequence* of the database ensuring 'multi-user-synchronization'. Read [Detecting and Ending Deadlocks](http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx). You're missing an index on `Table A`. – Remus Rusanu Jan 31 '14 at 09:45

1 Answers1

0

My suggestion is to create another thread that will handle your updates into the database. So add the information into a collection from the threads in a thread safe manner, and let 1 worker thread do the updates/inserts into the table. You can even concatenate 10-30 of these statements and execute them together.

This is what we have done on a SMS Sender where we used up to 50 threads each sending SMS a SMS every 100ms. It worked brilliantly for us.

Jaques
  • 2,215
  • 1
  • 18
  • 35
  • This is quite the same, I came up with. But I have also another application that serves the stored data as a webservice ... – Coxer Jan 31 '14 at 15:51
  • That is most probably why you get the dead locks, because you are selecting from the same table that you update. When you do a select from the other application, add the `WITH (NOLOCK)` or change your Isolation level to READ UNCOMMITTED. (http://stackoverflow.com/questions/686724/sql-when-should-you-use-with-nolock). If you don't roll data back from the one side, and doesn't do another update from somewhere else while the webservice is selecting, you should not have a problem. – Jaques Feb 01 '14 at 16:39
  • Coxer, you cant have the best of both worlds. You have to make a decision on what the most important is. You dont have to lock the record while editing do you? Does the worker threads update those same records again. If not, then there is no problem. Read the information, manupilate it and update the record afterwards. Otherwise you need to rethink your architecture around the problem. – Jaques Feb 02 '14 at 21:26
  • The problem rather is, that I do not have the capability to lock only what **I** want. As I see the situation right now, I can only provide a transaction with a IsolationLevel and that is it. The locks are acquired by the NHibernate or .NET magic-database-framework. Is that true? In many cases, both processes do not access the same data, but when they do, I have to overcome this problem. It is okay if the webservice has to wait for the collection service to complete, but as it is now, the database system does only allow deadlocks in this situation. – Coxer Feb 03 '14 at 14:47