数据库引擎如何处理并发插入？ [英] How does database engine handle concurrent inserts?

问题描述

我试图用谷歌搜索这个问题，但是没有发现与此类似的问题，所以我在这里问。这似乎是一个菜鸟问题，但我真的不知道数据库如何处理并发插入。

I tried to google up this question but found no question similar to this so I am asking here. This may seem like a noob question but I really don't know how does database handle concurrent inserts.

请问如果有100位用户启动了插入查询。数据库是同时插入多个记录（相同的秒，相同的纳秒等）还是将每个记录一个接一个地插入？数据库在多线程环境中运行，因此自然的答案是在同一时间触发插入。

Say if 100 users fire up insert query. Does the database insert multiple records at the same time (same second, same nanosecond etc) or it inserts each record one after the other? Database runs in a multithreaded environment so the natural answer would be inserts fire up at the same time.

推荐答案

像SQL这样的数据库引擎服务器（我假设您正在询问，因为您已对其进行了标记）将使用 ARIES的某些版本 预写日志记录协议。由于这样的协议要求将日志作为顺序流写入，因此遵循以下规则：100个并发触发插入操作，即使在100个CPU服务器上，当它们访问日志时也必须在内部进行序列化。因此，正如您所建议的，纳秒级别的并发甚至在理论上是不可能的。在多个地方描述了该主题，请参见预写事务日志，事务恢复和工作原理：Bob Dorr的SQL Server I / O演示文稿。

A database engine like SQL Server (which I assume you are asking about, since you tag it so) will use some version of the ARIES Write-Ahead Logging protocol. Since such a protocol requires the writing of the log as a sequential stream, it follows as a rule that 100 inserts fired concurrently, even on a 100 CPU server will have to serialize internally when they access the log. Therefore concurrency at the 'nanosecond' level, as you suggest, is not even theoretically possible. The topic is described on several places, see Write-Ahead Transaction Log, Transaction Recovery and How It Works: Bob Dorr's SQL Server I/O Presentation.

但是，从实际的角度来看，您应该从交易和隔离级别。

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

这篇关于数据库引擎如何处理并发插入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！