Parallel calls to DataAbstractService.SQLGetData

net

(Tim Mayer) #1

I have to execute a lot of queries, and in order to improve performance, I was hoping to run them in parallel using Parallel.ForEach(…). However, when calling DataAbstractService.SQLGetData(sqlText, true, -1) it produces an InvalidOperationException “Cannot release connection because its transaction is still active”.

Is SQLGetData(…) not thread safe?


(antonk) #3

Any non-static methods of the DataAbstractService class are NOT guaranteed to be thread-safe.

Note: This doesn’t mean that the DataService cannot be accessed from several clients simultaneously - in this case Data Abstract will provide its own instance of DataService class to any calling client. However direct calls to the DataService methods from several treads at once will result in undefined behavior.

After all most of these methods end up in calling DB connection methods, that are not thread-safe as well.

Your code should look like

		Parallel.ForEach(queries, query =>
		{
			var svc = new DataService();
			try
			{
				svc.Activate(Guid.Empty, false); // Real session ID should be passed here if the service does require login

				svc.SQLGetData(query, true, -1);
			}
			finally
			{
				svc.Deactivate(Guid.Empty);
				svc.Dispose();
			}
		});

Thanks to built-in connection pooling the overhead of Activate/Deactivate methods can be ignored compared to the resources used by the SQLGetData method call.