2

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.)

Alex Angas
  • 681
  • 1
  • 8
  • 19
  • I'm confused. You say that the queries execute and return the results. So these are read queries? Or is there writing taking place? – RibaldEddie Mar 20 '15 at 07:39
  • @RibaldEddie Thanks you make an excellent point, I've updated the question – Alex Angas Mar 22 '15 at 23:05
  • well then reads don't cause deadlocks. Your database software should provide a mechanism for determining which queries are actually deadlocked and then you should examine which resources are under contention. Then you can figure out next steps for ameliorating the contention. – RibaldEddie Mar 22 '15 at 23:40
  • 1
    In other words, there is a write somewhere that you need to track down. Apparently MSSQL has a way to debug deadlocks by showing you a deadlock graph of queries. – RibaldEddie Mar 23 '15 at 00:21

1 Answers1

1

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.

Fernando Cordeiro
  • 264
  • 1
  • 8