Comment verrouiller des tables dans SQL Server 2005, et même si je dois le faire?

Celui-ci va prendre un peu difficile à expliquer. Ce que j'ai fait est de créer un message personnalisé de la file d'attente dans SQL Server 2005. J'ai une table avec des messages qui contiennent des horodateurs pour les deux accusé de réception et d'achèvement. La procédure stockée que les appelants exécuter pour obtenir le message suivant dans la file d'attente reconnaît également le message. So far So good. Eh bien, si le système est confronté à une quantité massive de transactions (en milliers par minute), n'est-il pas possible qu'un message pour être reconnu par l'autre à l'exécution de la procédure stockée pendant que l'autre est prêt à donc lui-même? Permettez-moi de les aider en montrant mon code SQL dans la procédure stockée:

--Grab the next message id
declare @MessageId uniqueidentifier
set @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);

--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId

--Select the entire message
...
...

Dans le code ci-dessus, ne pouvait pas une autre procédure stockée en cours d'exécution dans le même temps obtenir le même id et tenter de le reconnaître en même temps? Pourrais-je (ou devrais-je) de mettre en œuvre une sorte de verrouillage pour empêcher une autre procédure stockée à partir de reconnaître les messages d'une autre procédure stockée est l'interrogation?

Wow, n'a tout de même un sens? C'est un peu difficile à mettre en mots...

vous avez peut-être re-tag-ce donc que "bestpractice" modifications les plus couramment utilisées "les meilleures pratiques"

OriginalL'auteur Kilhoffer | 2008-09-11

sql-server tsql

Quelque chose comme cela

--Grab the next message id
begin tran
declare @MessageId uniqueidentifier
select top 1 @MessageId =   ActionMessageId from UnacknowledgedDemands with(holdlock, updlock);

--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId

-- some error checking
commit tran

--Select the entire message
...
...

OriginalL'auteur SQLMenace

2

Cela semble être le genre de situation où SORTIE peut être utile:
```
-- Acknowledge and grab the next message
declare @message table (
    -- ...your `ActionMessages` columns here...
)
update ActionMessages
set    AcknowledgedTime = getdate()
output INSERTED.* into @message
where  ActionMessageId in (select top(1) ActionMessageId from UnacknowledgedDemands)
  and  AcknowledgedTime is null

-- Use the data in @message, which will have zero or one rows assuming
-- `ActionMessageId` uniquely identifies a row (strongly implied in your question)
...
...
```
Là, nous mettre à jour et de saisir la ligne dans la même opération, qui raconte l'optimiseur de requête exactement ce que nous faisons, en lui permettant de choisir le plus granulaire de verrouillage de il peut et de le maintenir pour les plus brefs délais. (Bien que le préfixe de colonne est INSERTED, OUTPUT est comme les déclencheurs, exprimés en termes de UPDATE être comme la suppression de la ligne et de l'insertion de la nouvelle.)

J'avais besoin de plus d'informations à propos de votre ActionMessages et UnacknowledgedDemands tables (vues/Tvf/whatever), pour ne pas mentionner une plus grande connaissance de SQL Server verrouillage automatique, pour dire si cette and AcknowledgedTime is null article est nécessaire. Il est là pour se défendre contre une condition de concurrence entre les sous-sélection et la mise à jour. Je suis certain qu'il ne serait pas nécessaire si nous étions de la sélection de ActionMessages lui-même (par exemple, where AcknowledgedTime is null avec un top sur le update, au lieu de la sous-sélection sur UnacknowledgedDemands). - Je m'attendre même si c'est inutile, c'est inoffensif.

Noter que OUTPUT est dans SQL Server 2005 et au-dessus. C'est ce que vous avez dit que vous étiez à l'aide, mais si la compatibilité avec les gériatrique SQL Server 2000 installe étaient nécessaires, vous voulez aller dans une autre.

(Très en retard à la fête, mais j'ai vu que personne n'avait mentionné, donc...)

OriginalL'auteur T.J. Crowder
1

@Kilhoffer:

L'ensemble de commandes SQL est analysée avant l'exécution de SQL sait que vous allez faire une mise à jour de la table, ainsi que de sélectionner à partir.

Edit: Aussi, SQL ne sera pas nécessairement de verrouillage de l'ensemble de la table - il pourrait simplement verrouiller le nécessaire lignes. Voir ici pour un aperçu de verrouillage dans SQL server.

Je suis en désaccord avec cela. L'analyse n'a rien à faire avec elle. Si elle le faisait, SQL via EXÉCUTER ne serait pas de transaction sécurisé.
Mais il n'y a pas d'EXÉCUTER ici. Bon point, même si - comment SQL sais quoi de verrouillage lors de l'exécution des requêtes SQL via les exécuter?
SQL server décide quoi de verrouillage, et s'il convient de conserver les serrures ou les libérer, comme il arrive (qu'il s'exécute chaque instruction de la transaction), et non PAS au moment de l'analyse. Donc il n'y a pas de différence entre EXEC appels et de normal de lot. La seule différence serait dans le choix d'un plan de requête (si l'utilisation d'un cache ou pas).

OriginalL'auteur Blorgbeard

Plutôt explicite de verrouillage, ce qui est souvent atteint par SQL Server pour plus de précision que celle souhaitée, pourquoi ne pas essayer cette approche:

declare @MessageId uniqueidentifier
select top 1 @MessageId = ActionMessageId from UnacknowledgedDemands

update ActionMessages
  set AcknowledgedTime = getdate()
  where ActionMessageId = @MessageId and AcknowledgedTime is null

if @@rowcount > 0
  /* acknoweldge succeeded */
else
  /* concurrent query acknowledged message before us,
     go back and try another one */

Le moins vous verrouillez - le plus haut niveau de simultanéité que vous avez.

OriginalL'auteur Constantin

0

Devriez-vous vraiment de traitement les choses une par une? Ne pas disposer de SQL Server reconnaître tous les messages non reconnus avec la date d'aujourd'hui et de les renvoyer? (tous également dans une opération de cours)

N'est pas que ce que je fais? SQL Server est en reconnaissant les messages sur le nom de l'appelant et de les renvoyer.
Non, je voulais dire quelque chose comme ceci (non testé): BEGIN TRANSACTION SÉLECTIONNEZ * à PARTIR de UnacknowledgedDemands; mise à JOUR ActionMessages ENSEMBLE AcknowledgetTime = getdate() OÙ EXISTS (SELECT * from UnacknowledgedDemands OÙ ActionMessages.ActionMessageId = UnacknowledgedDemands.ActionMessageId) COMMIT TRANSACTION

OriginalL'auteur rpetrich
0

Lire plus sur SQL Server Sélectionnez Verrouillage ici et ici. SQL Server a la capacité d'invoquer un verrou de table sur une sélection. Rien n'arrivera à la table lors de la transaction. Lorsque la transaction est terminée, toutes les insertions ou des mises à jour sera alors de résoudre eux-mêmes.

Alors, est-ce à dire que, tout simplement, d'emballage le code ci-dessus dans une transaction, SQL Server, dans les faits, de mettre en œuvre sélectionnez verrouillage tel que décrit dans les liens ci-dessus?

OriginalL'auteur Jarrett Meyer
-1

Vous souhaitez envelopper votre code dans une transaction, SQL server poignée de verrouillage les lignes ou les tables.
```
begin transaction

--Grab the next message id
declare @MessageId uniqueidentifier
set @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);

--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId

commit transaction

--Select the entire message
...
```
Serait-il était si simple... si un (partagé) verrou est détenu par la première instruction dépend du niveau d'isolation de transaction, et dans le meilleur des cas (si le niveau d'isolation est telle que les verrous partagés), alors que le code va entraîner des blocages que deux connexions différentes reçois le même message et les deux essayer de le mettre à jour (chacun empêché de le faire par l'autre de l'écluse).

OriginalL'auteur Blorgbeard

Vous devez vous connecter pour publier un commentaire.