Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to prevent duplicate rows inserted by SP called simultaneously by different SPIDs (without...


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

Author
Message
Laurence Neville
Laurence Neville
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 182
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 Neville
Laurence Neville
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 182
For a solution see here: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d5f87abc-e303-484a-b40f-5639dfbe2efa/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search