multithreading with database
- by Darsin
I am looking out for a strategy to utilize multithreading (probably asynchronous delegates) to do a synchronous operation. I am new to multithreading so i will outline my scenario first. This synchronous operation right now is done for one set of data (portfolio) based on the the parameters provided. The (psudeo-code) implementation is given below:
public DataSet DoTests(int fundId, DateTime portfolioDate)
{
// Get test results for the portfolio
// Call the database adapter method, which in turn is a stored procedure,
// which in turns runs a series of "rule" stored procs and fills a local temp table and returns it back.
DataSet resultsDataSet = GetTestResults(fundId, portfolioDate);
try
{
// Do some local processing on the results
DoSomeProcessing(resultsDataSet);
// Save the results in Test, TestResults and TestAllocations tables in a transaction.
// Sets a global transaction which is provided to all the adapter methods called below
// It is defined in the Base class
StartTransaction("TestTransaction");
// Save Test and get a testId
int testId = UpdateTest(resultsDataSet); // Adapter method, uses the same transaction
// Update testId in the other tables in the dataset
UpdateTestId(resultsDataSet, testId);
// Update TestResults
UpdateTestResults(resultsDataSet); // Adapter method, uses the same transaction
// Update TestAllocations
UpdateTestAllocations(resultsDataSet); // Adapter method, uses the same transaction
// It is defined in the base class
CommitTransaction("TestTransaction");
}
catch
{
RollbackTransaction("TestTransaction");
}
return resultsDataSet;
}
Now the requirement is to do it for multiple set of data. One way would be to call the above DoTests() method in a loop and get the data. I would prefer doing it in parallel. But there are certain catches:
StartTransaction() method creates a connection (and transaction) every time it is called.
All the underlying database tables, procedures are the same for each call of DoTests(). (obviously).
Thus my question are:
Will using multithreading anyway improve performance?
What are the chances of deadlock especially when new TestId's are being created and the Tests, TestResults and TestAllocations are being saved? How can these deadlocked be handled?
Is there any other more efficient way of doing the above operation apart from looping over the DoTests() method repeatedly?
