How does the database engine handle concurrent inserts?

Question

I tried to deal with this question, but did not find a question like this, so I ask here. This may seem like a null question, but I really don't know how the database handles parallel inserts.

Tell me if 100 users run an insert request. Does the database enter several records at the same time (the same second, the same nanosecond, etc.) Or inserts each record one by one? The database runs in a multi-threaded environment, so the natural response will be inserted at the same time.

Answer 1

A database engine such as SQL Server (which I assume you ask as you tag it) will use some version of the ARIES Write-Ahead Logging protocol . Since such a protocol requires recording a log in the form of a serial stream, as a rule, 100 inserts running simultaneously, even on a 100-processor server, will have to be serialized internally when they access the log. Therefore, concurrency at the "nanosecond" level, as you think, is even theoretically impossible. The topic is described in several places, see Write-Write Transaction Log, Transaction Recovery and How It Works: Presentation of SQL Server I / O in Bob Dorr database .

However, from a practical point of view, you should think about transactions and isolation levels .

Answer 2

Assuming users insert their own transactions, the engine executes each transaction in isolation. When commits make their way to the table, they are randomly ordered based on which thread receives what the cutoff time is before. If users insert data that interferes with table constraints (say, they insert non-unique values ​​in a unique column), the transaction of one random user will succeed, and all others will fail.