SQL MySQL eindeutigen Datensatz auswählen

[bandit600]

Hallo zusammen,

ich benötige eine effiziente und "sicher" Abfrage einer MySQL-Datenbank-Tabelle aus einem PHP-Script.

Folgende Anforderungen meinerseites werden gestellt:

1) ich möchte einen Datensatz aus einer Tabelle mit fortlaufenden Zahlen auswählen und sicherstellen, dass dieser nicht noch einmal ausgewählt wird
2) der nächste Nutzer kommt nach einer sehr kurzen Zeit um ebenfalls wieder einen eindeutigen unbenutzten Datensatz auszuwählen
3) die Tabelle hat mehr als 500.000 Einträge

Folgende Ideen hatte ich bereits

a) 1. SELECT id, zahl, ... FROM tabelle WHERE status='0' LIMIT 1;
2. UPDATE tabelle SET status='1' WHERE id='$id';
b) 1. UPDATE tabelle t1, (SELECT id AS wert FROM tabelle t2 WHERE status='0') SET t1.status = '1', ident='irgendeinwert' WHERE t1.id = t2.wert;
2. SELECT * FROM tabelle WHERE ident='irgendeinwert'

beides habe ich bereits ausprobiert mit folgendem Ergebnis:
1) Variante a) ist schnell, aber garantiert mir leider nicht, dass jeder Datensatz nur einmal ausgewählt wird
2) Variante b) führt zu einer Überlastung meines Servers bei mehreren Nutzern aber garantiert mir, dass ich jeden Datensatz nur einmal nutze and update

Darum kommt jetzt die eigentliche Frage:

Gibt es eine bessere Möglichkeit, mit der ich mein Anliegen schnell und sicher verwirklichen kann???

 

[Yuuri]

Ich versteh zwar kaum was du willst, aber versuchs mal mit SELECT DISTINCT ....

 

[fry2k]

wie wärs mit lock tables ?

http://dev.mysql.com/doc/refman/5.1/de/lock-tables.html

 

[icecubeblue88]

Wie wäres es mit einer zweiten Tabelle?
Datensatz aus Tabelle A auslesen, Datensatz aus Tabelle A löschen, Datensatz in Tabelle B schreiben

 

[bandit600]

Hallo zusammen

@Yuuri:
SELECT DESTINCT stellt nur sicher, dass jeder verschiedene Datensatz aus 1 Spalte lediglich nur einmal angezeigt wird.

ich möchte allerdings sicherstellen, dass ein Datensatz zur weiteren Nutzung wie "geblockt" wird und nicht nocheinmal genutzt werden kann. Daher habe ich mir schon überlegt, dass man es vllt über eine status-Spalte machen könnte.

@fry2k
lock tables geht nicht weil ich damit alle anderen Nutzer blockiere und das mir das zu langsam ab

@icecubeblue88
wie mache ich das in MySQL (PseudoCode reicht mir) und hast du das zufällig schon mal probiert ? Dafür brauche ich doch mindestens auch 2 statements oder mehr ?!?!?

Gruß bandit600

 

[Daaron]

Du wirst um Locks nicht drumrum kommen. Du hast mindestens 2 atomare Vorgänge:
- SELECT einzigartigen Eintrag
und wahlweise:
- UPDATE setze Eintrag Status auf "used" oder
- DELETE Eintrag (und schreibe seine Werte in eienr 3. Aktion in eine 2. Tabelle)

Wenn 2 User parallel auf deinem Server sind kann der 2. gerade beim Anfang vom SELECT ankommen, bevor der 1. mit seinem UPDATE/DELETE durch ist. Typische Race Condition, die einen Mutex erfordert.... in dem Falle halt eine Sperre auf die Tabellen.
Die reine Performance-Probleme von Locks kannst du umgehen, indem du einfach etwas mehr Hardware auf das Problem wirfst.

Ach ja, schalt bloß den Query Cache bei solchen Aktionen ab.... da suchste dich tot nach der Fehlerquelle.

 

[thes33k]

Ehrlich gesagt, glaube ich nicht, dass das was du möchtest sich performant umsetzen lässt. Egal wie du es drehst, du kommst um einen lockenden Vorgang nicht herum und dementsprechend kann das nicht performant werden.

Vielleicht würde es uns etwas helfen, wieso du ausgerechnet über MySQL selber das erreichen möchtest? Manchmal klärt sich dann die Thematik schneller.

Alternative schnellere Lösungen wie z.B. eine Zwischentabelle mit memcached oder redis oder ähnliches zu erhalten, verschieben das Problem nur.

 

[bandit600]

@ Daaron
Das Problem ist, dass ich es nicht mit mehr Hardware erschlagen kann, da die Hardware begrenzt ist und die Hardware (4*2,5GHz und 10 GB Ram) m.E. ausreichen sollte.

Ich werde es mal mit einem Lock versuchen, mache ich das bevor oder nachdem ich das SELECT mache ???

@all
Ich würde auch andere Techniken nutzen, die mich effizient zum Ziel bringen. Vllt wäre es besser noch einmal den Gesamtkontext zu erklären:

Ein Nutzer A soll eine Nummer aus einer Liste bekommen. Damit soll er ... sagen wir mal ... einen Auftrag mit eindeutiger Auftragsnummer verschicken... und kein anderer Nutzer soll nocheinmal etwas mit der gleichen Nummer machen können.

Nach 5 Minuten kommt ein anderer Nutzer und möchte diesen Auftrag stornieren. Diese Stornierung soll auch wieder nur 1mal von einem Nutzer möglich sein.

Jetzt kommt noch die Problematik hinzu, dass es von vielen Nutzern auf einmal gemacht werden soll.

----------------------

Früher habe ich es schon einmal mit CSV-Listen versucht aber da war das Problem, dass wenn zuviele Nutzer B da waren wurde mehr storniert als angelegt. Dann habe ich auf PHP mit mySQL umgestellt weil damit kontrolliert werden konnte, dass nur Stornierungen für bereits vergebene Auftragsnummern vorgenommen werden konnten.

Also ich wäre auch für andere einfach Möglichkeiten offen, Vorschläge sind gern willkommen

Gruß

Ergänzung (18. Februar 2012)

Kann man vllt in einem Statement ein Update und ein Select machen und anschließend noch wissen, welchen Datensatz man geändert hat ... und das vllt noch ohne ein zusätzliches SELECT ????

 

[Daaron]

@ Daaron
Das Problem ist, dass ich es nicht mit mehr Hardware erschlagen kann, da die Hardware begrenzt ist und die Hardware (4*2,5GHz und 10 GB Ram) m.E. ausreichen sollte.

Ich werde es mal mit einem Lock versuchen, mache ich das bevor oder nachdem ich das SELECT mache ???

Wie viele Millionen User erwartest du, dass SO eine Kiste damit nicht klar kommt?
Die Reihenfolge ist: Lock -> Select -> Delete/Update -> Unlock. Dadurch wird aus den 2 Einzeloperationen, in die sich eine dazwischenmogeln kann, eine einzelne lange Operation.

Ein Nutzer A soll eine Nummer aus einer Liste bekommen. Damit soll er ... sagen wir mal ... einen Auftrag mit eindeutiger Auftragsnummer verschicken... und kein anderer Nutzer soll nocheinmal etwas mit der gleichen Nummer machen können.

Nach 5 Minuten kommt ein anderer Nutzer und möchte diesen Auftrag stornieren. Diese Stornierung soll auch wieder nur 1mal von einem Nutzer möglich sein.

Jetzt kommt noch die Problematik hinzu, dass es von vielen Nutzern auf einmal gemacht werden soll.

Warum sollte ein User die Aufträge eines ANDEREN Users stornieren dürfen (außer er ist Admin)? Das macht irgend wie keinen Sinn... Aber ok. Nix was man über eine gute User-Table mit ner weiteren Table für "User, denen ich Rechte erteilt habe, meine Aufträge zu stornieren" nicht lösen könnte.

Außerdem ist die Stornierung doch nur einmal möglich.

UPDATE TABLE SET status='storniert' WHERE status='laufend' AND id='?'

Ergänzung (18. Februar 2012)

Kann man vllt in einem Statement ein Update und ein Select machen und anschließend noch wissen, welchen Datensatz man geändert hat ... und das vllt noch ohne ein zusätzliches SELECT ????

Hab ich mich auch schon gefragt, aber noch nie von sowas gehört...

 

[bandit600]

Wie viele Millionen User erwartest du, dass SO eine Kiste damit nicht klar kommt?
Die Reihenfolge ist: Lock -> Select -> Delete/Update -> Unlock. Dadurch wird aus den 2 Einzeloperationen, in die sich eine dazwischenmogeln kann, eine einzelne lange Operation.

Es sollen auf diese Weise bis zu 5 Autragsnummern pro Sekunde aus der DB geholt werden, vllt später auch noch mehr. Ich werde das mit dem Lock heute mal versuchen und dann mitteilen ob es so schnell geht wie ich das möchte.

 

[Daaron]

All zu lange werden die Querys für Select und Update doch nciht laufen, wenn deine Indizes sauber liegen. Jeder einzelne Lock-Block sollte deutlich unter 100ms bleiben, denk ich mal.

Im Zweifel einfach mit microtime() testen, wie lange ien Abfrageblock läuft.

 

[Hancock]

http://forums.mysql.com/read.php?21,155023,155156#msg-155156
Das wär doch ne Idee, da Updates ja atomar in Bezug auf die Zeile sein müsste...

 

[ice-breaker]

Also man muss erstmal unterscheiden, die Idee mit dem Locking ist ein pessimistisches Vefahren, du kannst aber genauso gut ein optimistisches Verfahren verwenden:

$tries = 0;
do {
  $entry = fetch('SELECT ..... FROM ... WHERE ....');
  $updated =   update('UPDATE .... SET gesperrt = 1 WHERE gesperrt = 0 AND id = ....'); // boolean (affected_rows == 1)
} while(++$tries <= 3 && !$updated);
var_dump($updated);
var_dump($entry);

So kannst du es auch mit einem optimistischen Verfahren lösen, da eine DB-Änderung atomar ist, funktioniert das ganze sehr gut.
Welches Verfahren für dich besser ist, kann man pauschal nicht beantworten.
Das Problem eines Tabellen-Locks ist natürlich, dass du alle gleichzeitigen Abfragen blockierst. Bei dem optimistischen Verfahren kommen sich nur Prozesse ins Gehege, die gleichzeitig den gleichen Datensatz für sich beanspruchen wollen.

 

[bandit600]

Hallo zusammen,

Danke am Blitzmerker für den Link, ich habe es mal in meine Anwendung angepasst und es scheint mir sicher und schnell wenn ich es mit 1 Nutzer mache... ich werde dennoch dann probieren ob es auch mit vielen Nutzern schnell genug geht oder meinen Server umhaut

UPDATE tabelle SET auftragsnr=(@_tempauftragsNR:=auftragsnr), status = '1' WHERE  status = '0' LIMIT 1;

SELECT @_tempauftragsNR;

Damit kann man ein Update in der Tabelle machen und anschließend weiß man ohne ein zusätzliches SELECT in der Tabelle noch seine wichtigen Werte.

Nun muss ich nur noch probieren ob das performant ist.

Gruß

 

[ice-breaker]

das sollte so ziemlich die gleiche Performance wie ein normales Update haben
Genauer gesagt ein SELECT+Update

 

[bandit600]

das sollte so ziemlich die gleiche Performance wie ein normales Update haben
Genauer gesagt ein SELECT+Update

ja das ist richtig, aber es funktioniert für meinen Zweck wesentlich zuverlässiger als wenn ich erst ein SELECT und dann ein UPDATE mache.

Ziel erreicht: es wird nur noch von einem Nutzer eine Nummer einmal verwendet und mein Server lahmt auch nicht mehr wenn es viele Nutzer auf gleichzeitig machen.


Danke allen Teilnehmern dieses Threads