CODEWITHSUNDEEP
c#sql-server
martoo
martoo

Reputation: 103

Two servers running the same query to single shared database c#

I have two servers that runs the same query checking for specific values in a single shared DB. If the query finds the values it will alter those values. At the same time the other server might run the same query and there will be some kind of conflict while trying to alter the information.

Question: How could I best configure that the servers won't run their query at the same time and guarantee that they won't get conflicts?

Upvotes: 1

Views: 134

Answers (3)

Bewar Salah
Bewar Salah

Reputation: 567

You need to have an extra column in database ex. server_id. and write a query as:

select * from database where server_id = 1 --for the first server
select * from database where server_id = 2 --for the second server

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

Databases take care of this for you automatically. They use locks to make sure only one query access specific data at a time. These locks don't have to apply whole tables; depending on the query and transaction type, per-row locks are possible also. When you have two queries that should be grouped together, such as your select and update, transactions make sure the locks from the first query are not released until both queries have finished.

Generally, databases are meant to serve queries (and release their locks) quickly, so that two queries that arrive at about the same time will be processed in sequence with little to no observable delay to the end user. It is possible for locks to cause problems for queries that need to lock a lot of data, that need to run for a long time, or when two transactions begin to lock unrelated data but later both need data locked by the other. That is called a deadlock.

Problems with locks can be controlled by adjusting transaction isolation levels. However, it's usually a mistake to go messing with isolation levels. Most of the time the defaults will do what you need, and messing with isolation levels without fully-understanding what you're doing can make the situation worse, as well as allow queries to return stale or wrong data.

Upvotes: 3

Takarii
Takarii

Reputation: 1648

Transactions and Isolation levels are your friends here. You need to set the isolation level so that they won't interfere.

Refer to https://msdn.microsoft.com/en-gb/library/ms173763.aspx for guidance on the level you need to set.

Upvotes: 1

Related Questions