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?

Improve this question
3
  • are you reusing connection objects? Commented Sep 19, 2014 at 7:27
  • Are you working with ADO.NET? Commented Sep 19, 2014 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 Commented Sep 19, 2014 at 9:20

2 Answers 2

Reset to default
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

Improve this answer
4
  • 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?? Commented Sep 19, 2014 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 Commented Sep 19, 2014 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.. Commented Sep 19, 2014 at 11:46
  • To answer you question in simple word, yes it is. there is lot more on it like deadlock. Commented Sep 19, 2014 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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.