I'm working on a web application hosted on multiple load-balanced web front ends (WFEs). This application has an API method that performs a database query and returns the results. This query only performs reads. There is a single database server.
We are finding that when the API method is called in quick succession and the requests are distributed across the WFEs, the database queries that execute will intermittently deadlock.
There are occasional writes to the database that occur from other parts of the system. I haven't yet verified if they are occurring at the same time.
Load is increasing and this issue is likely to occur more frequently over time. We have optimised the query and query transaction settings as best we can.
Is there a more appropriate architecture for this scenario?
(The technologies involved are MS .NET / Web API / SQL Server 2008 R2 although we're open to any options.)
Best Answer
Scaling is hard.
Scaling databases is even harder.
If you have enough calls to often have simultaneous calls to database, even if you can avoid deadlocking it won't solve your problem, as latency will start to increase.
You probably need another database server, and that comes with a ton of stuff to change to work with a cluster. I would take this chance to look into other databases, such as NoSQL databases aor maybe PostGreSQL. They may be a game-changer.
Also, caching models (I.e. something like Redis caching results for the most usual queries) may help a lot.