I have a website which is used by all branches of a store and what it does is that it records customer purchases into a table called myTransactions.myTransactions table has a column named SerialNumber.For each purchase i create a record in the transactions table and assign a serial to it.The stored procedure that does this calls a UDF function to get a new serialNumber before inserting the record.Like below :
Create Procedure mytransaction_Insert
as begin
insert into myTransactions(column1,column2,column3,...SerialNumber)
values( Value1 ,Value2,Value3,...., getTransactionNSerialNumber())
end
Create function getTransactionNSerialNumber
as
begin
RETURN isnull(SELECT TOP (1) SerialNumber FROM myTransactions READUNCOMMITTED
ORDER BY SerialNumber DESC),0) + 1
end
The website is being used by so many users in different stores at the same time and it is creating many duplicate serialNumbers(same SerialNumbers).So i added a Sql transaction with ReadCommitted level to the transaction and i still got duplicate transaction numbers.I changed it to SERIALIZABLE in order to lock the resources and i not only got duplicate transaction numbers(!!HOW!!) but i also got sporadic deadlocks between the same stored procedure calls.This is what i tried : (With ommissions of try catch blocks and rollbacks)
Create Procedure mytransaction_Insert
as begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRASNACTION ins
insert into myTransactions(column1,column2,column3,...SerialNumber)
values( Value1 ,Value2 , Value3, ...., getTransactionNSerialNumber())
COMMIT TRANSACTION ins
SET TRANSACTION ISOLATION READCOMMITTED
end
I even copied the function that gets the serial number directly into the stored procedure instead of the UDF function call and still got duplicate serialNumbers.So,How can a stored procedure line create something Like the c# lock() {} block.
By the way,i have to implement the transaction serial number using the same pattern and i can't change the serialNumber to any other identity field or whatever.And for some reasons i need to generate the serialNumber inside the databse and i can't move SerialNumber generation to application level.
Thank you.
