How to prevent duplicate rows inserted by SP called simultaneously by different SPIDs (without unique constraint on table)

Question

A stored procedure "AllocatePaymentToOrderItems" creates rows in a table "Transactions". The intention is that the SP never creates duplicates, i.e. if any records already exist in the table with the same OrderID, PaymentID and TransactionTypeID, the SP should insert zero rows. OrderID, PaymentID and TransactionTypeID are constants either passed in as parameters or calculated inside the SP. The SP code is below.

There is no unique constraint/index on OrderID, PaymentID and TransactionTypeID in the Transactions table - this is intended, because in other scenarios is is legal to have duplicates - they just must never be created by this SP.

There is a check within the SP that no rows already exist at the time the insert is performed.

The problem is that if the SP is called simultaneously with the same parameters by separate SPIDs, duplicates are created. The SPIDs have to reach the insert at exactly the same moment, but this has been seen to happen (usually due to a lock being placed on a dependant table e.g. TransactionTypes - see below, which is then released, allowing SPIDs that were blocked at the same point in the SP to progress).

I have tried all the following but none eliminate the issue:

1. Wrapping the insert in BEGIN TRAN / COMMIT TRAN
2. Setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED, REPEATABLE READ and SERIALIZABLE
3. Using an IF NOT EXISTS (SELECT ....) prior to the insert instead of the LEFT JOIN

I am looking for a solution that can be implemented within the SP. I also would like to avoid solutions that just try to reduce the probability of the issue arising e.g. by reading from the dependant tables without locks. Lastly, there are a high number of reads and writes against the Transactions table, so I have to lock it in the most minimal way.

Any ideas? Many thanks.

PS: this is SQL Server 2008 R2

/******************************************************************************************************/

create procedure dbo.AllocatePaymentToOrderItems
(
@OrderID int,
@PaymentID int
)

as
begin
declare @TransactionTypeID int

select @TransactionTypeID = tr.TransactionTypeID
from TransactionTypes tr
where tr.TransactionTypeCode = 'PAYM'

create table #NewTransactions (
OrderID int not null,
OrderLineNumber int not null,
AmountAllocated decimal(10,2) not null,
primary key (OrderID, OrderLineNumber)
)


/* LOGIC TO POPULATE TEMP TABLE GOES HERE */


-- Add the transactions
insert Transactions (
TransactionDate, 
TransactionTypeID, 
OrderID, 
OrderLineNumber, 
Value, 
PaymentID
)
select 
getdate() as TransactionDate,
@TransactionTypeID,
pa.OrderID,
pa.OrderLineNumber,
pa.AmountAllocated as Value,
@PaymentID
from #NewTransactions pa
left join Transactions t on pa.OrderID = t.OrderID and pa.PaymentID = t.PaymentID and t.TransactionTypeID = @TransactionTypeID
where t.TransactionID is null -- Ensure no equivalent transactions already exist in the order
end
go


/******************************************************************************************************/

---

Laurence

Answer 1

Your INSERT query suggests to me that the duplicates are not related to multiple SPIDs, but in fact are duplicates within the temp table itself.  Are you running any sql to ensure there are no duplicates in the temp table before you insert the data?

Answer 2

I don't know exactly what you tried, but this pattern should cut it:

BEGIN TRANSACTION

DELETE #NewTransactions N
WHERE  EXISTS (SELECT *
               FROM   Transactions T WITH (SERIALIZABLE, UPDLOCK)
               WHERE  N.OrderID = T.OrderID
                 AND  ...

INSERT #NewTransactions (...)
   SELECT * FROM #NewTransactions ...

COMMIT TRANSACTION

However, while this will prevent duplicates, you will have deadlocks all over the place I am afraid. I added UPDLOCK to prevent conversion deadlocks, but the isolation level serializable is prone to cause deadlocks for other reasons.

A alternative approach is to use an application lock. In this case you don't need SERIALIZABLE to get protection against other instances of the same procedure. However, you will not be protected by other process that may insert data simultaneously.

Look up sp_getapplock and sp_releaseapplock in Books Online.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Thanks Erland. Your application lock idea sounds the best, as I could not allow deadlocks in this process.

---

Laurence