1

What are the consequences of inserting new rows (not updating existing) into the same table (SQL Server), from two different programs?

I have a Windows Service, which does a lot of inserts into db (1 insert per second, sometimes faster). I want to make this service scalable (I want to run the same service on many computers). I'm afraid that it can cause problems during those inserts.

If this is the issue, What's the way of handling it? (I'm not asking for "The best" way, so it's not an opinionated question).

My first idea, is to create a new service - "data access service", with the queue. It will be the only service that talks with the database. Other services will connect to that service when they want to insert something. I'm not sure if this is an overkill though. Is there a better way? Or maybe I don't have an issue at all and it's handled by SQL Server (which would be ideal)?

robertwojnar
  • 119
  • 2
  • 10

2 Answers2

2

SQL-Server has been designed for concurrent use. So, no, inserting from different programs is not a problem.

The problem arises if inserts, updates, deletes and selects are interleaved. This can result in inconsistent results. A wise use of transactions is required in such cases.

The Transactions Per Second (TPS) count highly depends on the hardware used, the DB schema, server configuration etc. So I cannot give you exact number on this, but you can expect it to handle several hundreds or even thousands of TPS.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • Hi Oliver, so technically, if the case is inserts only I don't need any transactions, correct? Also.. just to be sure.. inserting from different threads within one program should also be find, correct? – robertwojnar Sep 23 '18 at 18:49
  • Yes. A case where you need transactions is where you transfer money from one account to another one. You would do this with 2 UPDATE commands: `UPDATE accounts SET balance = balance - 100 WHERE account_id = 1` and `UPDATE accounts SET balance = balance + 100 WHERE account_id = 2`. Now, if someone SELECTs the accounts after update one, but before update two, he will miss $100. Therefore the updates should be done inside a transaction with appropriate [isolation level](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017). – Olivier Jacot-Descombes Sep 24 '18 at 10:51
0

Create a stored procedure to insert data and return the scope_identity so you can re-select the data if needed. Within this stored procedure you use transactions.

You can lock the table as you can find in this answer, but you should be fine without. You can block from within a transaction but there is no need to do this.

Here some more tips and advanced tricks, but this can be overwhelming.

Tarabass
  • 3,132
  • 2
  • 17
  • 35
  • Hi Tarabass, so if the case are insert operations only, do I need store procedure? Occasionally I will read from that table, but it's more like polling – robertwojnar Sep 23 '18 at 18:52
  • It's best practice. You don't need them. But stored procedures are fast and accept a list of variables/data. You only have to call that SP with data from several places and the logic will be in one place. It's up to you. – Tarabass Sep 23 '18 at 19:04