Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to prevent duplicate rows inserted by SP called simultaneously by different SPIDs (without unique constraint on table) Expand / Collapse
Author
Message
Posted Wednesday, January 09, 2013 8:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 06, 2014 3:57 AM
Points: 21, Visits: 101
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


/******************************************************************************************************/
Post #1404848
Posted Thursday, January 17, 2013 4:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 06, 2014 3:57 AM
Points: 21, Visits: 101
For a solution see here: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d5f87abc-e303-484a-b40f-5639dfbe2efa/
Post #1408337
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse