Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 (Deutsch) » SQL Anywhere Server - SQL-Benutzerhandbuch » Transaktionen und Isolationsstufen » Praktische Einführung in Isolationsstufen

 

Praktische Einführung: Einführung in Phantomsperren

In dieser praktischen Einführung haben sowohl der Accountant als auch der Sales Manager Aufgaben, welche die Tabellen "SalesOrder" und "SalesOrderItems" betreffen. Der Accountant muss die Anzahl der Provisionsschecks überprüfen, die an die Vertriebsmitarbeiter ausgezahlt wurden, während der Sales Manager feststellt, dass einige Bestellungen fehlen, und sich anschickt, sie hinzuzufügen.

Voraussetzungen

Sie müssen die Systemprivilegien SELECT ANY TABLE, INSERT ANY TABLE und DELETE ANY TABLE haben.

Für diese praktische Einführung wird davon ausgegangen, dass Sie mit der Beispieldatenbank als Sales Manager und Accountant verbunden sind. Siehe Praktische Einführung: Einrichten des Szenarios für die praktischen Einführungen zur Isolationsstufe.

Hinweis

Damit diese praktische Einführung funktioniert, darf die Option Datenbanksperren automatisch freigeben in Interactive SQL nicht aktiviert sein. Sie können die Einstellung dieser Option überprüfen, indem Sie auf Extras » Optionen klicken und anschließend im linken Fensterausschnitt auf SQL Anywhere.

Kontext und Bemerkungen

In dieser praktischen Einführung werden Phantomsperren gezeigt. Eine Phantomsperre ist eine gemeinsame Sperre, die auf eine indizierte Suchposition gesetzt wird, um Phantomzeilen zu verhindern. Wenn eine Transaktion mit Isolationsstufe 3 Zeilen auswählt, die ein angegebenes Kriterium erfüllen, bringt der Datenbankserver Anti-Einfügesperren an, damit andere Transaktionen keine Zeilen einfügen können, die ebenfalls diesem Kriterium entsprechen. Die Anzahl der für Ihre Transaktion gesetzten Sperren hängt sowohl vom Suchkriterium als auch vom Aufbau der Datenbank ab.

 Aufgabe
  1. Setzen Sie die Isolationsstufe im Fenster des Sales Managers und des Accountants auf 2, indem Sie die folgende Anweisung in jedem Fenster ausführen:

    SET TEMPORARY OPTION isolation_level = 2;
  2. Die Handelsvertreter erhalten monatlich eine Provision, die als Prozentsatz der Umsätze für den jeweiligen Monat berechnet wird. Der Accountant bereitet die Provisionszahlungen für April 2001 vor. Seine erste Aufgabe besteht darin, den Gesamtumsatz jedes Vertreters für diesen Monat zu berechnen. Die Preise, Bestellinformationen und Daten der Angestellten sind in getrennten Tabellen gespeichert. Verknüpfen Sie diese Tabellen unter Verwendung von Fremdschlüsselbeziehungen, um die notwendigen Teilinformationen zusammenzusetzen.

    Führen Sie als Accountant die folgende Anweisung aus:



    SELECT EmployeeID, GivenName, Surname,
       SUM( SalesOrderItems.Quantity * UnitPrice )
          AS "April sales"
    FROM GROUPO.Employees
       KEY JOIN GROUPO.SalesOrders
       KEY JOIN GROUPO.SalesOrderItems
       KEY JOIN GROUPO.Products
    WHERE '2001-04-01' <= OrderDate
       AND OrderDate < '2001-05-01'
    GROUP BY  EmployeeID, GivenName, Surname
    ORDER BY EmployeeID;
    EmployeeID GivenName Surname April sales
    129 Philip Chin 2160.00
    195 Marc Dill 2568.00
    299 Rollin Overbey 5760.00
    467 James Klobucher 3228.00
    ... ... ... ...
  3. Der "Sales Manager" bemerkt, dass ein großer Auftrag von Philip Chin nicht in die Datenbank eingegeben wurde. Philip will seine Provision immer pünktlich erhalten, also gibt der "Sales Manager" den fehlenden Auftrag ein, der am 25. April gebucht wurde.

    Führen Sie als "Sales Manager" die folgenden Anweisungen aus. Die Bestellung und die Elemente werden in getrennten Tabellen eingegeben, da eine Bestellung viele Elemente umfassen kann. Sie sollten zuerst den Eintrag für die Bestellung durchführen, bevor Sie Elemente hinzufügen. Um die referenzielle Integrität aufrechtzuerhalten, gestattet der Datenbankserver eine Transaktion, mit der Elemente einer Bestellung hinzufügt werden, nur dann, wenn diese Bestellung bereits vorhanden ist.

    INSERT into GROUPO.SalesOrders
    VALUES ( 2653, 174, '2001-04-22', 'r1',
          'Central', 129 );
    INSERT into GROUPO.SalesOrderItems
    VALUES ( 2653, 1, 601, 100, '2001-04-25' );
    COMMIT;
  4. Der Accountant kann nicht wissen, dass der Sales Manager gerade einen neuen Auftrag hinzugefügt hat. Wäre der neue Auftrag früher eingegeben worden, so wäre er in der Berechnung von Philip Chins Aprilumsätzen berücksichtigt.

    Berechnen Sie im Fenster des "Accountants" noch einmal den Gesamtumsatz für den Monat April. Verwenden Sie die gleiche Anweisung. Es wird Ihnen auffallen, dass Philip Chins Aprilumsatz plötzlich $4560,00 beträgt.

    EmployeeID GivenName Surname April sales
    129 Philip Chin 4560.00
    195 Marc Dill 2568.00
    299 Rollin Overbey 5760.00
    467 James Klobucher 3228.00
    ... ... ... ...

    Nehmen wir an, dass der Accountant alle im April gebuchten Aufträge markiert, um anzuzeigen, dass die Provision bezahlt wurde. Der vom "Sales Manager" gerade eingegebene Auftrag wird in der zweiten Suchabfrage gefunden und als bezahlt markiert, obwohl er nicht in Phillips Gesamtumsatz für den Monat April berücksichtigt wurde.

  5. Bei Isolationsstufe 3 bringt der Datenbankserver Anti-Einfügesperren an, um zu gewährleisten, dass keine anderen Transaktionen eine Zeile hinzufügen können, die den Such- oder Auswahlkriterien entspricht.

    Führen Sie als Sales Managers die folgenden Anweisungen aus, um den neuen Auftrag zu entfernen:

    DELETE
    FROM GROUPO.SalesOrderItems
    WHERE ID = 2653;
    DELETE
    FROM GROUPO.SalesOrders
    WHERE ID = 2653;
    COMMIT;
  6. Führen Sie als Accountant die folgenden Anweisungen aus:

    ROLLBACK;
    SET TEMPORARY OPTION isolation_level = 3;
  7. Führen Sie die folgende Abfrage aus:



    SELECT EmployeeID, GivenName, Surname,
       SUM( SalesOrderItems.Quantity * UnitPrice )
          AS "April sales"
    FROM GROUPO.Employees
       KEY JOIN GROUPO.SalesOrders
       KEY JOIN GROUPO.SalesOrderItems
       KEY JOIN GROUPO.Products
    WHERE '2001-04-01' <= OrderDate
       AND OrderDate < '2001-05-01'
    GROUP BY  EmployeeID, GivenName, Surname;

    Da Sie die Isolationsstufe auf 3 gesetzt haben, bringt der Datenbankserver automatisch Anti-Einfügesperren an, die gewährleisten, dass der Sales Manager keine April-Aufträge eingeben kann, solange der Accountant seine Transaktion nicht beendet hat.

  8. Versuchen Sie als Sales Manager, Philip Chins fehlenden Auftrag einzugeben, indem Sie die folgende Anweisung ausführen.

    INSERT INTO GROUPO.SalesOrders
    VALUES ( 2653, 174, '2001-04-22',
             'r1','Central', 129 );

    Das Sales Manager-Fenster reagiert nicht mehr und der Vorgang wird nicht abgeschlossen. Klicken Sie in der Symbolleiste auf Stopp, um diesen Eintrag zu unterbrechen.

  9. Der "Sales Manager" kann den Auftrag zwar nicht im April eingeben, aber der Eintrag im Mai sollte doch möglich sein.

    Ändern Sie das Datum in der Anweisung auf den 05. Mai und versuchen Sie es noch einmal.

    INSERT INTO GROUPO.SalesOrders
    VALUES ( 2653, 174, '2001-05-05', 'r1',
          'Central', 129 );

    Das "Sales Manager"-Fenster reagiert nicht mehr. Klicken Sie in der Symbolleiste auf Stopp, um diesen Eintrag zu unterbrechen. Obwohl der Datenbankserver nicht mehr als die notwendigen Sperren zum Vermeiden von Einfügungen anbringt, haben diese Sperren das Potenzial, sich mit vielen Transaktionen zu überlagern.

    Der Datenbankserver bringt Sperren in Tabellenindizes an. Er bringt zum Beispiel eine Phantomsperre in einem Index an, damit keine Zeile direkt davor eingefügt werden kann. Ist aber kein entsprechender Index vorhanden, so muss jede Zeile in der Tabelle gesperrt werden. Es gibt Situationen, in denen Anti-Einfügesperren einige Einfügungen in einer Tabelle verhindern, andere aber erlauben.

  10. Um zu vermeiden, dass die SQL Anywhere-Beispieldatenbank geändert wird, sollten Sie die Änderungen in der Tabelle SalesOrders zurücksetzen. Im Fenster Sales Manager und Accountant führen Sie die folgende Anweisung aus:

    ROLLBACK;
  11. Fahren Sie beide Instanzen von Interactive SQL herunter.

Ergebnisse

Sie haben die praktische Einführung zum Verständnis von Phantomsperren abgeschlossen.

 Siehe auch