2

If I need to update same row with two concurrent thread then is it possible in SQL Server?

Suppose I have one table that contains Id and Name columns and now I want to update the third number Id with Name 'xyz' using one thread. Using the other thread, I want to update the same name which I am updating using the first thread, but it gives error like your connection is in connecting state. I can't update the data.

Does anyone know how to solve it? Do I need to assign a lock?

  • are you reusing connection objects? – vijayp Sep 19 '14 at 7:27
  • Are you working with ADO.NET? – vijayp Sep 19 '14 at 7:32
  • Yes.I am working with ADO.Net in wpf application. When i update data in first thread : i open the connection and after ExecuteQuery() i close it and in second thread i did the same – hemangi mehta Sep 19 '14 at 9:20
3

Is Sql server thread-safe?

This issue is not with that SQL Server being thread safe or not.The issue is there with SQL Server Connection Pooling

In Short:- Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. More reading from the link above.

Things to try with Connection object to fix this

  • Don't reuse connections or any ADO.NET objects at all and don't make them static.

  • Always create, open(in case of Connections), use, close and dispose them where you need them

  • Use the using-statement to dispose and close(Connections) implicitely

Now update on same row: If the update statement are to acquire a row/table lock, for instance, any other update process to the same row/table at the same time would be blocked.

Further Reading:

Locking and Row Versioning and Table Hints

  • Connection pooling solved the problem but now the execution of two threads are to be done randomly. sometimes the First thread update executes and sometimes second thread gets update. So what can i Say SQL Server is not thread safe thats why this happens?? – hemangi mehta Sep 19 '14 at 11:17
  • If the update statement are to acquire a row/table lock, for instance, any other update process to the same row/table at the same time would be blocked.I have added these in post – vijayp Sep 19 '14 at 11:35
  • yes but i am asking that what does it mean? does it mean that SQl Server meant to be thread-safe as it handles blocking stuff itself?? i am a beginner so don't know detail stuff about this.. – hemangi mehta Sep 19 '14 at 11:46
  • To answer you question in simple word, yes it is. there is lot more on it like deadlock. – vijayp Sep 19 '14 at 11:55
1

From the database perspective, this can be configured with isolation levels. Be aware that every isolation has some scenarios which can be solved but also with a certain impact. I think the wikipedia article is very clear for a first step. See: http://en.wikipedia.org/wiki/Isolation_(database_systems)

If you do not want/can't solve it with your database, you have to look what your application server stack offers you for thread synchronizations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.