I apologize as this issue almost sounds and reads like one of the questions I remember on one of my SQL Server certification exams from years back!
I have inherited a .Net Client-Server app with single sql server db on the back end.
Users make changes in the app at client machine then clicks save, the app's UI moves on and then writes background tasks (long running) still to process in the BackgrounTasks table and the the user can move on and do other things in the UI.
The background tasks in the BackgrounTasks table are then processed by a windows service(s). Currently, the windows service runs either on the server with multiple threads or each client machine also has their own version of this service running that has one thread only.
The problem is everyone/everything is fighting over this BackgrounTasks table and deadlocking on it as everything is either reading or writing to it all the time. This model/method scales horribly as more users = more deadlocks.
The SQL DB setup uses pretty much all default options as far as collation and read-committed isolation (not read-committed snapshot, so I know read only trans are adding to the lock contention).
My first thought is to get rid of the background processing at the client machines and move that to just a single multi-threaded windows service on the server so there is one controlling unit and remove a lot of variables of what kind of client hardware, network perf, etc. Resources and connectivity at the client machines are far from ideal. Also, putting a version of the background processing windows service on each of the clients was a late addition thought to alleviate bottlenecks but has actually caused the issues to get worse. I am looking for design ideas to minimize contention at the db level and eliminate the deadlocks and something that will scale well with more users.
Any help is greatly appreciated!
No comments:
Post a Comment