TSQL WÄHLEN Sie dann UPDATE in einer Transaktion, dann wieder AUSWÄHLEN

Bin ich wirklich Mühe mit einer Abfrage in meinem ColdFusion-Anwendung (backended zu MS SQL 2008). Ich bekomme den DB-deadlock-Fehler an dieser Transaktion:

<code>
<cftransaction>
   <cfquery name="selectQuery">
      SELECT TOP 20 item_id, field2, field3
      FROM Table1
      WHERE subject_id = #subject_ID# AND lock_field IS NULL AND
            NOT EXISTS (SELECT * FROM Table2 WHERE Table2.user_ID = #user_ID# Table1.item_id = Table2.item_id)
   </cfquery>

   <cfquery name="updateQuery">
      UPDATE Table1
      SET lock_field = 1, locked_by = #user_ID#
      WHERE Table1.item_id IN (#ValueList(selectQuery.item_id#)
   </cfquery>
</cftransaction>
</code>

Bascially, ich habe eine Tabelle (Tabelle1), die für einen großen Schlange der wartenden Elemente. Der Benutzer "zur Kasse" - Elemente, um Ihnen eine Punktzahl. Nur ein Benutzer kann ein Element ausgecheckt zu einer Zeit. Ich brauche auf Anfrage einen block von 20 Elementen, die zu einem Zeitpunkt für einen bestimmten Benutzer. Diese Elemente können bereits überprüft werden und der Benutzer kann bereits erzielte Sie vor (daher der lock_field IST NULL-und not EXISTS-Anweisung im SELECT). Einmal habe ich bestimmt die Liste der 20 item_ids, muss ich dann update der queue-Tabelle zu markieren, die Sie als gesperrt, so dass niemand sonst prüft Sie bei der gleichen Zeit. Ich auch zurückgeben müssen, die Liste der item_ids.

Ich dachte, es funktioniert vielleicht besser, wenn ich zog diese aus einer cftransaction, um eine gespeicherte Prozedur auf dem SQL Server-Seite. Ich bin mir nur nicht sicher, ob die cftransaction Verriegelung stört irgendwie. Ich bin kein TSQL-guru, so dass etwas Hilfe würde geschätzt werden.

  • Haben Sie eine <cflock scope="application"> im Ort zu schützen Sie Ihre UPDATE-Vorgang? Auf diese Weise werden alle concurrency-Probleme sollten Weg gehen.
  • Das update ist Teil der cftransaction. Ich bin mir nicht sicher, ob ein cflock erforderlich ist, nicht?
  • Ein cftransaction lediglich ein Rollback für jede SQL-Anweisung ausgegeben, die innerhalb es, sobald ein Fehler Auftritt. Es sperrt nicht, dass code-Abschnitt.
  • Ja, aber die Verriegelung in Frage nicht-CF-variable sperren, es Datenbank-sperren. Ich bin nicht versuchen, den Zugriff/Aktualisierung der CF-vars in dieses problem.
  • Es ist wahrscheinlich eine Transkription Fehler, aber Sie ' re fehlt eine Klammer in der update-query.
  • Ich Stimme mit Tomalak. Sie haben vermutlich eine race-condition geht. Am besten single-thread-dieses Stück code mit CFLOCK.
  • Lewis: Sie sind nicht nur sperren von Variablen den Zugriff mit <cflock> - Sie stellt sicher, dass nur ein thread kann immer Zugriff auf diesen code-Abschnitt, der zu einem bestimmten Zeitpunkt. Alle anderen threads müssen warten, bis der einen verlassen hat, den gesperrten Abschnitt. Es scheint wie ein guter Fall für single-threaded-Zugriff. Natürlich bedeutet dies die überführung einer Applikation-große, exklusive Sperre.

InformationsquelleAutor Bryan Lewis | 2009-11-16
  1. 12

    Verwendung einer common table expression zu wählen Sie die Daten, danach update der CTE und die Ausgabe der UPDATE-Anweisung. Auf diese Weise ist alles eine einzige operation:

    with cte as (
 SELECT TOP 20 item_id, field2, field3 
 FROM Table1 WITH (ROWLOCK, UPDLOCK)
 WHERE subject_id = #subject_ID# 
 AND lock_field IS NULL 
 AND NOT EXISTS (
   SELECT * FROM Table2 
   WHERE Table2.user_ID = #user_ID# AND Table1.item_id = Table2.item_id))
update cte   
 SET lock_field = 1, locked_by = #user_ID# 
 output inserted.item_id;
    • Danke für die schnelle Antwort. Ich habe gerade realisiert, obwohl, ich brauche, um alle 3 Felder aus der select-Abfrage, nicht nur die item_id. Wie würde das Ihre Antwort?
    • Die AUSGABE eingefügt.item_id, eingefügt.Feld2, eingefügt.feld3
    • Ok, so ich sollte in der Lage sein, diese drop-code in eine neue gespeicherte Prozedur und offensichtlich ändern Sie die #varname# ColdFusion-style-vars @varname gespeichert proc vars und alle sollte gut sein, nicht?
    • Remus, nie benutzt CTEs vor. Eine schnelle google-Suche macht es so Aussehen, wie diese Art von wie für die dynamischen Ansichten, nicht?
    • Ein allgemeiner Tabellenausdruck ist eine andere Schreibweise für eine abgeleitete Tabelle. ZB. statt zu schreiben SELECT ... FROM (SELECT ... FROM table) schreiben Sie WITH cte AS (SELECT ... FROM TABLE) SELECT ... FROM cte und die beiden sind identisch. Mein Beispiel kann umgeschrieben werden, mit abgeleiteten Tabellen, wie Ralph haben. Ich lieber CTE syntax wie ich finde es einfacher zu Lesen und zu befolgen.
    • Da dies in einer einzigen operation, gibt es keine Notwendigkeit für eine TRANSAKTION, richtig?
    • Recht vor, die gesamte Anweisung (SELECT, UPDATE, AUSGABE), läuft in seine eigene implizite Transaktion.
    • Da die Select from Tabelle2 Innenseite der not EXISTS-Klausel kann ein "schmutziges" Lesen, gäbe es irgendeinen Vorteil der Verwendung der WITH (NOLOCK) - Hinweis auf, dass die innere Abfrage?
    • Ich weiß nicht genehmigen, viel dirty reads, Sie führen zu Richtigkeit Probleme sehr viel häufiger als die meisten realisieren. In der Regel Lesen begangen, funktioniert einwandfrei, und in sehr hohen concurent system read committed snapshot kann verwendet werden, um spektakuläre Ergebnisse, w/o jeder der dirty read corectness Probleme: msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx. Wenn Sie wirklich glauben, dass dirty reads sind sicher und geeignet ist, dann verwenden Sie NOLOCK in der inneren wählen.
    • Um es klarzustellen: dirty reads geben würde advatages (und auch caus eproblems...), nur wenn es mit hoher Wahrscheinlichkeit ein update sperren der Tabelle2 Zeilen dieser Abfrage ist interessiert, dh. diejenigen, für #user_id# und die item_id werden aus der Warteschlange entfernt werden. Wenn solche updates auftreten, es wirft einige Fragen über wie werden Ihre Daten/Benutzer-Aktivität paritioned, dh. warum ist eine andere Transaktion aktualisiert das gleiche user_id aufnehmen? WENN Sie feststellen, dass das sperren auftreten, die auf andere user_id record ist eine Angabe table-scans auftreten, und wahrscheinlich sind Sie fehlt ein index.

    InformationsquelleAutor Remus Rusanu
  2. 2

    Nicht zu wissen, viel (sprich: nichts) über PHP, aber habe einige Erfahrung mit TSQL können Sie zu prüfen, ändern Sie Ihre Abfrage, um so etwas wie dieses:

    update TABLE1 set LOCK_FIELD = 1
output inserted.item_id, inserted.OtherInterestingColumnsGoHere
from (select top 20 item_id from TABLE1(holdlock) ) as a
where a.item_id = table1.item_id

    Dies sollte sicherstellen, dass die Elemente, die Ihre auswählen, werden gesperrt, bis das update abgeschlossen ist.

    edit: Hinzugefügt eine output-Klausel der ursprünglichen Frage wollte auch wissen, welche Zeilen aktualisiert wurden.

    • Ja, aber das Problem mit dem setzen der wählen Sie innerhalb des UPDATE ist, dass ich brauchen, um wieder den Inhalt der select-Abfrage als Teil dieser Transaktion.
    • Warum brauchen Sie HOLDLOCK? Nicht die Sperre gehalten werden, da eh du tust ein UPDATE?
    InformationsquelleAutor Ralph Shillington
Schreibe einen Kommentar