Le niveau d'isolation des transactions de Serializable ne fonctionne pas pour moi

Question

J'ai un site web qui est utilisé par toutes les succursales d'un magasin et qui enregistre les achats des clients dans une table appelée myTransactions.myTransactions table has a column named SerialNumber. Pour chaque achat, je crée un enregistrement dans la table des transactions et je lui attribue un numéro de série. La procédure stockée qui fait cela appelle une fonction UDF pour obtenir un nouveau numéro de série avant d'insérer l'enregistrement. Comme ci-dessous :

---

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

---

Le site web est utilisé par un grand nombre d'utilisateurs dans différents magasins en même temps et crée de nombreux numéros de série en double (mêmes numéros de série). J'ai donc ajouté une transaction Sql avec le niveau ReadCommitted à la transaction et j'ai toujours obtenu des numéros de transaction en double. Je l'ai changé en SERIALIZABLE afin de verrouiller les ressources et je n'ai pas seulement obtenu des numéros de transaction dupliqués(!!COMMENT !!) mais j'ai aussi obtenu des blocages sporadiques entre les mêmes appels de procédures stockées. Voici ce que j'ai essayé : (En omettant les blocs try catch et les 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

J'ai même copié la fonction qui obtient le numéro de série directement dans la procédure stockée au lieu de l'appel de la fonction UDF et j'ai toujours obtenu des numéros de série en double. Par ailleurs, je dois implémenter le numéro de série de la transaction en utilisant le même modèle et je ne peux pas changer le numéro de série pour un autre champ d'identité ou autre.Et pour certaines raisons, je dois générer le numéro de série à l'intérieur de la base de données et je ne peux pas déplacer la génération du numéro de série au niveau de l'application.

---

Désolé, mais j'ai déjà essayé sans READUNCOMMITTED dans la fonction et j'obtiens toujours des numéros de série en double.

En ce qui concerne la colonne IDENTITY, je dois dire que cette application va être utilisée par d'autres entreprises qui ont besoin de numéros de série différents et nous ne pouvons pas simplement la changer en identity.

Answer 1

Vous avez READUNCOMMITTED dans l'UDF. Il ignorera alors les verrous exclusifs détenus par d'autres transactions.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE n'est pas la même chose qu'un applock Il ne s'agit pas d'une "section critique" de la base de données, mais simplement d'un contrôle du comportement de verrouillage des instructions suivantes de la transaction.

Retirer le READUNCOMMITTED et il devrait commencer à fonctionner comme prévu.

Bien entendu, cela ne tient pas compte du fait que vous avez essentiellement réimplémenté une fonction IDENTITY colonne. Si vos numéros de série sont vraiment incrémentaux, vous devriez jeter tout cela et le remplacer par un simple IDENTITY colonne. Vous affirmez que vous "ne pouvez pas", mais vous ne justifiez pas cette affirmation ; il me semble que vous êtes presque certain de "pouvoir". peut .

Answer 2

Ce qui vous manque, c'est une contrainte unique (ou clé primaire) pour votre table de transactions. Si c'était le cas, l'entrée dupliquée serait supprimée lorsque vous tenteriez de la valider.

Mais je dirais clairement que vous devriez utiliser la colonne "Identité" (comme l'a dit @Aaronaught) en SQL. Celle-ci commencera à la valeur que vous souhaitez et s'incrémentera vers l'avant ou vers l'arrière. Si vous voulez que vos commandes commencent à un nombre donné, vous pouvez l'avancer. Mais si vous avez besoin d'un identifiant qui est unique et qui se trouve être une valeur entière, alors utilisez l'identité.