blocks|key|296805|text|不经意间，我有一种感觉，这些列中有一个或多个接受空值。我希望看到包含约束的表的create语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|296806|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Off the top of my head, I have a feeling one or more of those columns accepts nulls. I would like to see the create statement for the table including the constraint.

blocks|key|3234904|text|RE：“我仍然很好奇为什么原来的实现不能工作。”|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3234905|为什么它会起作用？|3234906|如何防止两个并发事务交错，如下所示？|3234907|Tran+A++++++++++++++++++++++++++++++++Tran+B
---------------------------------------------
SELECT+COUNT(*)...
++++++++++++++++++++++++++++++++++SELECT+COUNT(*)...
INSERT+....
++++++++++++++++++++++++++++++++++INSERT...+(duplicate+key+violation).|code-block|syntax|javascript|3234908|冲突锁的唯一时间是在Insert阶段。|offset|length|style|CODE|3234909|要在SQL事件探查器中查看此信息，请执行以下操作|3234910|创建表脚本|3234911|create+table+MyTable
(
FieldA+int+NOT+NULL,+
FieldB+int+NOT+NULL,+
FieldC+int+NOT+NULL
)
create+unique+nonclustered+index+ix+on++MyTable(FieldA,+FieldB,+FieldC)|3234912|然后将下面的内容粘贴到两个不同的SSMS窗口中。记下连接(x和y)的spid，并设置SQL+Profiler跟踪来捕获锁定事件和用户错误消息。应用spid=x或y且严重性=0的筛选器，然后执行这两个脚本。|3234913|插入脚本|3234914|DECLARE+@FieldA+INT,+@FieldB+INT,+@FieldC+INT
SET+NOCOUNT+ON
SET+CONTEXT_INFO+0x696E736572742074657374

BEGIN+TRY
WHILE+1=1
++++BEGIN

++++++++SET+@FieldA=(+(CAST(GETDATE()+AS+FLOAT)+-+FLOOR(CAST(GETDATE()+AS+FLOAT)))+*+24+*+60+*+60+*+300)
++++++++SET+@FieldB+=+@FieldA
++++++++SET+@FieldC+=+@FieldA

++++++++RAISERROR('beginning+insert',0,1)+WITH+NOWAIT
++++++++INSERT+INTO+MyTable+(FieldA,+FieldB,+FieldC)
++++++++SELECT+FieldA=@FieldA,+FieldB=@FieldB,+FieldC=@FieldC
++++++++WHERE+(SELECT+COUNT(*)+FROM+MyTable+WHERE+FieldA=@FieldA+AND+FieldB=@FieldB+AND+FieldC=@FieldC+)+=+0
++++END
END+TRY
BEGIN+CATCH
++++DECLARE+@message+VARCHAR(500)
++++SELECT+@message+=+'in+catch+block+'+%2B+ERROR_MESSAGE()
++++RAISERROR(@message,0,1)+WITH+NOWAIT
++++DECLARE+@killspid+VARCHAR(10)+
++++SELECT+@killspid+=+'kill+'+%2BCAST(SPID+AS+VARCHAR(4))+FROM+sys.sysprocesses+WHERE+SPID!=@@SPID+AND+CONTEXT_INFO+=+(SELECT+CONTEXT_INFO+FROM+sys.sysprocesses+WHERE+SPID=@@SPID)
++++EXEC+(+@killspid+)
END+CATCH|3234915|entityMap^0|0|0|0|0|A|6|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|18|8|@$M|19|N|1A|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|1D|8|@]|9|@]|A|$I|J]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1F|8|@]|9|@]|A|$]]|$1|10|3|11|5|H|7|1G|8|@]|9|@]|A|$I|J]]|$1|12|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|13|$]]

RE: &quot;I'm still curious as to why the original implementation doesn't work.&quot;
Why would it work?
What is there to prevent two concurrent transactions being interleaved as follows?
<pre><code>Tran A Tran B
---------------------------------------------
SELECT COUNT(*)...
 SELECT COUNT(*)...
INSERT ....
 INSERT... (duplicate key violation).
</code></pre>
The only time conflicting locks will be taken is at the <code>Insert</code> stage.
To see this in SQL Profiler
<h2>Create Table Script</h2>
<pre><code>create table MyTable
(
FieldA int NOT NULL, 
FieldB int NOT NULL, 
FieldC int NOT NULL
)
create unique nonclustered index ix on MyTable(FieldA, FieldB, FieldC)
</code></pre>
Then paste the below into two different SSMS windows. Take a note of the spids of the connections (x and y) and set up a SQL Profiler Trace capturing locking events and user error messages. Apply filters of spid=x or y and severity = 0 and then execute both scripts.
<h2>Insert Script</h2>
<pre><code>DECLARE @FieldA INT, @FieldB INT, @FieldC INT
SET NOCOUNT ON
SET CONTEXT_INFO 0x696E736572742074657374

BEGIN TRY
WHILE 1=1
 BEGIN

 SET @FieldA=( (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 24 * 60 * 60 * 300)
 SET @FieldB = @FieldA
 SET @FieldC = @FieldA

 RAISERROR('beginning insert',0,1) WITH NOWAIT
 INSERT INTO MyTable (FieldA, FieldB, FieldC)
 SELECT FieldA=@FieldA, FieldB=@FieldB, FieldC=@FieldC
 WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA=@FieldA AND FieldB=@FieldB AND FieldC=@FieldC ) = 0
 END
END TRY
BEGIN CATCH
 DECLARE @message VARCHAR(500)
 SELECT @message = 'in catch block ' + ERROR_MESSAGE()
 RAISERROR(@message,0,1) WITH NOWAIT
 DECLARE @killspid VARCHAR(10) 
 SELECT @killspid = 'kill ' +CAST(SPID AS VARCHAR(4)) FROM sys.sysprocesses WHERE SPID!=@@SPID AND CONTEXT_INFO = (SELECT CONTEXT_INFO FROM sys.sysprocesses WHERE SPID=@@SPID)
 EXEC ( @killspid )
END CATCH
</code></pre>

blocks|key|3235064|text|为什么这个不起作用？|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3235065|我相信SQL+Server的默认行为是在不再需要共享锁时立即释放它们。您的子查询将导致表上的一个短暂的共享(S)锁，该锁将在子查询完成后立即释放。|3235066|在这一点上，没有什么可以阻止并发事务插入您刚刚验证不存在的行。|3235067|我需要做什么修改才能不会因为违反约束而出现异常？|3235068|将HOLDLOCK提示添加到子查询将指示SQL+Server持有该锁，直到事务完成。(在您的示例中，这是一个隐式事务。)HOLDLOCK提示等同于SERIALIZABLE提示，它本身等同于您在“其他方法”列表中提到的可序列化事务隔离级别。|CODE|3235069|仅HOLDLOCK提示就足以保留S锁，并防止并发事务插入您要防止的行。但是，您很可能会发现唯一的键冲突错误被死锁取代，并以相同的频率发生。|3235070|如果只保留表上的S锁，请考虑两次并发尝试之间的竞争，以锁步方式进行插入同一行--两次尝试都成功地获取了表上的S锁，但都不能成功获取执行插入所需的独占(X)锁。|3235071|幸运的是，对于这种情况，还有另一种锁类型，称为Update+(U)锁。U锁与S锁相同，但有以下区别:在同一资源上可以同时持有多个S锁，但一次只能持有一个U锁。(换句话说，虽然S锁彼此兼容(即可以无冲突地共存)，但U锁彼此不兼容，但可以与S锁共存；沿着频谱，排它(X)锁与S锁或U锁都不兼容)|3235072|您可以使用UPDLOCK提示将子查询上的隐式S锁升级为U锁。|3235073|在表中插入同一行的两次并发尝试现在将在初始select语句中序列化，因为这将获取(并持有)一个U锁，该锁与来自并发插入尝试的另一个U锁不兼容。|3235074|空值|3235075|由于FieldC允许空值的事实，可能会出现另一个问题。|3235076|如果ANSI_NULLS为on+(默认值)，则相等检查FieldC=NULL将返回false，即使在FieldC为NULL的情况下也是如此(当ANSI_NULLS为on时，必须使用IS+NULL运算符检查null+)。由于FieldC可以为空，因此在插入空值时将不会执行重复检查。|3235077|要正确处理空值，您需要修改Existes子查询，以便在插入空值时使用IS+NULL操作符，而不是=。(或者，您可以更改该表，以禁止所有相关列中的空值。)|3235078|SQL+Server联机丛书参考|3235079|3235080|Locking+Hints|unordered-list-item|3235081|Lock+Compatibility+Matrix|3235082|ANSI_NULLS|3235083|3235084|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms187373.aspx|1|http://msdn.microsoft.com/en-us/library/ms186396.aspx|2|http://msdn.microsoft.com/en-us/library/ms188048.aspx^0|0|A|0|0|0|0|O|0|1|8|1O|8|21|C|0|1|8|0|0|0|5|7|0|0|0|2|0|0|2|A|R|B|1Z|A|2I|7|0|Y|7|1C|1|0|0|G|0|0|0|D|0|0|0|P|1|0|0|A|2|0|0^^$0|@$1|2|3|4|5|6|7|1T|8|@$9|1U|A|1V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1W|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|1X|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|1Y|8|@$9|1Z|A|20|B|C]]|D|@]|E|$]]|$1|L|3|M|5|6|7|21|8|@$9|22|A|23|B|N]|$9|24|A|25|B|N]|$9|26|A|27|B|N]]|D|@]|E|$]]|$1|O|3|P|5|6|7|28|8|@$9|29|A|2A|B|N]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|2B|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|2C|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|2D|8|@$9|2E|A|2F|B|N]]|D|@]|E|$]]|$1|W|3|X|5|6|7|2G|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|2H|8|@$9|2I|A|2J|B|C]]|D|@]|E|$]]|$1|10|3|11|5|6|7|2K|8|@]|D|@]|E|$]]|$1|12|3|13|5|6|7|2L|8|@$9|2M|A|2N|B|N]|$9|2O|A|2P|B|N]|$9|2Q|A|2R|B|N]|$9|2S|A|2T|B|N]]|D|@]|E|$]]|$1|14|3|15|5|6|7|2U|8|@$9|2V|A|2W|B|N]|$9|2X|A|2Y|B|N]]|D|@]|E|$]]|$1|16|3|17|5|6|7|2Z|8|@$9|30|A|31|B|C]]|D|@]|E|$]]|$1|18|3|-4|5|6|7|32|8|@]|D|@]|E|$]]|$1|19|3|1A|5|1B|7|33|8|@]|D|@$9|34|A|35|1|36]]|E|$]]|$1|1C|3|1D|5|1B|7|37|8|@]|D|@$9|38|A|39|1|3A]]|E|$]]|$1|1E|3|1F|5|1B|7|3B|8|@]|D|@$9|3C|A|3D|1|3E]]|E|$]]|$1|1G|3|-4|5|6|7|3F|8|@]|D|@]|E|$]]|$1|1H|3|-4|5|6|7|3G|8|@]|D|@]|E|$]]]|1I|$1J|$5|1K|1L|1M|E|$1N|1O]]|1P|$5|1K|1L|1M|E|$1N|1Q]]|1R|$5|1K|1L|1M|E|$1N|1S]]]]

Why doesn't this work?

I believe the default behaviour of SQL Server is to release shared locks as soon as they are no longer needed. Your sub-query will result in a short-lived shared (S) lock on the table, which will be released as soon as the sub-query completes.

At this point there is nothing to prevent a concurrent transaction from inserting the very row you just verified was not present.

What modification do I need to make so there is no chance of an exception due to the constraint violation?

Adding the <code>HOLDLOCK</code> hint to your sub-query will instruct SQL Server to hold on to the lock until the transaction is completed. (In your case, this is an implicit transaction.) The <code>HOLDLOCK</code> hint is equivalent to the <code>SERIALIZABLE</code> hint, which itself is equivalent to the serializable transaction isolation level which you refer in your list of "other approaches".

The <code>HOLDLOCK</code> hint alone would be sufficient to retain the S lock and prevent a concurrent transaction from inserting the row you are guarding against. However, you will likely find your unique key violation error replaced by deadlocks, occurring at the same frequency.

If you're retaining only an S lock on the table, consider a race between two concurrent attempts to insert the same row, proceeding in lockstep -- both succeed in acquiring an S lock on the table, but neither can succeed in acquiring the Exclusive (X) lock required to execute the insert.

Luckily there is another lock type for this exact scenario, called the Update (U) lock. The U lock is identical to an S lock with the following difference: whilst multiple S locks can be held simultaneously on the same resource, only one U lock may be held at a time. (Said another way, whilst S locks are compatible with each other (i.e. can coexist without conflict), U locks are not compatible with each other, but can coexist alongside S locks; and further along the spectrum, Exclusive (X) locks are not compatible with either S or U locks)

You can upgrade the implicit S lock on your sub-query to a U lock using the <code>UPDLOCK</code> hint.

Two concurrent attempts to insert the same row in the table will now be serialized at the initial select statement, since this acquires (and holds) a U lock, which is not compatible with another U lock from the concurrent insertion attempt.

NULL values

A separate problem may arise from the fact that FieldC allows NULL values.

If <code>ANSI_NULLS</code> is on (default) then the equality check <code>FieldC=NULL</code> would return false, even in the case where FieldC is NULL (you must use the <code>IS NULL</code> operator to check for null when <code>ANSI_NULLS</code> is on). Since FieldC is nullable, your duplicate check will not work when inserting a NULL value.

To correctly deal with nulls you will need to modify your EXISTS sub-query to use the <code>IS NULL</code> operator rather than <code>=</code> when a value of NULL is being inserted. (Or you can change the table to disallow NULLs in all the concerned columns.)

SQL Server Books Online References

<ul>
<li><a href="http://msdn.microsoft.com/en-us/library/ms187373.aspx" rel="noreferrer">Locking Hints</a></li>
<li><a href="http://msdn.microsoft.com/en-us/library/ms186396.aspx" rel="noreferrer">Lock Compatibility Matrix</a></li>
<li><a href="http://msdn.microsoft.com/en-us/library/ms188048.aspx" rel="noreferrer">ANSI_NULLS</a></li>
</ul>

I'm inserting into a SQL database from multiple processes. It's likely that the processes will sometimes try to insert duplicate data into the table. I've tried to write the query in a way that will handle the duplicates but I still get:

<pre><code>System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.
</code></pre>

My query looks something like:

<pre><code>INSERT INTO MyTable (FieldA, FieldB, FieldC)
SELECT FieldA='AValue', FieldB='BValue', FieldC='CValue'
WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA='AValue' AND FieldB='BValue' AND FieldC='CValue' ) = 0
</code></pre>

The constraint 'UK1_MyConstraint' says that in MyTable, the combination of the 3 fields should be unique.

My questions:

<ol>
<li>Why doesn't this work?</li>
<li>What modification do I need to make so there is no chance of an exception due to the constraint violation? </li>
</ol>

Note that I'm aware that there are other approaches to solving the original problem of "INSERT if not exists" such as (in summary):

<ul>
<li>Using TRY CATCH</li>
<li>IF NOT EXIST INSERT (inside a transaction with serializable isolation)</li>
</ul>

Should I be using one of the approaches?

Edit 1 SQL for Creating Table:

<pre><code>CREATE TABLE [dbo].[MyTable](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [FieldA] [bigint] NOT NULL,
 [FieldB] [int] NOT NULL,
 [FieldC] [char](3) NULL,
 [FieldD] [float] NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
 (
 [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [UK1_MyTable] UNIQUE NONCLUSTERED 
 (
 [FieldA] ASC,
 [FieldB] ASC,
 [FieldC] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
</code></pre>

Edit 2 Decision:

Just to update this - I've decided to use the "JFDI" implementation suggested in the linked question (<a href="https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there/3408196#3408196">link</a>). Although I'm still curious as to why the original implementation doesn't work.

Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005

SQL数据库

SQL Server

数据库

我从多个进程插入到SQL数据库中。这些进程有时可能会尝试将重复数据插入到表中。我尝试用一种可以处理重复项的方式来编写查询，但我仍然得到：System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot...

问违反SQL Server 2005上INSERT WHERE COUNT(*) =0上的唯一键约束
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问违反SQL Server 2005上INSERT WHERE COUNT(*) =0上的唯一键约束EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问违反SQL Server 2005上INSERT WHERE COUNT(*) =0上的唯一键约束
EN