CODEWITHSUNDEEP
sql-servermsdtc
Steve
Steve

Reputation: 6460

Distributed Transaction from Multiple Sources to a Single SQL server Instance

What I want to do is have multiple threads, processes or even machines share a single transaction. To be precise. I want to share out a chunk of work (mostly SQL Server queries) to my multiple threads, processes or machines, and I want the work to be atomic. I have looked at MSDTC, but it seems to be great if you want to, for example, update several different SQL Server instances (or other resources), and have everything under the same transaction, but it seems to always originate from the same source. i.e. one process (one connection). Is it possible? No restriction on technology, except SQL Server. I want two phase commit, but I want to start the transaction from one machine, and give that transaction (or a way to join it) to other processes.

Problem Definition The problem is I have a long process, which involves some complicated calculations and updates to the database. The whole process can be scheduled and a lot of it can be processed in parallel. I'd like to farm that processing (including database updates) to separate processes including remote processes.

Upvotes: 0

Views: 670

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294247

To answer strictly the question:

However this is almost certainly not what you want to do. I recommend you describe the actual problem you're trying to solve, not asking how to implement a specific solution you believe is appropiate.

Upvotes: 3

shA.t
shA.t

Reputation: 16958

I think you want to do a big transaction; like loading a huge row-set by a select statement; If I'm in a right way; I hope this steps will direct you to what you want ;).

  1. Each process, thread or machine say them stations needs a unique ID, like s1, s2, ..., sn.
  2. [Optional] As capacity of each station set a station size for all.
  3. Store this data in a table like station.
  4. Now with a [dynamic] paging solutions using from station table.
  5. Assign each page to a station, call stations to execute their pages.

Example:

Query:

select * from someTables

stations:

stationId | size   
----------+----------
s1        | 10
s2        | 20
...       | ...
----------+----------
          | sumSize

(i.) You need a queue that repeated when sumSize is less than count(*)

queue_repeat_count = (select count(*) from sometables) / sumSize

When you call stations you need to repeat it queue_repeat_count times.

In each queue you have a cursor on station table to call station with an argument, like this:

station_Calls(queue_repeat)

You have a stored-procedure in your server that handles paging with queue_repeat argument.

This is just an idea in my mind, I hope it can help you ;).

Upvotes: 0

Related Questions