SQL: alleen te inserten als row niet bestaat

[FooBarWidget]

Stel ik heb de volgende tabel:

code:

CREATE TABLE soort (
  naam    VARCHAR(100) NOT NULL,
  aantal  INT UNSIGNED NOT NULL,
  PRIMARY KEY (naam)
);

Deze tabel is onderdeel van een programma die het aantal dieren telt, en bijhoudt hoeveel van elk soort is geteld. De code die ik op dit moment schrijf ziet er ongeveer zo uit, in pseudocode:

code:

function tel($naam) {
    sql("LOCK TABLES soort READ");
    sql("LOCK TABLES soort WRITE");
    sql("SELECT * FROM soort WHERE naam = $naam");
    if (SELECT retourneert een rij) {
        // Er is al een soort met deze naam.
        sql("UPDATE soort SET aantal = aantal + 1 WHERE naam = $naam");
    } else {
        // Deze soort bestaat niet in de database, dus voeg het toe.
        sql("INSERT INTO soort VALUES($naam, 1)");
    }
    sql("UNLOCK TABLES");
}

Let op het feit dat ik table locks gebruik. Ik doe dat om concurrency problemen te voorkomen: want als meerdere computers dieren gaan tellen, maar gebruik maken van dezelfde database, en je lockt niet, dan kan het voorkomen dat een rij eigenlijk dubbel ingevoegd wordt. Ik wil dus alleen maar een rij invoegen als dat nodig is.

Deze code werkt prima, maar table locks zijn vrij duur. Is er een manier om van die table locks af te komen? Ik heb al geprobeerd om transactie te gebruiken met SERIALIZABLE als transactie niveau, maar MySQL 5 zegt dan dat ie een deadlock detecteert.

[ Voor 3% gewijzigd door FooBarWidget op 10-12-2006 16:28 ]

[jochemd]

FooBarWidget schreef op zaterdag 09 december 2006 @ 19:18:
Stel ik heb de volgende tabel:

code:

CREATE TABLE soort (
  naam    VARCHAR(100) NOT NULL,
  aantal  INT UNSIGNED NOT NULL,
  PRIMARY KEY (naam)
);

(..)
Let op het feit dat ik table locks gebruik. Ik doe dat om concurrency problemen te voorkomen: want als meerdere computers dieren gaan tellen, maar gebruik maken van dezelfde database, en je lockt niet, dan kan het voorkomen dat een rij eigenlijk dubbel ingevoegd wordt.

Dat kan helemaal niet want je hebt een primary key over naam liggen dus die is per definitie uniek.

[Verwijderd]

nvm

[ Voor 90% gewijzigd door Verwijderd op 09-12-2006 19:36 ]

[FooBarWidget]

Dat kan helemaal niet want je hebt een primary key over naam liggen dus die is per definitie uniek.

Dat is het punt niet, het gaat me alleen om of er een efficientere manier is dan tabel locks.

Maar om toch jouw punt in te gaan: stel dat ik toch die INSERT doe met een naam die al bestaat. Dan geeft de database een error. Maar ik wil helemaal niet een error terugkrijgen, ik wil juist die error voorkomen, anders gaat er een telling verloren! Ik kan ook niet die error netjes afvangen en nog eens proberen, want de database retourneert een error message string, en niet een nettje machine-leesbare object die me precies vertelt dat het mislukt is omdat de waarde voor kolom zus en zo al bestaat. Ik kan natuurlijk die error message string gaan parsen, maar wie vertelt me nou dat die string niet plotseling verandert in een nieuwe database versie, of als de taalintelling anders is?

Maar ik wil gewoon een efficientere alternatief voor table locks in zo'n situatie.

gewoon een update query doen lijkt me?

Dat kan ik toch niet doen als de row niet al bestaat? Stel nou ik tel een kat. Als dit de eerste keer is dat een kat is geteld, dan moet ik "kat" invoegen in de database. Een update zou geen effect hebben aangezien "kat" nog niet in de database zit!

[ Voor 19% gewijzigd door FooBarWidget op 09-12-2006 19:49 ]

[MIster X]

INSERT ... ON DUPLICATE KEY UPDATE

[robbert]

FooBarWidget schreef op zaterdag 09 december 2006 @ 19:36:
Maar om toch jouw punt in te gaan: stel dat ik toch die INSERT doe met een naam die al bestaat. Dan geeft de database een error. Maar ik wil helemaal niet een error terugkrijgen, ik wil juist die error voorkomen, anders gaat er een telling verloren! Ik kan ook niet die error netjes afvangen en nog eens proberen, want de database retourneert een error message string, en niet een nettje machine-leesbare object die me precies vertelt dat het mislukt is omdat de waarde voor kolom zus en zo al bestaat. Ik kan natuurlijk die error message string gaan parsen, maar wie vertelt me nou dat die string niet plotseling verandert in een nieuwe database versie, of als de taalintelling anders is?

Ik weet niet wat je voor een taal gebruikt, maar in php heb je mysql_errno (in andere talen heb je vast wel een variant). Daarmee is vast wel iets te achterhalen, toch?
Daarnaast denk ik niet dat MySQL die error messages zomaar gaat veranderen in een nieuwe versie.

[ Voor 6% gewijzigd door robbert op 09-12-2006 20:06 ]

[FooBarWidget]

Ik weet niet wat je voor een taal gebruikt, maar in php heb je mysql_errno (in andere talen heb je vast wel een variant). Daarmee is vast wel iets te achterhalen, toch?

Ik gebruik AdoDB en voor zover ik weet is het niet mogelijk de error number te verkrijgen uit een ADODB_Exception.

[GX]

MySQL doet dit met REPLACE (ietwat beperkt, je kan inet de update blocken. Maar dat wil je wrs toch niet), volgens de standaard met MERGE INTO.

[ Voor 38% gewijzigd door GX op 09-12-2006 21:03 ]

[jochemd]

FooBarWidget schreef op zaterdag 09 december 2006 @ 19:36:

Dat is het punt niet, het gaat me alleen om of er een efficientere manier is dan tabel locks.

Maar om toch jouw punt in te gaan: stel dat ik toch die INSERT doe met een naam die al bestaat. Dan geeft de database een error. Maar ik wil helemaal niet een error terugkrijgen, ik wil juist die error voorkomen, anders gaat er een telling verloren!

Dan haal je die primairy key er af, doe je altijd een insert en bereken je het aantal aan de hand van een SUM en ruim je eens in de tijd op door rijen te aggregreren.

[robbert]

jochemd schreef op zaterdag 09 december 2006 @ 21:53:
[...]

Dan haal je die primairy key er af, doe je altijd een insert en bereken je het aantal aan de hand van een SUM en ruim je eens in de tijd op door rijen te aggregreren.

Dat klinkt wel erg smerig...

[Soultaker]

Het is toch geen probleem als de INSERT faalt? Waarom kun je niet zoiets doen?

SQL:

INSERT INTO Soort (Naam, Aantal) VALUES ("aap", 0);
UPDATE Soort SET Aantal = Aantal + 1 WHERE Naam = "aap";

Veel RDBMS systemen hebben ook een optie om fouten in de eerste query te negeren.

[T-MOB]

De oplossing is toch al aangedragen door Mister X. ON DUPLICATE KEY UPDATE doet precies wat er inde TS gevraagd wordt:

SQL:

INSERT INTO
    `soort` (`naam`,`aantal`)
VALUES 
    ("aap",1)
ON DUPLICATE KEY UPDATE
    `aantal`=`aantal`+1

[FooBarWidget]

Dan haal je die primairy key er af, doe je altijd een insert en bereken je het aantal aan de hand van een SUM en ruim je eens in de tijd op door rijen te aggregreren.

En dan heb je allemaal redundante rijen in je database. Dat wil ik niet. Bovendien is het berekenen van zo'n som best duur vergeleken met alleen even snel een kolomwaarde opvragen.

MySQL doet dit met REPLACE

Volgens de MySQL documentatie gooit hij eerst een rij weg om vervolgens een nieuwe rij in te voegen. Hoe is REPLACE performance ten opzichte van UPDATE?

MySQL schijnt MERGE INTO niet te ondersteunen. Een snelle Google zoekactie levert niets nuttigs op.

De oplossing is toch al aangedragen door Mister X. ON DUPLICATE KEY UPDATE doet precies wat er inde TS gevraagd wordt:

"ON DUPLICATE KEY UPDATE" zal helaas mijn probleem niet oplossen. Die 'soort' tabel is maar een simpele voorbeeld. In een van mijn applicaties heb ik de volgende tabel:

code:

CREATE TABLE stat_products (
    id       MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name     VARCHAR(100) NOT NULL,
    version  VARCHAR(30) NOT NULL,

    PRIMARY KEY (id),
    INDEX USING BTREE (name),
    INDEX USING BTREE (version)
);

In deze tabel moet het paar (name, version) uniek zijn. Het kan best voorkomen dat er twee namen hetzelfde zijn, als hun versie maar verschillen. Maar het mag niet zo zijn dat er twee rijen zijn met dezelfde name *en* version. Voor zover ik weet is het niet mogelijk om een UNIQUE constraint te specificeren voor een combinatie van meerdere kolommen, waardoor ik "ON DUPLICATE KEY UPDATE" niet kan gebruiken.

[Robtimus]

FooBarWidget schreef op zondag 10 december 2006 @ 16:01:
"ON DUPLICATE KEY UPDATE" zal helaas mijn probleem niet oplossen. Die 'soort' tabel is maar een simpele voorbeeld. In een van mijn applicaties heb ik de volgende tabel:

code:

CREATE TABLE stat_products (
    id       MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name     VARCHAR(100) NOT NULL,
    version  VARCHAR(30) NOT NULL,

    PRIMARY KEY (id),
    INDEX USING BTREE (name),
    INDEX USING BTREE (version)
);

In deze tabel moet het paar (name, version) uniek zijn. Het kan best voorkomen dat er twee namen hetzelfde zijn, als hun versie maar verschillen. Maar het mag niet zo zijn dat er twee rijen zijn met dezelfde name *en* version. Voor zover ik weet is het niet mogelijk om een UNIQUE constraint te specificeren voor een combinatie van meerdere kolommen, waardoor ik "ON DUPLICATE KEY UPDATE" niet kan gebruiken.

Waarom kun je geen unique constraint specificeren? Ik heb op mijn MySQL database dat via PHPMyAdmin zelfs op 3 kolommen gedaan.

[FooBarWidget]

Waarom kun je geen unique constraint specificeren? Ik heb op mijn MySQL database dat via PHPMyAdmin zelfs op 3 kolommen gedaan.

Hoe doe je dat dan? Nogmaals, ik heb het niet over een UNIQUE per kolom, naar een UNIQUE voor een combinatie van een aantal kolommen. Zie uitleg.

Als ik bijvoorbeeld dit doe:

code:

name VARCHAR(100) NOT NULL UNIQUE
version VARCHAR(100) NOT NULL UNIQUE

Dan zijn zulke rijen niet mogelijk:

code:

(name = "Notepad", version = "1.0")
(name = "Notepad", version = "1.2")

Maar dat wil ik juist wel! Ik wil alleen zulke rijen niet mogelijk maken:

code:

(name = "Notepad", version = "1.0")
(name = "Notepad", version = "1.0")

[robbert]

FooBarWidget schreef op zondag 10 december 2006 @ 16:30:
[...]


Hoe doe je dat dan? Nogmaals, ik heb het niet over een UNIQUE per kolom, naar een UNIQUE voor een combinatie van een aantal kolommen. Zie uitleg.

Als ik bijvoorbeeld dit doe:

code:

name VARCHAR(100) NOT NULL UNIQUE
version VARCHAR(100) NOT NULL UNIQUE

code:

name VARCHAR(100) NOT NULL,
version VARCHAR(100) NOT NULL,
UNIQUE KEY blaat (name,version)

Zoiets

[MIster X]

FooBarWidget schreef op zondag 10 december 2006 @ 16:30:
Hoe doe je dat dan? Nogmaals, ik heb het niet over een UNIQUE per kolom, naar een UNIQUE voor een combinatie van een aantal kolommen.

Natuurlijk kun je een key wel over meerdere kolommen leggen! Basisfunctie in databases. Als je nou die link die ik postte had gelezen, dan wordt daar nota bene uitgelegd: "If column b is also unique, ..."

[ Voor 44% gewijzigd door MIster X op 10-12-2006 20:29 ]