In einem MySQL Query select und update/insert

In einem Projekt stand ich vor dem Problem, dass über eine API verschiedene Clients auf die gleichen Tabellen zugreifen. Damit die Tabellen sauber bleiben, darf allerdings immer nur ein Client gleichzeitig schreibende Operationen ausführen. 

Auch muss es weiterhin möglich sein, dass interne Scripte die Tabellen lesen und schreiben dürfen. LOCK TABLES kommt dadurch also nicht in Frage.

Die einfachste Lösung war eine Tabelle mit zwei Spalten. Einem Identifier und dem Status. Der Identifier muss dabei natürlich einzigartig (UNIQUE) sein. Außerdem darf in der Tabelle nur ein Datensatz gleichzeitig den Status 1 (gesperrt) haben.

Folgendes Query führt dann alle nötigen Prüfungen und Aktionen aus:

INSERT INTO LockTable
   (name, status, createdBy, creationTime) 
SELECT 
   '$name', '$lock_status', '11', NOW() 
FROM 
   LockTable 
WHERE 
   '1' NOT IN (
      SELECT  
         status  
      FROM  
         LockTable  
      WHERE  
         status = '1'
   ) OR 
   '$lock_status'='0'
ON DUPLICATE KEY UPDATE 
   status = '$lock_status', 
   lastModifiedBy='1', 
   lastModifiedTime=NOW()

Das Query führt drei Aktionen auf einen Rutsch aus:

• Ist für $name kein Datensatz vorhanden, wird dieser erstellt.
• Ein Datensatz wird nur gesperrt, wenn es keinen anderen gibt, der gesperrt (auf 1 gesetzt) ist.
• Existiert bereit ein Datensatz für $name, wird dieser aktualisiert.

Würde man diese Aktionen nun in einzelne Querys aufteilen, bestünde die Gefahr, dass sich zwischen SELECT und INSERT/UPDATE eine weitere Verbindung zwischen schaltet und sich dort als gesperrt markiert. Im schlimmsten Fall wären dann zwei Verbindungen gleichzeitig gesperrt.

In der Anwendung kann man dann über den Affected Rows Zähler (der muss >0 sein) sehen, ob die sperre erfolgreich gesetzt wurde.

Wichtig: In der Tabelle muss sich IMMER mindestens ein Datensatz befinden. Ansonsten funktioniert das erste Select, welches die Daten für den Insert zusammenbaut nicht!