0

I currently have a program that creates a bunch of tasks and then wait for them to complete. This is fine and all but it means that sometimes, it will create 50 tasks at once. This causes locking issues on my database. So in order to reduce these errors I would like to limit the number of threads to say, 16.

I've googled a bit and coming from Java, I don't see a way to create something like a "Threadpool" and limiting its size to 16 and then waiting for all the tasks to complete. How can I do this?

nisseHUlt1
  • 11
  • 1
    One way would be to use SemaphoreSlim so throttle the number of concurrent tasks, – Magnus Sep 15 '20 at 11:05
  • Just count how many tasks you're creating, set a max, and don't create more than that at once. If more tasks are still needed, create them as the others finish – ADyson Sep 15 '20 at 11:06
  • 3
    First, you don't need a ThreadPool at all. .NET's task and concurrency structures are higher level abstractions than Java's. If you want to await 50 tasks to complete, use `Task.WhenAll()` on a list with all the tasks. If you want to allow only 50 tasks to run at a time though, you can use any of the constructs that accept a DOP, eg an ActionBlock with a specific DOP>1 – Panagiotis Kanavos Sep 15 '20 at 11:11
  • 2
    *On the other hand* parallel database operations lead to increased locking, not increased performance, as the locks accumulated by connections block each other. The solution is to use batch data and use *fewer* operations. If you want to insert eg 100K rows to a table, use `SqlBulkCopy` instead of 100K individual INSERT requests – Panagiotis Kanavos Sep 15 '20 at 11:12
  • 2
    *Why* do you want to perform 50 concurrent database operations? I have to load thousands of records from parsed MB-sized files every 1 hour. Instead of executing individual inserts, I use Dataflow blocks to identify the files, parse them, batch the records and insert them with SqlBulkCopy at the end, quite similar to how a shell pipeline would work. The step that really needs parallelism is parsing, not database insertion. I use a TransformBlock for this, with a `MaxDegreeOfParallelism` of 3-4 to use multiple cores without freezing the server – Panagiotis Kanavos Sep 15 '20 at 11:13
  • 1
    Queries that lock too many records or worse, long lived transactions though, harm performance and only get *far worse* with multiple transactions. The fix to that is to remove the need for long transactions and overlocking. Quite often, the lack of an index forces the server to lock more rows. Or, if you use database transactions to emulate `transaction per request` you end up *exterminating performance* - that term never referred to *database* transactions. The fix for this is to modify the code so it *doesn't* need long transactions – Panagiotis Kanavos Sep 15 '20 at 11:20
  • I see. Well I found this blog showing a pretty straight forward example using SemaphoreSlim: https://blog.danskingdom.com/limit-the-number-of-c-tasks-that-run-in-parallel/ Yes batches would probably be better, though it would require quite a lot of rewrite of the program that which I don't have time for really. But I will keep this in mind for sure. The operations are...how do I say, not completetly concurrent. The program will read 50 files from disk, due some processing on them and then insert them. The files are in various sizes so some will take 1 second while some might take 5. – nisseHUlt1 Sep 15 '20 at 11:22
  • I might also add that the code is using a bunch of stored proccedures that I cannot change so there's that to. – nisseHUlt1 Sep 15 '20 at 11:30
  • @nisseHUlt1 You could use Polly's [Bulkhead policy](https://github.com/App-vNext/Polly/wiki/Bulkhead) to maximize parallelism. – Peter Csala Sep 15 '20 at 11:54
  • Take a look at this: [How to limit the amount of concurrent async I/O operations?](https://stackoverflow.com/questions/10806951/how-to-limit-the-amount-of-concurrent-async-i-o-operations). – Theodor Zoulias Sep 15 '20 at 11:59

0 Answers0