Keskustelu: Nettisivujen teko: monta sql queryi linkitetty

Tämä nyt ei varsinaisesti kuulu tälle alueelle, mutta ajattelin että se sopisi tänne kuitenkin.

Melko usein tulee sellaisia sql-kyselyitä, että niiden toiminta pitäisi liittää toisiinsa. Otetaan esimerkiksi tälläinen kahden insert-queryn sarja:

INSERT INTO artist (id, name) VALUES (1, "Jean Sibelius");
INSERT INTO titles (id, artist, name) VALUES (123, 1, "Sinfonia");

Miten voisi tehdä sillain, että jos tuo alempi query failaa, niin poistettaisiin myös tuo ensimmäinen. Toistepäinhän tuo on helppo tehdä php:n avulla, mutta alhaalta ylöspäin useamman queryn kanssa tulee ongelmia.

Ajattelin, että tähän olisi jonkin sortin systeemin myslin puolelta ja sellainen löytyikin, nimittäin autocommit. Eli jotenkin näin:

START TRANSACTION;
INSERT INTO artist (id, name) VALUES (1, "Jean Sibelius");
INSERT INTO titles (id, artist, name) VALUES (1, 1, "Sinfonia");
jos joku failas:
    ROLLBACK;
jos kaikki ok:
    COMMIT;

Mutta eikös tän kanssa tule ongelmia, kun skriptiä ajetaan monta kertaa päällekäin? Vai osaako tuo mysql tunnistaa, että nyt on eri (tai siis eri käyttäjän ajama) skripti kyseessä?

Tietenkin transaktioita käytettäessä on mahdollista päästä deadlock tilanteeseen, eli transaktio x odottaa että objekti a vapautuu transaktiolta y, joka taas odottaa että objekti b vapautuu transaktiolta x. Silloin lopulta jompi kumpi transaktio "tapetaan" eli rollbackataan väkisin ja toinen transaktio ajetaan loppuun. Deadlock-tilanteita voi tietysti välttää niin ettei lukitse sellaista mitä ei oikeasti ole pakko ja esimerkiksi pyrkii käpistelemään objektit samassa järjestyksessä.

Meinasin siis lähinnä tälläistä ongelmaa:

jos php-tiedosto on näin:

<?php
$yhteys = mysql_connect("localhost", "user", "pass");
mysql_select_db("kanta");

mysql_query("START TRANSACTION");
if(mysql_query("INSERT INTO artist (id, name) VALUES (1, 'Jean Sibelius')") && mysql_query("INSERT INTO titles (id, artist, name) VALUES (1, 1, 'Sinfonia')")) {
    mysql_query("COMMIT");
}
else {
    mysql_query("ROLLBACK");
}

mysql_close($yhteys);
?>

Jos nyt käy niin huono, tuuri, että käyttäjä A ja käyttäjä B avaa tuon nettisivun tismalleen samaan aikaan, niin nuo transactionit menee päällekkäin, ja lopputulos on siinä, että mahdollisesti joku inserteistä jää laittamatta, tai molemmat failaa.

Tunnistaako siis mysli nämä transactiot erilliseksi, vaikka niitä kutsutaan samasta ip-osoitteesta, samoilla tunnuksilla ja samalla php-tiedostolla. Jos ei tunnista, niin miten tuolta voi sitten välttyä?

transaktiot ovat yhteyskohtaisia. Avaat yhteyden tuossa mysql_connect -kohdassa.