If multiple different services send INSERT queries for the same table simultaneously to SQL Server, using their own connections, how many INSERTS can be committed by SQL Server in parallel? Or SQL Server can only process them sequentially?
How fast is the insert operation depends on the table columns (count, type), its indexes, its foreign keys. The engine may need to check a lot of things in order to leave the database in a consistent state.
Let’s say you are performing only
INSERT operations and not other ones on this table with one column and primary key:
CREATE TABLE [dbo].[DemoUsers] ( [UserID] INT CONSTRAINT [PK_DemoUsers] PRIMARY KEY ); INSERT INTO [dbo].[DemoUsers] ([UserID]) VALUES (101) ,(102) ,(103);
Then in two separate query windows execute the following statements:
BEGIN TRANSACTION; INSERT INTO [dbo].[DemoUsers] ([UserID]) VALUES (107); --COMMIT TRANSACTION; BEGIN TRANSACTION; INSERT INTO [dbo].[DemoUsers] ([UserID]) VALUES (108); --COMMIT TRANSACTION;
It’s like two user are trying to insert a row which is taking some time (because the transaction is not committed):
You can see that IX lock on the same PAGE of the first transaction is not blocking the IX on the page of the other one:
In theory the inserts are not blocking each other and can be executed at the same time.