当多个源可以同时插入到表中时，如何最大限度地减少非标识列上的主键冲突？
我们的Server 2012中有一个表用于生成和发送电子邮件。其简化结构如下：
CREATE TABLE [dbo].[EmailRequest]
(
[EmailRequestID] [int] NOT NULL,
[EmailAddress] [varchar](1024) NULL,
[CCEmailAddress] [varchar](1024) NULL,
[EmailReplyToAddress] [varchar](128) NULL,
[EmailReplyToName] [varchar](128) NULL,
[EmailSubject] [varchar](max) NULL,
[EmailBody] [varchar](max) NULL,
[Attachments] [varchar](max) NULL,
[CreateDateTime] [datetime] NULL,
[_EmailSent] [varchar](1) NULL,
[_EmailSentDateTime] [datetime] NULL,
CONSTRAINT [PK_EmailRequest]
PRIMARY KEY CLUSTERED ([EmailRequestID] ASC)
) 我对该表或它所在的数据库没有任何控制权；它是“原样”提供的。
不同的程序和脚本以随机间隔将记录插入到表中。我怀疑他们中的大多数都是通过这样的查询来做到这一点的：
INSERT INTO [dbo].[EmailRequest] ([EmailRequestID], ... <other affected columns>)
SELECT MAX([EmailRequestID]) + 1, <constants somehow generated in advance>
FROM [dbo].[EmailRequest];我运行一个大型SQL脚本，在某些条件下也必须发送电子邮件。就我而言，负责电子邮件的部分如下所示：
INSERT INTO [dbo].[EmailRequest] ([EmailRequestID], ... <other affected columns>)
SELECT MAX([EmailRequestID]) + 1, <values collected from elsewhere>
FROM [dbo].[EmailRequest]
JOIN db1.dbo.table1 ON ...
JOIN db1.dbo.table2 ON ... and so on;"select“部分需要花费时间，因此当它实际插入数据时，计算出来的MAX([EmailRequestID]) + 1值可能会变得多余，并导致主键冲突(罕见的事件，但还是恼人的事件)。
问题是:是否有一种方法来设计查询，以便它稍后计算MAX([EmailRequestID])+1，就在insert之前
备选办法之一可能是：
INSERT INTO [dbo].[EmailRequest] ([EmailRequestID], ... <other affected columns>)
SELECT
(SELECT MAX([EmailRequestID]) + 1
FROM [dbo].[EmailRequest]), <values collected from elsewhere>
FROM db1.dbo.table1
JOIN db1.dbo.table2 ON ... and so on;但我不知道它是否带来任何好处。
因此，可能还有另一个问题:是否有办法查看查询执行的“时间间隔”？
测试是一个挑战，因为没有人向测试数据库发送请求，所以我永远不会在那里得到PK违规。
谢谢。
测试接受的答案的一些惊人的结果。原始(真实)查询的时间-2000.2800毫秒；相同的查询没有插入部分-1200.1800毫秒。注意："select“语句从三个数据库收集信息。
测试查询保留真实的"select“语句(删除如下)：
Declare @mailTable table
(mt_ID int,
mt_Emailaddress varchar(1024),
mt_CCEmailAddress varchar(1024),
mt_EmailSubject varchar(max),
mt_EmailBody varchar(max)
);
insert into @mailTable
select row_number() over (ORDER BY (SELECT NULL)),
am.ul_EMail, ... -- EmailAddress - the rest is removed
FROM <real live tables>;
insert into dbo.EmailRequest
(EmailRequestID, _MessageID, EmailType, EmailAddress, CCEmailAddress,
BulkFlag, EmailSubject, EmailBody, EmailReplyToAddress,
CreateDateTime, SQLServerUpdated, SQLServerDateTime, _EmailSent)
select (select Max(EmailRequestID)+1 from dbo.EmailRequest),
0, '*TEXT', -- _MessageID, EmailType
mt_Emailaddress,
mt_CCEmailAddress,
'N', -- BulkFlag
mt_EmailSubject, -- EmailSubject
mt_EmailBody, -- EmailBody
'', GetDate(), '0', GetDate(), '0'
FROM @mailTable;第一部分运行10次所需时间-- 48 ms (最差)，8次(最佳)；第二部分，可能发生碰撞的时间-- 85 ms (最差)，1 ms (最佳)。
回答 2
Stack Overflow用户
发布于 2018-03-21 02:59:10
我知道这可能不是最理想的解决方案，但为了完整起见，我想添加它。不幸的是，有时我们在处理某些问题上没有太多的选择。
让我以一项免责声明作为开场白：
在非常高并发性的情况下，这可能不能很好地工作，因为它将保持表上的独占锁。在实践中，我使用了这种方法，多达32个并发线程在4台不同的机器上与表交互，这不是瓶颈。如果可能的话，确保这里的事务单独运行。
基本思想是先执行复杂的查询，然后将结果暂时存储在某个地方(本例中的表变量)。然后，在查找最大ID的同时，在表上取一个锁，根据该ID插入记录，然后释放锁。
假设您的表是这样构造的：
CREATE TABLE EmailRequest (
EmailRequestID INT,
Field1 INT,
Field2 VARCHAR(20)
);您可以尝试这样的方法来推动插入：
-- Define a table variable to hold the data to be inserted into the main table:
DECLARE @Emails TABLE(
RowID INT IDENTITY(1, 1),
Field1 INT,
Field2 VARCHAR(20)
);
-- Run the complex query and store the results in the table variable:
INSERT INTO @Emails (Field1, Field2)
SELECT Field1, Field2
FROM (VALUES
(10, 'DATA 1'),
(11, 'DATA 2'),
(15, 'DATA 3')
) AS a (Field1, Field2);
BEGIN TRANSACTION;
-- Determine the current max ID, and lock the table:
DECLARE @MaxEmailRequestID INT = (
SELECT ISNULL(MAX(EmailRequestID), 0)
FROM [dbo].[EmailRequest] WITH(TABLOCKX, HOLDLOCK)
);
-- Insert the records into the main table:
INSERT INTO EmailRequest (EmailRequestID, Field1, Field2)
SELECT
@MaxEmailRequestID + RowID,
Field1,
Field2
FROM @Emails;
-- Commit to release the lock:
COMMIT;如果复杂查询返回大量行(千)，则可能需要考虑使用临时表而不是表变量。
老实说，即使您删除了BEGIN TRANSACTION、COMMIT和锁提示(WITH(TABLOCKX, HOLDLOCK))，这仍然有可能显着地降低您所描述的问题的频率。在这种情况下，上述免责声明将不再适用。
Stack Overflow用户
发布于 2018-03-21 01:01:54
如果不能修复表，您就没有任何好的选择。该表应定义为：
CREATE TABLE [dbo].[EmailRequest](
[EmailRequestID] [int] identity(1, 1) NOT NULL PRIMARY KEY,
. . . 然后，数据库将为每一行生成一个唯一的id。
如果不考虑性能问题，则可以锁定表以防止其他线程写入表。那是个糟糕的主意。
你最好的办法是捕捉错误，然后再试一次。不能保证事情会在什么时候结束，而且您可能最终会遇到不同的线程--所有的死锁。
等等，你还有一件事可以做。你可以用一个序列代替最大的id。如果控制表中的所有插入，则可以创建序列并从该值插入，而不是从表中插入。这将解决性能问题和需要一个独特的id。要真正做到这一点，您可能需要删除数据库，将其重新打开，使用序列设置所有代码，然后再让then。
尽管如此，更好的解决方案是身份主键。
https://stackoverflow.com/questions/49396511
复制相似问题
腾讯云开发者
