Sql server - 如果我尝试通过并发进程将数据插入表中会有问题吗？ [英] Sql server - will there be a problem if I try to insert data into a table via concurrent processes?

问题描述

您好。

只是想知道当多个用户试图同时将他们的数据插入到一个表中时，是否有人遇到过问题。表具有名为ID的唯一标识列，并且只要插入记录，它就会自动递增其自身的值。如果用户会尝试同时插入数据，比如20个并发用户，会不会导致表格内的死锁？



我尝试了什么：



试图模拟3个并发会话，每个会话插入10,000个唯一记录，虽然处理速度比我实际预期的要慢，但没有出现任何问题。但是我们的生产服务器最多支持200个用户，预计最大并发会话数大约为100，而且我没有那些模拟资源可以用来对我的应用程序进行压力测试。希望你指点我正确的方向帮助我。谢谢！ ：）

Hi.
Just wondering if any of you have encountered issues when multiple users are trying to insert their data into a single table concurrently. Table has unique identity column named ID and it auto-increments its own value whenever a record is inserted. if users will try to insert data simultaneously, say 20 concurrent users, will it cause a deadlock within the table itself?

What I have tried:

Tried to simulate 3 concurrent sessions inserting 10,000 unique records each, nothing problematic occurred though processing is slower than I actually anticipated. But our production server supports up to 200 users with an expected max concurrent sessions of around 100, and I don't have that simulation resources to play with to stress test my app. Hope you could help me by pointing me to the right direction. Thanks! :)

推荐答案

所有基于服务器的SQL Server本身都处理并发连接和操作。您无需在代码中执行任何操作即可确保其正常工作。



实际上，您实际上不得不自行解决问题，例如在代码中分配您自己的主键值，而不是让SQL服务器执行它。而男人，诺布只是喜欢走出困境，搞砸了事情，并且竭尽所能。

All server-based SQL Servers handle concurrent connections and operations themselves. You don't have to do anything in your code to ensure this works.

In fact, you actually have to go out of your way to screw things up, like assigning your own primary key values in code instead of letting the SQL server do it. And man, noob's just love to go out of their way, screw things up, and do everything the hard way.

死锁是一个不同的概念。如果



- 用户A更新表1中的第11行

- 用户B更新表2中的第22行

- 用户B尝试更新表1中的第11行。现在它必须等待，因为该行被用户A锁定。



以上是一个死锁。两个用户都在等待来自其他人的资源，并且都不愿意发布它自己的资源。



问题本身是什么。如果插入表中，即使多个用户同时插入同一个表，也应该没有问题。这是数据库中常见的情况。



但是，您可能遇到的一些问题

- 操作因锁定等待而变慢。即使行不重复，也会发生这种情况。例如索引锁可能导致等待

- 如果不在快速设备上，事务日志处理成为瓶颈

- 如果内存的太大部分包含脏，则内存处理会降低操作速度页面无法在磁盘上刷新

- 依此类推......



大多数潜在问题都与服务器端资源。因此，确保资源（内存，CPU，磁盘速度......）足够应该解决大多数问题。如果操作主要是更新，请注意索引量，如果不是真的需要请不要过度索引。



也许最大的问题可归结为个人交易。事务必须强制执行数据完整性，但保持尽可能短的帮助。在创建客户端时应该考虑这一点。例如，如果单个事务包含多个插入，请不要从客户端单独执行它们。相反，创建一个存储过程，该过程将数组作为参数并在单个调用期间插入所有行。

Deadlock is a different concept. In a simple for deadlock occurs if

- user A updates row 11 in table 1
- user B updates row 22 in table 2
- user B tries to update row 11 in table 1. Now it has to wait because the row is locked by user A
- user A tries to update row 22 in table 2. Now it has to wait because the row is locked by user B

The above is a deadlock. Both users are waiting for a resource from someone else and neither is willingly releasing it's own resources.

What comes to the question itself. If you insert into a table you should have no problems even if multiple user insert into the same table at the same time. This is a common scenario in databases.

However, some problems you might encounter
- operations slow down because of lock waits. This will happen even if the rows are not duplicates. For example index locks may cause waits
- transaction log handling becomes the bottleneck if not on a fast device
- memory handling slows operations if too big portion of the memory contains dirty pages without being able to flush them on the disk
- and so on...

Most of the potential problems are related to the server side resources. So ensuring that the resources (memory, CPU, disk speed...) are adequate should tackle most questions. If the operations are mainly updates, be careful with the amount of indexes, do not over index if not really needed.

Perhaps the biggest question comes down to the length of individual transactions. Transaction must enforce data integrity but keeping them as short as possible helps. This is something that should be taken into consideration when creating the client side. For example if a single transaction contains multiple inserts, do not execute them separately from the client side. Instead, create a stored procedure which takes an array as a parameter and insert all the rows during a single call.

这篇关于Sql server - 如果我尝试通过并发进程将数据插入表中会有问题吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！