January 9, 2013 at 8:58 am
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
/******************************************************************************************************/
January 17, 2013 at 4:54 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply