Archive for SQL

NoSQL – Die dunkle Seite

Nachdem es so langsam den ersten dämmert das es, wie immer im Leben, halt nicht nur schwarz oder weiß gibt:

The dark side of NoSQL

finden sich die auch mehr und mehr Lösungen die von beiden sich etwas Abschneiden – mixen würde noch treffender passen, so gefunden bei Using multiple database models in a single application.

Dabei wird auch gleich ein neues schönes Buzzword eingeführt, polyglot persistence – an sich nichts wirklich neues.

Sondern frei nach dem Motto für jedes Problem das richtige Werkzeug. Wenn man das noch ein wenig dehnt kommt man schnell auf den Trichter  das sich ein Problem vielleicht mit zwei Werkzeugen bearbeiten lässt, oder besser gesagt sich das Problem in mehrere Teilprobleme zerlegen lässt und somit auch mehrere Werkzeuge passen…

Auf unsere Datenbankwelt bezogen, warum nicht für eine Anwendung mit beiden Welten arbeiten dem klassischen RDMS und einem Key Value Store um wieder den Kreis zu schließen?

Übrigens ganz neu ist das alles nicht, die Betrachtung über Grenzen hinweg gibts auch bei der “Polyglot Programmierung

In diesem Sinne – es bleibt spannend….

Using multiple database models in a single application

Kommentare

Eine Queue leerräumen mit SELECT FOR UPDATE

Man braucht es nicht oft aber ab und an ist ein blockierender SELECT ein sogenannter SELECT FOR UPDATE notwendig, z.B. bei dem Klassiker:
Gegeben ist eine Datenbankqueue von der sich viele Jobs bedienen, nun soll sichergestellt werden das Daten nicht mehrfach aus dieser gelesen und z.B. gelöscht werden, wie geht man vor.
Im Gegensatz zu Oracle kennt MySQL kein RETURNING sonst wäre ein DELETE mit einem rowcount und einem RETURNING das Mittel der Wahl, irgendwas so in der Art:

DELETE FROM TABLE WHERE rownum < 100 RETURNING id;

In MySQL müssen wir das in eine Transaktion verpacken und erst die Daten selektieren und dann können wir sie löschen (und genau dafür brauche ich nun eine gefüllte Testdatenbank aus meinem letzten Posting):

BEGIN;
SELECT id,f1,f2 FROM strintab ORDER BY ts ASC LIMIT 100;
#mach was mit den Results
DELETE FROM stringtab WHERE id in (..vermutlich alle abgeholten....);
COMMIT;

Damit hole ich exklusiv die ersten 100 Zeilen aus der DB und keiner kann auf die Daten zugreifen.
Kurz ausprobiert und tatsächlich niemand kann zugreifen weder auf die 100 records noch auf die anderen rows…

Wieso?
MySQL sperrt alle Zeilen über die sei zum zusammenstellen des Ergebnisses “drüber streicht”.
Will heißen so lange MySQL einen full table scan machen muss, sind alle records gesperrt sobald ein Index im Spiel ist und nur ein index range scan notwendig wird ist dieser Bereich gesperrt (mehr zu Explain).
Und auch ein LIMIT wird berücksichtigt also tatsächlich nur 100 Zeilen gesperrt wenn ich das Ergebnis mit einem LIMIT einschränke.

Also dann, legen wir einen Index an:

ALTER TABLE `test`.`stringtab` 
 DROP INDEX `Index_timestamp`,
 ADD INDEX `Idx_stringtab_ts` USING BTREE(`ts`);

Und führen auf je einer Connection obiges Statement aus und wundern uns zuerst wieso eins davon wieder in einem Lock wait hängt (auch ein LIMIT 10,10 hilft nix)
Wenn man weiß das MySQL LIMIT immer bei 0 anfängt wird es klarer, die gesperrte Range startet immer bei 0 das Offset ist nur für die Ausgabe relevant.
Abhilfe schafft eine Änderung des Sortierkriteriums z.B.: DESC oder eine andere WHERE clause.
Alternativ und eher die empfehlenswerte Variante, im aufrufenden Programm sich die zu bearbeitenden resultsets merken und dann sofort auf der DB löschen und die Transaktion mit COMMIT freigeben und dabei halt das Risiko der Persistenz im Programm tragen.

Fassen wir zusammen:

  • ohne Index immer ein lock auf die ganze Tabelle
  • mit Index immer ein lock auf alle rows die durch die WHERE/ORDER BY clause eingeschränkt werden
  • LIMIT wird berücksichtigt und sorgt für weniger row locks
  • EXPLAIN ist wichtig

Comments (7) Trackback / Pingback (1)

Test Tabellen erstellen per PROCEDURE

Ronald Bradfrods Blogeintrag über MVCC Merkwürdigkeiten in MySQL hat mich inspiriert seine Variante mal anzutesten wie er sich Tabellen mit Inhalt anlegt.
Und seine Funktion dann so anzupassen das eine Tabelle auch mit String Feldern und einem timestamp gefüllt werden kann.

Tabelle anlegen:

create table stringtab
( id int unsigned not null primary key auto_increment,
  f1 varchar(512) not null,
  f2 varchar(512) not null,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) engine=innodb;

Dann die passende Funktion:

DELIMITER $$
DROP PROCEDURE IF EXISTS `fill_strings` $$
CREATE PROCEDURE `fill_strings`(in p_max int)
    DETERMINISTIC
begin
  declare counter int default 1;
  truncate table stringtab;
  insert into stringtab values (1,1,1,now());
  while counter < p_max
  do
      insert into stringtab (f1, f2, ts)
          select  right (concat(counter,f1,"blah"),511),
                    right (concat(f1,"blub",f2),511), 
                    from_unixtime(RAND()*1251885200)
          from stringtab;
      select count(*) from stringtab into counter;
  end while;
  select counter;
end $$
DELIMITER ;

aufgerufen wird das ganze dann so:

call fill_strings(130);

Die Funktion gibt dann die tatsächlich eingefügten Zeilen als Rückgabewert aus.

Und für was man das ganze dann alles verwenden kann darauf gehe ich dann im nächsten Artikel ein ;)

Comments (1)

Update mit JOIN – Syntaxstolperei

Heute kurz festgehalten worüber SQL Neulinge auch des öfteren mal stolpern:

Ich will in einer Tabelle Felder mit Werten aus einer anderen Tabelle füllen wenn die ID aus der Quelltabelle identisch ist mit der Ziel Tabelle.

Dann wird erst einmal losgewerkelt und sich gewundet warum ein

UPDATE ziel SET ziel.wert = quell.wert FROM quelle WHERE ziel.id = quell.id;

nicht funktioniert.
Ein UPDATE kennt halt kein FROM und so wird das dann auch was:

UPDATE ziel,quelle SET ziel.wert = quell.wert WHERE ziel.id = quell.id;

Selbstverständlich geht das auch mit allen LEFT/RIGHT und was auch immer für JOINs

Kommentare

Autoincrement Verhalten in MySQL

Immer wieder mal stolpere ich über die Frage wie verhält sich das MySQL Autoincrement bei bereits belegten PK IDs.

CREATE TABLE `testab`
(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO `testab` (`name`) VALUES (‘blub’);

INSERT INTO `testab` (`id`,`name`) VALUES  (9,’9bsdwldub’);

INSERT INTO `testab` (,`name`) VALUES (‘blub’); (’2′blub’);

Der letzte insert hat den PK 10 d.h. MySQL (wie auch Oracle) füllt mitnichten Lücken auf!

Ebenso muß mann sich bei MySQL nicht darum scheren ob eine ID schon belegt ist – diese wird dann beim insert übersprungen, das allerdings geht mit Sequenzen wie sie Oracle verwendet natürlich nicht.

Eine Sorge weniger…

Comments (1)