2

I'm working on system with a SQL Server database and about 50 clients which are communicating with the database.

In the database, there are two tables, first (orders) has ~1,3 million rows, second (products) has around ~3 million rows. Tables are connected by foreign key (one to many relationship).

There is over 50 clients connected to database which are making select and update without knowing each other. In addition I have one client which is receiving new data (orders and products) over TCP and adds it into database (delete old, insert new). This TCP client adds around 50 new orders every minute.

Each of client has their own connection to database. Connection is established for each operation:

  1. Open
  2. Operation (Insert, Update, Select, Delete, Transaction)
  3. Close

At the beginning there was no problem with those multi connections to database, but now, because of lots of clients, sometimes are deadlocks.

To solve this problem, I plan to write a WCF service with Entity Framework which will be able to connect to the database. All clients will do database actions through WCF service.

What is the best practice to handle multi database connections in web service? How should I prevent for deadlock happening?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Keramus
  • 46
  • 3
  • 1
    IF you use the default settings for WCF, then each request gets its own instance of the WCF service class - so there's no issues with "multiple database connections" in the web service - each service class instance (for a single request) gets its own, separate database connection (and only one) – marc_s Mar 16 '17 at 13:10
  • How do you manage your transactions? – Amit Kumar Ghosh Mar 16 '17 at 13:10
  • Long story cut short, depending on what your specific needs are, you need to implement either optimistic or a pessimistic offline lock. -> http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking – pijemcolu Mar 16 '17 at 13:15
  • @marc_s ok, but to avoid deadlocks I have to use locks to queue requests? Or even if two clients are executing methods from wcf, then the second will wait until the first end request? – Keramus Mar 16 '17 at 13:51
  • That's typically all handled by the database just fine - nothing to worry about. This handles exactly the same as a web application that makes multiple requests (on behalf of multiple users) to a database. – marc_s Mar 16 '17 at 14:00
  • Thanks. I am going to test it. – Keramus Mar 16 '17 at 14:35

0 Answers0