welches locking brauche ich?

Eine Frage an LOCK-Experten:

Ich habe

table words
 id (serial)
 status (int, default 0)
 word (varchar)

In diese Tabelle werden von vielen Clients zwischen 5 und 50 Einträge pro Sekunde INSERTET.

Auf der anderen Seite gibt es ein Script, das die Tabelle abarbeitet, vereinfacht gesagt macht das:

select id,word from table where status=0 limit 1;
update table set status=1 where id=$id

Nun möchte ich gerne die Tabelle mit 2 oder mehr Scripts gleichzeitig abarbeiten.
Wie kann ich zuverlässig verhindern, daß die “Abarbeite-Scripts” sich gleichzeitig dieselbe Zeile krallen, ohne die Performance der INSERTS massiv zu bremsen (ich vermute, ein LOCK würde das tun)?

Muss ich für die SELECT/UPDATE-Kombination wirklich ein LOCK machen? - wenn ja, welches?
Kann ich die select/update-kombination ggf. in einen query verpacken? brauche ich dann noch ein lock?

Für jeden Tip dankbar,

thomas

noch immer:
viele clients beackern gleichzeitig eine tabelle. ich möchte verhindern, dass sich zwei clients gleichzeitig einen eintrag krallen.

versucht habe ich das so:

$dbh->do(“BEGIN WORK”)
$dbh->do(“LOCK TABLE hosts2do”)

$query=“SELECT id,hostname FROM hosts2do WHERE status=0 limit 1”;
$sthw=$dbh->prepare($query)
$sthw->execute()
my ($id,$ret)=$sthw->fetchrow;

$dbh->do(“UPDATE hosts2do SET status=1 where id=$id”)
$dbh->do(“COMMIT WORK”)

das geht besser als ohne LOCK, gelegentlich kommts aber immer noch vor, daß zwei clients denselben eintrag kriegen.

ein wenig ratlos,

ts

Hi,

das sollte daher rühren, das kein exclusive lock gesetzt wird und immer noch lesend auf die Tabelle zugegriffen wird. Schau dir mal[1] an, da gehts um verschiedene Lock-Modes.

Du kommst um ein differenziertes Locking nicht drumrum, ausser du sperrst für die ganze Tabelle Schreib- und Leserechte für die Zeit der Tarnsaktion. Das geht aber tierisch auf die Performance. Nur als Tip, ohne das getestet zu haben: Zum Lesen der ID sperrst du die ganze Tabelle mit einem write-lock, wenn du deine ID hast, sperrst du explizit diese Zeile exklusiv (Schreib/Lese-Lock) und gibst den ersten Lock wieder frei. Dann machst du dein Update und schliest die Transaktion ab.

Viel Glück,
Domski

[1]http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html

hmm - die performance ist derzeit auch mein problem: um die sache mit den locks nicht ausufern zu lassen, lese ich inzwischen innerhalb des locks jeweils 50 datensätze ein, die dann upgedatet werden, bevor ein commit stattfindet.

lesend funktioniert das prima und bringt einen ziemlichen performancegewinn.

ab 30 clients ist die table dann aber quasi ständig gelockt - mit dem ergebnis, daß die performance der inserts drastisch sinkt (alle paar sekunden geht mal einer durch, dann ist wieder gelockt).

wie es die vorredner schon prophezeit haben komme ich wohl um ein differenzierteres locking nicht herum.
nichtsdestotrotz: mein eher schmalbrüstiger db-server ist auch bei 50 clients, die (andere) indizierte tabellen mit inserts und updates behämmern noch unter einer la von 1 - die performance finde ich schlicht begeisternd.

ich spiel mal mit dem locking und poste bei gelegenheit die ergebnisse…

ts

Du könntest einen SELECT … FOR UPDATE nutzen, fischt dir mit einer Anfrage deine 50 Einträge heraus und markierst diese gleichzeitig zum Ändern. Dann greift sich kein anderer Prozess diese Einträge bzw. wenn er das will, muss er doch warten.

Damit ersparst du dir, die komplette Tabelle locken zu müssen.

ja, ich hab das genau so gemacht.
leider ist auch das

  1. nicht sonderlich performant, wenn gleichzeitig massiv neue einträge geschrieben werden
  2. lausig performant, wenn es gleichzeitig von mehreren threads gemacht wird. wenn die queries dann noch über eine langsame verbindung kommen, bricht das sehr oft mit einer fehlermeldung ab.

ein weiteres problem scheint zu sein, daß sich die prozesse manchmal ins gehege kommen und einer die einträge des anderen bearbeitet - ich kann aber derzeit nicht zuordnen, ob das nun von der db oder von meiner lausigen perl-umsetzung kommt.

ich bastle und probiere weiter.
nichtsdestotrotz kann ich derzeit mit der lösung arbeiten.

ts

Na ja, du musst auch beachten, das du bei so vielen Schreibzugriffen gleichzeitig, sowohl Inserts wie Updates (wenn ich das richtig verstanden habe) und auch noch Selects irgendwann an die Grenzen deiner Hardware stößt.

Die Daten müssen ja einerseits sauber auf die Platte geschrieben werden, das Log möchte geschrieben werden, Transaktionen müssen sichergestellt sein und Daten möchtest du auch noch zurückbekommen. Dabei kommt die DB zwar nicht durcheinander aber irgendwann kann sie das alles vielleicht nicht mehr abarbeiten.

Ausserdem solltest du beachten, das du durch das Multiversion Controlsystem Daten von offenen Transaktionen nicht sehen kannst. Also alles, was gerade geschrieben wird, ist noch nicht für Selects sichtbar.

Es wäre interessant, hier herauszufinden, wo genau Performance fehlt.

  1. ich weiss, daß die hardware (celeron 600, 521MB, alte ide-hd, alles auch noch anderweitg beschäftigt) unterdimensioniert ist - ein neuer server ist bestellt.

  2. das “performance-loch” würde mich auch interessieren. ein guter teil der performance geht derzeit fürs nachschauen drauf, ob der eintrag schon existiert - ich werds demnächst mal versuchen, den eintrag einfach mal reinzuschieben - die db schmeisst wg. des unique-indexes dann einen fehler - in ersten tests sieht mir das aber schneller aus, als vorher einen select abzusetzen und nur im falle eines neuen eintrags ein update zu fahren.

ts

Die Hardware ist tatsächlich ein wenig schwach aufb der Brust. Grad der Speicherdurchsatz lässt da zu wünschen übrig und der ist grade relevant :wink:

Aber die Idee mit dem Insert auf Verdacht ist vernünftig. Wenn dir die Info reicht, das der Datensatz schon existiert, ist das die schnellste Variante. Den Unique-Index muss die DB auch bei einem erfolgreichen Insert checken und aktualisieren, so gesehen erledigt die Db bei deiner Select & Insert Variante die Arbeit doppelt.

das hauptargument dagegen war ein kosmetisches:

die fehlermeldung verunstaltet mir den output meines perl-scripts kostet bei zigtausend inserts sinnlos bandbreite … :slight_smile:

aber irgendwie müsste ich DBI die Fehlermeldungen austreiben können.

ts

Das hatte das so verstanden, das du das Ergebnis der Select-Abfrage brauchst!

Wenn die Ausgabe stört, mache eine pg/plsql-Funktion, die dein Insert übernimmt. Dadrin fängst du den Fehler ab und gibst nur jeweils einen return-code für erfolgreiches/nicht erfolgreiches Insert an dein Perl-Script. Das sollte die zu übertragende Datenmenge auf ein Minimum reduzieren und vielleicht sogar noch ein wenig DB-Performance herausholen.

hmm. bevor ich mich an ein update des importscripts mache, tun sich einige fragen auf.

ich habe jeweils zwischen 20 (meistens) und 10.000 (selten) zeilen auf einmal zum inserten.

kennt postgresql eine funktion, mit der ich die inserts in einer zeile (wie in mysql) machen kann?

geht das in einer funktion? - ich fürchte, ich kapiere das manual im bezug auf die funktionen nicht so ganz.

macht es sinn, die mass-inserts in eine transaktion einzuschliessen?

gibt es analog zu mysql eine “insert … delayed” funktion in postgresql?

fragen über fragen…

ts

Der Befehl dafür nennt sich COPY, alternativ kannst du so einen Massen Insert auch mit einem Prepared Query machen. Dann muss die Datenbank den kompletten Queryplan nur einmal erstellen und schiebt bei weiteren Durchläufen die Daten bloss noch bis auf die Festplatte.