Wie Sie Tabellen sperren in SQL Server 2005, und sollte ich es überhaupt tun?

Diese wird näher erklärt werden. Was ich getan habe ist, erstellen Sie einen benutzerdefinierten message queue in SQL Server 2005. Ich habe eine Tabelle mit Nachrichten, die Zeitstempel für beide Anerkennung und Abschluss. Die gespeicherte Prozedur, die Anrufer ausführen, erhalten Sie die nächste Nachricht in Ihrer Warteschlange an und bestätigt die Meldung. So weit So gut. Gut, wenn das system erlebt einen massiven Betrag der Transaktionen (in tausend pro minute), ist es nicht möglich, für eine Nachricht, die bestätigt wird durch eine andere Ausführung der gespeicherten Prozedur, während ein anderer bereit ist, zu sich selbst? Lassen Sie mich helfen, indem Sie zeigen, mein SQL-code an die gespeicherte Prozedur:

--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
...
...

Im obigen code konnte nicht eine andere gespeicherte Prozedur ausgeführt in der gleichen Zeit die gleiche id, und versuchen Sie, es zu bestätigen in der gleichen Zeit? Könnte ich (oder sollte ich) implementieren Sie eine Art von sperren, die verhindern, dass eine andere gespeicherte Prozedur aus der Bestätigung der Nachrichten, dass eine andere gespeicherte Prozedur Abfragen?

Wow, hat das überhaupt Sinn? Es ist ein bisschen schwierig, zu den Worten...

möchten Sie vielleicht re-tag, damit "bestpractice" änderungen an Häufig verwendeten "best-practices"

InformationsquelleAutor Kilhoffer | 2008-09-11

sql-server tsql

Sowas

--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
...
...

InformationsquelleAutor SQLMenace

2

Dies scheint die Art von situation, wo OUTPUT nützlich sein kann:
```
-- 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)
...
...
```
Dort, wir aktualisieren und schnappen Sie sich die Zeile in der gleichen operation, was sagt das query optimizer genau, was wir tun, so dass Sie wählen Sie die granulare sperren können und halten es für die kürzeste mögliche Zeit. (Obwohl die Spalte Präfix ist INSERTED, OUTPUT ist wie Trigger, ausgedrückt in der UPDATE wie das löschen der Zeile und setzen Sie die neue ein.)

Bräuchte ich mehr Informationen über Ihre ActionMessages und UnacknowledgedDemands Tabellen (views/TVFs/was auch immer), nicht zu erwähnen, eine bessere Kenntnis von SQL Server für die automatische Verriegelung, zu sagen, ob das and AcknowledgedTime is null - Klausel notwendig ist. Es ist dort zu verteidigen, gegen eine race condition zwischen den sub-select und update. Ich bin mir sicher, es würde nicht notwendig sein, wenn der Auswahl von ActionMessages selbst (z.B. where AcknowledgedTime is null mit einem top auf die update, stattdessen den sub-select auf UnacknowledgedDemands). Ich erwarte auch wenn es unnötig ist, es ist harmlos.

Beachten Sie, dass OUTPUT ist in SQL Server 2005 und höher. Das ist, was Sie sagte, das Sie verwenden, aber wenn die Kompatibilität mit geriatrischen SQL Server 2000-Installationen erforderlich waren, würden Sie wollen einen anderen Weg gehen.

(Ziemlich spät, um der Partei, aber ich sah niemand hatte es erwähnt, also...)

InformationsquelleAutor T.J. Crowder
1

@Kilhoffer:

Den ganzen SQL-batch analysiert wird, vor der Ausführung, also SQL weiß, dass du gehst zu tun eine Aktualisierung der Tabelle auswählen.

Edit: Auch die SQL nicht unbedingt sperren Sie die gesamte Tabelle - es konnte nur sperren Sie die notwendigen Zeilen. Sehen hier für eine übersicht über die sperren in SQL server.

Ich Stimme nicht mit, dass. Analysieren hat nichts damit zu tun. Wenn es das täte, SQL ausführen über EXECUTE wäre nicht Transaktion-sicher.
Es gibt aber nicht AUSFÜHREN hier. Guter Punkt, obwohl - wie funktioniert SQL-wissen, was zu sperren, die beim ausführen von beliebigen SQL über ausführen?
SQL server entscheidet, was zu sperren, und ob Sie das sperren oder die Freigabe, die, wie es passiert ist (wie es läuft jede Anweisung in der Transaktion), NICHT zur Analysezeit. Daher gibt es keinen Unterschied zwischen EXEC-Aufrufe und eine normale batch. Der einzige Unterschied wäre in der Auswahl der Abfrage-plan (ob Cache oder nicht).

InformationsquelleAutor Blorgbeard

Anstelle von expliziten sperren, die oft eskaliert von SQL Server, um eine höhere Granularität als gewünscht, warum nicht einfach versuchen Sie diesen Ansatz:

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 */

Je weniger Sie Schloss die höhere Parallelität, die Sie haben.

InformationsquelleAutor Constantin

0

Sollten Sie wirklich die Verarbeitung, die Dinge ein-by-one? Sollten Sie gerade nicht haben, SQL-Server bestätigen Sie alle unbestätigten Meldungen wird mit dem heutigen Datum, und geben Sie Sie zurück? (alle auch in einer Transaktion natürlich)

Nicht, dass das, was ich mache? SQL Server ist die Anerkennung der Nachrichten, die im Namen der Anrufer und der Rückgabe.
Nein ich meinte so etwas (ungetestet): BEGIN TRANSACTION SELECT * FROM UnacknowledgedDemands; UPDATE ActionMessages SET AcknowledgetTime = getdate() WHERE EXISTS (SELECT * FROM UnacknowledgedDemands WO ActionMessages.ActionMessageId = UnacknowledgedDemands.ActionMessageId) COMMIT TRANSAKTION

InformationsquelleAutor rpetrich
0

Lesen Sie mehr über SQL Server Wählen Sie Sperren hier und hier. SQL Server hat die Möglichkeit, rufen Sie eine Tabellensperre auf eine Auswahl. Nichts wird passieren, um die Tabelle während der Transaktion. Wenn die Transaktion abgeschlossen ist, alle inserts oder updates wird es dann von alleine behoben.

Also das ist zu sagen, dass einfach das einwickeln der obige code in einer Transaktion in SQL Server ist in der Tat umzusetzen, wählen Sie sperren, wie beschrieben in den obigen links?

InformationsquelleAutor Jarrett Meyer
-1

Sie möchten, wickeln Sie Ihren code in eine Transaktion, dann wird in SQL server behandeln Sie sperren die entsprechenden Zeilen oder Tabellen.
```
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
...
```
Wäre es so einfach... ob ein (shared) lock gehalten wird durch die erste Anweisung wird, hängt von der Isolationsstufe der Transaktion, und im besten Fall (sofern das isolation-level solche, die gemeinsame sperren werden gehalten), dann wird dieser code wird zu einem Deadlock führen, da zwei verschiedene verbindungen die gleiche Nachricht erhalten und beide versuchen, ihn zu aktualisieren (dies verhindert, indem die anderen sperren).

InformationsquelleAutor Blorgbeard

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.