SQL Server MERGE command duplicate pk error
This stored procedure often fails if transactions happen simultaneusly, because it violates a pk constraint on duplicate key (field, name). I have been trying, holdlock, rowlock and begin/commit transaction, which result in the same error.
I am trying to perform an insert statement and if the record exist with the same key (field, name) update it instead.
Performance is important so i am trying to avoid a temp table solution.
MERGE Fielddata AS TARGET
USING (VALUES (@Field, @Name, @Value, @File, @Type))
AS SOURCE (Field, Name, Value, File, Type)
ON TARGET.Field = @Field AND TARGET.Name = @Name
WHEN MATCHED THEN
UPDATE
SET Value = SOURCE.Value,
File = SOURCE.File,
Type = SOURCE.Type
WHEN NOT MATCHED THEN
INSERT (Field, Name, Value, File, Type)
VALUES (SOURCE.Field, SOURCE.Name, SOURCE.Value, SOURCE.File, SOURCE.Type);
EDIT: Testing with serializable/holdlock for 24 hours. After 30 mins: no errors.
EDIT 2: WITH (SERIALIZABLE) / SET TRANSACTION ISOLATION LEVEL SERIALIZABLE solves the duplicate key problem effectively, costing a little bit of performance in our case/scenario.
You must increase the level of transaction isolation. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SERIALIZABLE Specifies the following: + Statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes. Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Serializable Implementations
SQL Server happens to use a locking implementation of the serializable isolation level, where physical locks are acquired and held to the end of the transaction (hence the deprecated table hint HOLDLOCK as a synonym for SERIALIZABLE).
This strategy is not quite enough to provide a technical guarantee of full serializability, because new or changed data could appear in a range of rows previously processed by the transaction. This concurrency phenomenon is known as a phantom, and can result in effects which could not have occurred in any serial schedule.
To ensure protection against the phantom concurrency phenomenon, locks taken by SQL Server at the serializable isolation level may also incorporate key-range locking to prevent new or changed rows from appearing between previously-examined index key values. Range locks are not always acquired under the serializable isolation level; all we can say in general is that SQL Server always acquires sufficient locks to meet the logical requirements of the serializable isolation level. In fact, locking implementations quite often acquire more, and stricter, locks than are really needed to guarantee serializability, but I digress.
https://sqlperformance.com/2014/04/t-sql-queries/the-serializable-isolation-level
if it is simple: it blocks not only the source but also the range for insertion
链接地址: http://www.djcxy.com/p/32884.html上一篇: 为Browserify定义全局变量