Éviter les problèmes de concurrence avec MAX + 1 entier dans SQL Server 2008 ... créer sa propre valeur d'IDENTITÉ

J'ai besoin d'incrémenter un nombre entier dans un Serveur SQL server 2008 colonne.

Dirait que je doit utiliser un IDENTITY colonne, mais j'ai besoin d'incrémenter les compteurs séparés pour chacun de mes clients. Penser à un site de e-commerce où chaque client a son propre incrémentant le numéro de commande, en commençant par 1. Les valeurs doivent être uniques (par client).

Par exemple,

Customer1  (Order #s 1,2,3,4,5...)
Customer2  (Order #s 1,2,3,4,5...)

Essentiellement, j'ai besoin de faire manuellement le travail de SQL identity fonction puisque le nombre de clients est illimité et j'ai besoin de order # des compteurs pour chacun d'entre eux.

Je suis assez à l'aise:

BEGIN TRANSACTION
  SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
  INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

Mon problème est le verrouillage et la simultanéité de préoccupations et d'assurer une valeur unique. Il semble que nous avons besoin de le verrouiller avec TABLOCKX. Mais c'est un fort volume de la base de données et je ne peux pas juste de verrouillage de l'ensemble de la Orders de la table chaque fois que j'ai besoin de faire un SELECT MAX+1 processus et insérer un nouvel enregistrement de commande.

Mais, si je n'ai pas de verrouillage de l'ensemble de la table, alors je risque de ne pas obtenir une valeur unique pour le client. Parce que certains de nos l'entrée de la commande se fait après le fait dans des lots par un multi-fileté de processus de Windows, il est possible que 2 opérations sera à la fois vouloir insérer une nouvelle commande pour le même client.

Alors quelle méthodologie de verrouillage ou de la technique de éviter les blocages et encore permettez-moi de maintenir unique de l'incrémentation des numéros de commande PAR le client?

source d'informationauteur stackonfire

  1. 8

    Je voudrais introduire une table de garder le dernier numéro par client
    pour interroger et mettre à jour dans la même opération avec la génération de la commande.

    TABLE CustomerNextOrderNumber
{
    CustomerID id PRIMARY KEY,
    NextOrderNumber int
}

    Verrou de mise à jour sur sélectionnez aidera à éviter condition de course lorsque les deux commandes sont placées simultanément par le même client. 

    BEGIN TRANSACTION

DECLARE @NextOrderNumber INT

SELECT @NextOrderNumber = NextOrderNumber
FROM  CustomerNextOrderNumber (UPDLOCK)
WHERE CustomerID = @CustomerID

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID


... use number here


COMMIT

    Similaire, mais plus simple d'approche (inspiré par Joachim Isaksson)
    verrou de mise à jour ici est imposée par la première mise à jour.

    BEGIN TRANSACTION

DECLARE @NextOrderNumber INT

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID

SELECT @NextOrderNumber = NextOrderNumber
FROM CustomerNextOrderNUmber
where CustomerID = @CustomerID

    ...

    COMMIT
  2. 3

    Vous pourriez faire ceci:

    BEGIN TRANSACTION
  SELECT ID
  FROM Customer WITH(ROWLOCK)
  WHERE Customer.ID = @ID

  SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
  INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

    Nous sommes maintenant seulement le verrouillage d'un Client de la table clients et pas tous les clients, chaque fois que 2 personnes essayez d'ajouter une commande pour le même client, dans le même temps, celui qui obtient le verrou sur le client en premier gagne et l'autre personne devra attendre.

    Si les gens sont d'insérer des commandes pour des clients différents, ils ne seront pas les uns les autres!

    Ici est de savoir comment cela pourrait fonctionner:

    • User1 commencer à insérer une commande de Client avec l'ID 1000.
    • User2 essaie d'insérer une commande de Client avec l'ID 1000.
    • User2 avoir à attendre jusqu'à ce que User1 terminer l'insertion de la commande.
    • User1 insérer la commande et la transaction est validée.
    • User2 pouvez maintenant insérer la commande et la garantie d'obtenir le vrai max n ° de commande pour la clientèle 1000.
  3. 3

    Dans SQL Server 2005 et versions ultérieures, il est préférable d'effectuer automatiquement, sans l'aide de toute transaction ou de verrouillage:

    update ORDERS 
set OrderNumber=OrderNumber+1 
output inserted.OrderNumber where CustomerID=@ID
  4. 2

    La valeur par défaut de la transaction, read committedne vous protège pas contre les lectures fantômes. Un fantôme de la lecture lorsqu'un autre processus insère une ligne entre votre select et insert:

    BEGIN TRANSACTION
SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

    Même d'un niveau plus élevé, repeatable read, ne vous protège pas. Seul le plus haut niveau d'isolation serializable, protège contre les lectures fantômes.

    Si une solution est le plus haut niveau d'isolation:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
...

    Une autre solution est d'utiliser le tablockx, holdlock et updlock les indicateurs de table à assurez-vous que votre transaction peut modifier la table. La première écluse de la table, la deuxième maintient le verrou jusqu'à la fin de la transaction, et le troisième prend un update verrou pour le sélectionner, de sorte qu'il n'est pas mis à jour plus tard.

    SELECT @NewOrderNumber = MAX(OrderNumber)+1 
From Orders with (tablockx, holdlock, updlock)
where CustomerID=@ID

    Ces requêtes sera rapide si vous avez un index sur CustomerIDje ne voudrais pas trop s'inquiéter de la concurrence, certainement pas si vous avez moins de 10 commandes par minute.

  5. 0

    serait-il possible de créer une table avec un champ d'IDENTITÉ pour chaque client, vous pouvez insérer un nouvel enregistrement dans la table du client et de tirer de la valeur.

  6. 0

    Vous essayez de relier deux des besoins complètement différents.

    Même si vous avez obtenu ce travail. Qu'advient-il si Un Client a une prise de possession plus tôt afin supprimé, allez-vous renumérotation de l'ensemble de leurs dossiers pour les garder et consécutifs à partir de 1. Maintenant, ce serait un verrouillage d'un problème....

    Donner à l'enregistrement d'une identité (ou peut-être un guid) si vous voulez un nombre, une requête pour elle, si vous voulez le numéro de ligne (jamais vu le point de moi-même), l'utilisation rowno.

    Vous n'avez pas besoin d'une auto increementing d'une commande par le client, vous n'en voulez pas, et sans une énorme quantité de verrouillage ne peut pas en avoir un.

    La pensée latérale temps.

    Si vous présentez

    Order Description Date Due
1     Staples     26/1/2012
2     Stapler     1/3/2012
3     Paper Clips 19/1/2012

    cela ne veut pas dire (et, en fait, ne signifie pas pour autant) que l'ordre des touches 1, 2 et 3, ils peuvent être n'importe quoi tant qu'ils remplissent une demande d'unicité.

  7. 0
    create table TestIds
(customerId int,
nextId int)

insert into TestIds
values(1,1)
insert into TestIds
values(2,1)
insert into TestIds
values(3,1)

go

create proc getNextId(@CustomerId int)
as

declare @NextId int

while (@@ROWCOUNT = 0)
begin
    select @NextId = nextId
    from TestIds
    where customerId = @CustomerId

    update TestIds
    set nextId = nextId + 1
    where customerId = @CustomerId
    and nextId = @NextId

end

select @NextId  
go