Transaction Isolation Level of Serializable not working for me

Question

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.

Answer 1

You have READUNCOMMITTED in the UDF. This will cause it to ignore any exclusive locks held by other transactions.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE is not the same as an applock, it is not a "critical section" in the database, it just controls the locking behaviour of subsequent statements in the transaction.

Take out the READUNCOMMITTED and it should start working as you expect.

Of course, this ignores the fact that you've essentially re-implemented an IDENTITY column. If your serial numbers are really incremental then you should throw all of this away and replace it with a simple IDENTITY column. You claim that you "can't", but don't provide any justification for that statement; it looks to me like you almost certainly can.

Answer 2

Sorry but I have already tried this without READUNCOMMITTED in the function and i still get duplicate SerialNumbers. As for the IDENTITY column,i should say that this app is going to be used by other companies that require different SerialNumbers and we can't just simply change it to identity.

Answer 3

What you are missing is a unique constraint (or primary key) down your transactions table. If you had that the duplicate entry would back out when you attempt to commit it.

But I would state clearly that you should use the "Identity" (as said by @Aaronaught) column in SQL. This will start at whatever you want it to and increment forward or backward. If you need your orders to start at a given number then forward it. But if you need an identifier that is unique and also happens to be an integer value then use identity.