Lesezugriff auf Zeile sperren InnoDB( MySQL )

[Carnie]

Ich kämpf seit einiger Zeit mit einer meiner Datenbanken und bräuchte da mal eure Hilfe ob ich da was übersehen habe oder es garnicht gehen kann.

verwendete Version; mysql Ver 14.12 Distrib 5.0.26, for suse-linux-gnu (i686)

Betriebssystem ist ein Linux 10.2 aber sollte keine Rolle spielen.

Tabellenaufbau(zum testen) : Eine Spalte und zehn Reihen mit den Zahlen von 1-10.

Erreichen möchte ich das eine zweite Transaktion erst dann die höchste ID ausliest wenn eine vorhergehende Transaktion beendet ist. Natürlich könnte ich die ganze Tabelle dafür sperren aber da dies Bestandteil eines grösseren Projektes werden soll will ich dies vermeiden und nur die besagten Zeilen sperren. Mein Ansatz:

Transaktion A starten:

Transaktion B starten:

Transaktion A; SELECT * from tabelle where nummer>5 FOR UPDATE;

(dies sollte laut meinem Verständnis durch das FOR UPDATE bewirken das Transaktion A exklusiven Zugriff auf diese Ergebnisszeilen erhält)

Transaktion B: SELECT * from tabelle where nummer<5 FOR UPDATE;

(funktioniert auch wie erwartet)

Transaktion B: SELECT * from tabelle where nummer>5 FOR UPDATE;

(wartet auf Transaktion A)

bis hier funktioniert es noch so wie geplant.

Wenn ich nun jedoch ein Insert mache in Transaktion A hängen beide Transaktionen weil B wartet auf die Freigabe(commit) vom SELECT in Transaktion A und das Insert in Transaktion A wartet auf das Commit vom SELECT in .

Wie erreiche ich das der INSERT Befehl durchläuft und nicht auf Transaktion B wartet. Isolation Level hab ich sowohl READ COMMITTED als auch das STANDARD REPEATABLE READ ausprobiert. Autocommit ist auch auf 0.Die Dokumentation von Mysql hat mir bisher auch nach xmal lesen nicht weitergeholfen.

[Carnie]

Ok das schien nen Bug mit der Mysql Version zu sein. Zu Hause geht es besser.

Jedoch erhalte ich in der 2. Transaktion nach dem ich Transaktion 1. comittet habe den ursprünglichen Wert vor Transaktion 1 und nicht den danach. Das sollte doch ansich durch READ COMITTED vermieden werden oder `?

[dr.dimitri]

Du hast seltsame Anforderungen. Was genau ist denn der Grund dafür das Du das so machen möchtest?

[Carnie]

Das sind keine seltsamen Anforderungen sondern bei einem grösserem System mit vielen simultanen Anfragen leider notwendig ist. .Es geht darum das ich in abhängigkeit davon wieviele Datensätze bereits in der Datenbank sind weitere Aktionen durchführe und davon auch abhängt mit welchen Parametern ich neue Datensätze einfüge. Jedoch bekommt die Selectabfrage alte Daten zurück da sie bereits aktiv wird bevor der neue Datensatz eingefügt ist.

Darstellung:

SELECT 1

INSERT

SELECT 2

SELECT 1 und 2 haben die gleichen Daten weil teilweise das Insert noch nicht abgearbeitet ist. Deswegen mein Ansatz das mit einer Transaktion bzw einer Sperre auf die Zeilen zu lösen. Wenn du einen anderen Ansatz hast..nur her damit.

[dr.dimitri]

Das sind keine seltsamen Anforderungen sondern bei einem grösserem System mit vielen simultanen Anfragen leider notwendig ist.

Hmm ich entwickle jetzt seit vielen Jahren Java und PL/SQL Anwendungen unter Oracle aber sowas ist mir noch nie untergekommen. Sei's drum.

Es geht darum das ich in abhängigkeit davon wieviele Datensätze bereits in der Datenbank sind weitere Aktionen durchführe

Das wirst Du nie schaffen, denn wenn es sich um ein größeres System handelt wie Du sagst, dann hast Du nie einen eingefrorenen Zustand haben (ausser Du sperrst Die Tabelle aber die user sollen ja arbeiten und nicht warten).

Darstellung:

SELECT 1

INSERT

SELECT 2

SELECT 1 und 2 haben die gleichen Daten weil teilweise das Insert noch nicht abgearbeitet ist.

Nein das ist nicht der Grund. Das ist der Isolationslevel der dafür sorgt, dass Deine Query einen konsistenten Datenzustand hat. Gehen wir das mal durch:

• Select 1 beginnt zum Zeitpunkt T1.
  
• Zum Zeitpunkt T2 wird ein insert gemacht.
  
• Select 2 beginnt bei T3
  
• Der Insert wird commited
  

Beide SQLs liefern einen konsistenten Datenzusatnd wie er zum Zeitpunkt T1 bzw. T3 in der Datenbank war und so arbeitet eine DB nun mal.

Daher ist deine Anforderung sehr seltsam. Eine Anwendung die so arbeitet ist

• Langsam
  
• Nicht skalierbar
  
• Schwer wartbar
  

Wenn Du anhand von bestimmten Insertdaten Aktionen machen möchtest, dann gibt es zwei Möglichkeiten:

• Trigger
  
• In der Anwendung selbst
  

Wenn Du anhand der vorhandenen Zeilen in einer Tabelle Aktionen machen möchtest, wirst in einem OLTP System direkt gegen die Wand fahren.

Vielleicht beschreibst Du einfach mal etwas genauer was Du machst/machen möchtest dann findet sich vielleicht eine bessere Lösung.

Dim