For update

09.01.2012, 17:31

Ich würde gerne mal ein Thema ansprechen, das ich bis jetzt immer ignoriert habe.

Ich habe folgende MySQL-Tabellen:

- user

- gallery
Hierin sind alle grundsätzlichen Galerie-Infos gespeichert.
Titel der Galerie, ob Galerie mit Passwort geschützt, ...

- image:
Hierin sind alle Infos zu den Galerie-Bildern gespeichert.
Die Spalte no (Typ unsigned int) dient dazu, die Bilder sortieren zu können.

Aus der Tabelle image soll nun ein Datensatz gelöscht werden.

Die SQL-Befehle sehen wie folgt aus:

Code:

START TRANSACTION

Der Select
- um zu kontrollieren, ob es Bild und zugehörige Galerie und User überhaupt gibt und
- um mittels FOR UPDATE Datensätze der Tabelle image zu sperren:

Code:

SELECT
      user.id AS userId,
      user.name AS userName,
      gallery.id AS galleryId,
      gallery.title AS galleryTitle,
      image.id,
      image.no
   FROM
      user
      INNER JOIN gallery ON (user.id = gallery.user_id)
      LEFT JOIN image ON (gallery.id = image.gallery_id)
      INNER JOIN (
         SELECT gallery_id
            FROM image
            WHERE id = $iImageId
      ) AS i ON (image.gallery_id = i.gallery_id)
   FOR UPDATE

Der eigentliche Löschbefehl:

Code:

DELETE FROM image WHERE id = $iImageId

Sauber durchnummerieren (zum Sortieren der Bilder erforderlich):

Code:

UPDATE image SET
      no = (@no := @no + 1)
   WHERE
      gallery_id = $iGalleryId AND
      (SELECT @no := -1)
   ORDER BY no, id

Code:

COMMIT

Ich hätte nun eine Frage zu dem FOR UPDATE. Bei diesem SELECT joine ich 3 Tabellen. Aufgrund des FOR UPDATEs gehe ich davon aus, dass in allen 3 Tabellen Datensätze gesperrt werden. Doch eigentlich möchte ich das überhaupt nicht. Eigentlich sollen ausschließlich nur die Datensätze der Tabelle image gesperrt werden. Ist das in MySQL überhaupt möglich?

tr0y · 10.01.2012, 01:31

Dann SELECTe nur das image table als FOR UPDATE und hole dir vor dem SELECT in der transaction die Daten die du brauchst um entsprechend zu SELECTen. Wie man mit Variablen in MySQL umgeht weißt du ja augenscheinlich.

10.01.2012, 09:23

Wie würde denn so eine Stored Procedure aussehen?

11.01.2012, 11:00

Ich habe heute herumexperimentiert, wie sich FOR UPDATE und LOCK IN SHARE MODE innerhalb eines SQL-Statements bei 2er gejointer Tabellen kombinieren lassen. Dabei musste ich Seltsames feststellen.

Nur mit der folgenden Variante erzielte ich das erwartete Ergebnis:

Code:

SET AUTOCOMMIT = 0

Code:

SELECT *
   FROM
      a
      INNER JOIN (
         SELECT *
            FROM b
            WHERE y = 100
         LOCK IN SHARE MODE
      ) temp ON (temp.a_id = a.id)
FOR UPDATE

Führt man dann in einem anderen Prozess einen SELECT auf die Tabelle b aus solange dieser Prozess noch läuft, wird der Datensatz mit y = 100 nicht gesperrt, ein SELECT auf den gematchten Datensatz von Tabelle a jedoch schon. Also alles OK.

Ich habe dann die beiden Modi innerhalb dieses SQL-Statements ausgetauscht. Das funktionierte dann jedoch nicht mehr wie erwartet. SELECTs anderer Prozesse wurden auf jeden Fall immer sofort ausgeführt. FOR UPDATE hatte dann keinen Effekt mehr. Er wurde durch LOCK IN SHARE MODE ersetzt.

Auch habe ich versucht, die Tabelle a als SubSelect einzubinden und darin einen Locking-Modus zu definieren. D. h. ich hatte 2 SubSelects mit unterschiedlichen Locking-Modi. FOR UPDATE wurde jedoch immer von LOCK IN SHARE MODE übertrumpft.

Das ist alles irgendwie ein seltsames Verhalten. Ich komme nicht weiter.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

09.01.2012, 17:31
php.de Gast Beiträge: n/a	For update Ich würde gerne mal ein Thema ansprechen, das ich bis jetzt immer ignoriert habe. Ich habe folgende MySQL-Tabellen: - user - gallery Hierin sind alle grundsätzlichen Galerie-Infos gespeichert. Titel der Galerie, ob Galerie mit Passwort geschützt, ... - image: Hierin sind alle Infos zu den Galerie-Bildern gespeichert. Die Spalte no (Typ unsigned int) dient dazu, die Bilder sortieren zu können. Aus der Tabelle image soll nun ein Datensatz gelöscht werden. Die SQL-Befehle sehen wie folgt aus: Code: START TRANSACTION Der Select - um zu kontrollieren, ob es Bild und zugehörige Galerie und User überhaupt gibt und - um mittels FOR UPDATE Datensätze der Tabelle image zu sperren: Code: SELECT user.id AS userId, user.name AS userName, gallery.id AS galleryId, gallery.title AS galleryTitle, image.id, image.no FROM user INNER JOIN gallery ON (user.id = gallery.user_id) LEFT JOIN image ON (gallery.id = image.gallery_id) INNER JOIN ( SELECT gallery_id FROM image WHERE id = $iImageId ) AS i ON (image.gallery_id = i.gallery_id) FOR UPDATE Der eigentliche Löschbefehl: Code: DELETE FROM image WHERE id = $iImageId Sauber durchnummerieren (zum Sortieren der Bilder erforderlich): Code: UPDATE image SET no = (@no := @no + 1) WHERE gallery_id = $iGalleryId AND (SELECT @no := -1) ORDER BY no, id Code: COMMIT Ich hätte nun eine Frage zu dem FOR UPDATE. Bei diesem SELECT joine ich 3 Tabellen. Aufgrund des FOR UPDATEs gehe ich davon aus, dass in allen 3 Tabellen Datensätze gesperrt werden. Doch eigentlich möchte ich das überhaupt nicht. Eigentlich sollen ausschließlich nur die Datensätze der Tabelle image gesperrt werden. Ist das in MySQL überhaupt möglich? Geändert von php.de (09.01.2012 um 19:08 Uhr).


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

10.01.2012, 01:31
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 4.856 PHP-Kenntnisse: Fortgeschritten	Dann SELECTe nur das image table als FOR UPDATE und hole dir vor dem SELECT in der transaction die Daten die du brauchst um entsprechend zu SELECTen. Wie man mit Variablen in MySQL umgeht weißt du ja augenscheinlich. __________________ Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.

10.01.2012, 09:23
php.de Gast Beiträge: n/a	Wie würde denn so eine Stored Procedure aussehen?

11.01.2012, 11:00
php.de Gast Beiträge: n/a	Ich habe heute herumexperimentiert, wie sich FOR UPDATE und LOCK IN SHARE MODE innerhalb eines SQL-Statements bei 2er gejointer Tabellen kombinieren lassen. Dabei musste ich Seltsames feststellen. Nur mit der folgenden Variante erzielte ich das erwartete Ergebnis: Code: SET AUTOCOMMIT = 0 Code: SELECT * FROM a INNER JOIN ( SELECT * FROM b WHERE y = 100 LOCK IN SHARE MODE ) temp ON (temp.a_id = a.id) FOR UPDATE Führt man dann in einem anderen Prozess einen SELECT auf die Tabelle b aus solange dieser Prozess noch läuft, wird der Datensatz mit y = 100 nicht gesperrt, ein SELECT auf den gematchten Datensatz von Tabelle a jedoch schon. Also alles OK. Ich habe dann die beiden Modi innerhalb dieses SQL-Statements ausgetauscht. Das funktionierte dann jedoch nicht mehr wie erwartet. SELECTs anderer Prozesse wurden auf jeden Fall immer sofort ausgeführt. FOR UPDATE hatte dann keinen Effekt mehr. Er wurde durch LOCK IN SHARE MODE ersetzt. Auch habe ich versucht, die Tabelle a als SubSelect einzubinden und darin einen Locking-Modus zu definieren. D. h. ich hatte 2 SubSelects mit unterschiedlichen Locking-Modi. FOR UPDATE wurde jedoch immer von LOCK IN SHARE MODE übertrumpft. Das ist alles irgendwie ein seltsames Verhalten. Ich komme nicht weiter. Geändert von php.de (11.01.2012 um 11:07 Uhr).