Controle op Queries

(Natuurlijk) gaat dat goed omdat de queries syntactisch gezien (qua vorm) correct zijn. De WHERE-conditie leverde alleen geen records op. Het al dan niet hebben van een lege recordselectie bepaalt niet of een UPDATE/DELETE query "succesvol" was.

Als je echter wilt/verwacht dat er een specifiek aantal records verwijderd zou moeten zijn, dan zou je dit kunnen controleren met affected_rows, en op grond van het resultaat daarvan je transactie verder aansturen.

Hi Thomas,


Thanks voor de reactie. Misschien een domme vraag maar ga hem toch stellen.
De tabellen staan in InnoDB formaat met bovenstaande code wil ik de 2 queries in een transactie verwerken zodat beide altijd worden uitgevoerd.

Als de queries syntactisch correct zijn zal die altijd true teruggeven maar waarom maak ik dan gebruik van InnoDB?

Als je een bank hebt en bij een persoon -100 en bij de ander +100 dan word toch gekeken of beide correct zijn uitgevoerd?
Dat zou dus beteken dat als als de queries syntactisch correct zijn dit bij een bank ook mis gaat?

Hoe ga ik dit uitleggen. Database transacties zijn geen wondermiddel voor alles wat fout kan gaan. Het zorgt enkel dat als er in een serie van queries één of meer queries mislukken (van mijn part omdat er een internet storing optreedt) OF een transactie nooit afgesloten wordt met een commit de andere queries ook niet uitgevoerd worden.

De enigste zekerheid die je dus krijgt is dat of alle queries (correct) uitgevoerd worden, of alle queries worden NIET uitgevoerd.

Dit is puur om te voorkomen dat er "Losse eindjes" in je database gaan voorkomen.

Ik heb laatst een soort Whatsapp chat appje gemaakt. Hiervoor had ik (onder andere) de volgende tabellen:

- conversations (Lijst van gesprekken)
- conversation_user (een koppeltabel waarin bijgehouden wordt welke gebruikers deelnemen aan een conversatie)

Daarnaast heb ik een formulier gemaakt waarin iemand een nieuwe conversatie kan aanmaken. In het formulier kan een onderwerp opgegeven worden welke in de tabel conversations wordt opgeslagen. Tevens kunnen in hetzelfde formulier gebruikers geselecteerd worden welke aan de conversatie toegevoegd mochten worden (minimaal de gebruiker zelf plus een andere gebruiker).

Nadat het formulier verstuurd is moest ik dus een nieuwe regel toevoegen in de tabel conversations maar ook twee of meer regels toevoegen aan de tabel conversation_user (voor iedere deelnemer één regel).

Omdat ik niet wil eindigen met een "conversatie" zonder deelnemers heb ik deze queries dus in een transactie gezet.

Hoop dat t helpt :-)

[size=xsmall]Toevoeging op 22/01/2020 21:59:29:[/size]

In jouw voorbeeld met de bank zal de boekhouding nooit uit balans raken als er één van de twee queries in de transactie mislukken. Maar wat wel blijft is een boze klant van de bank die gaat vragen waarom zijn opdracht niet uitgevoerd is. Immers is er iets fout gegaan waardoor beiden queries niet uitgevoerd zijn. Echter is dit altijd nog beter als dat iemand honderd euro bijgeschreven krijgt die nergens terug te herleiden is. Dat zou de bank flink geld kunnen gaan kosten.

[size=xsmall]Toevoeging op 22/01/2020 22:14:11:[/size]

Overigens kun je natuurlijk ook nog met PHP fouten afvangen en besluiten een rollback te doen in plaats van een commit maar dit lijkt mij niet echt de bedoeling van database transacties. Je zou jezelf moeten afvragen of je de fouten die dan optreden niet vooraf had kunnen afvangen. Zoals je in jouw eigen voorbeeld vooraf had kunnen checken of de rij met ID 3 en 6 wel bestaan.

Zoals @Frank al aangeeft valt wat je vraagt in een heleboel onderdelen uiteen, die elk een aparte uitleg nodig hebben. Tis net een kindersurprise.

Als de queries syntactisch correct zijn zal die altijd true teruggeven maar waarom maak ik dan gebruik van InnoDB?

Dit staat redelijk los van elkaar. Je gebruikt bij voorkeur de InnoDB engine als jouw applicatie een administratieve database nodig heeft. InnoDB stelt je namelijk (o.a.) in staat om relaties tussen tabellen op database-niveau af te dwingen (via foreign keys). Dit maakt je database tot een echte relationele database. Deze relaties zorgen ervoor dat gegevens onderling kloppen en blijven kloppen, oftewel, dit zorgt ervoor dat de referentiële integriteit (tussen gegevens) wordt gewaarborgd. Ook transacties dragen hier aan bij doordat alles in een transactie wel of niet wordt doorgevoerd als één "ondeelbaar" geheel. Maar eenzelfde transactie kan tegelijkertijd meerdere keren (parallel) worden uitgevoerd - deze zijn onderling niet ondeelbaar, waarover zo direct meer.

Als je een bank hebt en bij een persoon -100 en bij de ander +100 dan word toch gekeken of beide correct zijn uitgevoerd?

Met "correct" bedoel je hier waarschijnlijk dat er geen geld uit het niets wordt gecreëerd noch verloren gaat indien deze mutaties in een bepaalde volgorde plaatsvinden. Ja, dit is een klassiek voorbeeld, en ja, transacties kunnen helpen om dit goed te laten verlopen, maar nee, het gebruik van transacties alleen is hiervoor niet genoeg.

Op het moment dat je een soort van eindige bron (resource) hebt die men kan gebruiken, dan zouden wijzigingen hierin bijvoorbeeld als volgt geïmplementeerd kunnen zijn:

vraag bron X op
... nog wat code met controles, beslismomenten etc ...
werk bron X bij (mogelijk onder bepaalde condities)

En nu heb je gehoord dat transacties extra wit wassen, dus maak je hier de volgende routine van:

start transactie
    vraag bron X op
    ... code ...
    werk bron X bij
commit transactie

And Bob's your uncle right? WRONG.

Waar je hier geen rekening mee houdt is dat het (parallel) uitvoeren van twee of meer soortgelijke transacties onderling niet ondeelbaar zijn. Stel dus dat je twee transacties op eenzelfde bron hebt, dan kunnen de operaties op bron X verweven zijn, bijvoorbeeld als volgt (gemakshalve hier even A en B genoemd):

start transactie A
start transactie B
    vraag bron A.X op
    vraag bron B.X op
    ... yada yada ...
    werk bron B.X bij
    werk bron A.X bij
commit transactie B
commit transactie A

En je ziet de bui mogelijk al hangen, hier gaat dus waarschijnlijk data verloren en klopt de eindwaarde van X mogelijk niet meer. Hoe voorkom je dit? Door de gedeelde resource te locken.

En ja, natuurlijk is dat hele transactie gezever niet nodig als je maar één query hebt of de queries anders opstelt, maar als het de bedoeling is dat op elk moment maximaal één proces (transactie) toegang mag hebben tot een resource, dan zou je hier dus ook maatregelen voor moeten nemen om dat af te dwingen, maar dit doen transacties dus verder niet uit zichzelf.

Dit doe je met de FOR UPDATE toevoeging aan je SELECT-statement. Deze kan records (of tabellen) locken, al naar gelang de selectie in het WHERE-deel. Dan, op het moment dat in een transactie een record wordt opgevraagd dat vergrendeld is, dan zal er een poos gewacht worden totdat dit record is vrijgegeven en vervolgens claimt de transactie meteen de exclusieve lock op dit record. Op deze manier maak je meerdere instanties van eenzelfde transactie onderling echt ondeelbaar.

Maar ja eh, iets met klok en klepel, transacties zijn superhandig om te hebben om te zorgen dat je data correct blijft, maar dan moet je wel de regels van het spel volgen :). Het starten van een transactie is inderdaad geen toverspreuk waarna alles vanzelf goed gaat. Hier moet je iets voor doen en je moet begrijpen hoe het werkt.

Dat zou dus beteken dat als als de queries syntactisch correct zijn dit bij een bank ook mis gaat?

Nee want dit soort mutaties hebben dit, of een soortgelijk, mechanisme ook (resource locking), die garandeert dat er niet meerdere wijzigingen parallel kunnen plaatsvinden op eenzelfde rekening. Dit is iets anders als, en heeft een ander doel als het effect van een transactie (het uitsluiten van het onvolledig uitvoeren van een rij bewerkingen). Gezamenlijk (transactie + lock) bereik je het gewenste doel, maar afzonderlijk is dit doel niet haalbaar.

Overigens, nu begrijp ik misschien ook wat beter waar de verwarring precies zit. Je hebt nu misschien het gevoel dat je na moet gaan dat alle queries kloppen voordat je alles commit. Maar wat als je het nu wat anders aanpakt? Wat als je van Exceptions gebruik maakt in MySQLi, of je eigen exceptions throw-d op het moment dat er een query misgaat? Je verlaat dan de normale flow van het programma, en de commit die dan aan het einde van de query riedel staat wordt dan dus nooit uitgevoerd. Op het moment dat al je scripts zijn uitgevoerd (en daarmee je database-connectie sowieso impliciet afgesloten wordt) dan worden alle openstaande queries sowieso teruggedraaid, je hoeft dus niet alle queries binnen je transactie te controleren op correct uitvoeren, mits je het op een bovenstaande wijze aanpakt.

Met de bovenstaande Exception-aanpak betekent het "bereiken" van de commit-operatie dat alle queries tot dat punt goed waren gegaan en kun je dus zonder zorgen alles direct zonder extra controles committen.

Tegelijkertijd sputtert MySQL ook niet tegen als de desbetreffende records niet bestonden. Aan de andere kant "klopt" het eindresultaat dan nog steeds - beide records zijn na afloop gegarandeerd verwijderd, dus geen kind overboord? Tenzij het dus een noodzakelijke voorwaarde was dat ze van tevoren bestonden, dan zou je deze op moeten vragen om te controleren op het bestaan ervan en deze voor de goede orde ook moeten locken (omdat hier anders mogelijk elders wijzigingen in optreden tijdens de transactie) voor de duur van deze transactie.

Woow bedankt Frank en Thomas voor de heldere uitleg!
Hier kunnen we iets mee :)

Dan nog een vraag: ik kan zeer weinig vinden over start transactie in MySQLi
Wel zie ik een hoop over PDO, hoe start ik een transactie goed en commit ik in MySQLi?

Hier op PHPhulp hebben we een mooie tutorial staan:

https://www.phphulp.nl/php/tutorial/overig/mysqli--een-nieuwe-manier-van-interactie-tussen-php-en-mysq/533/transacties-met-mysqli-commit-en-rollback/1361/

[sup]Misschien moet je eens overwegen om juist PDO te gaan gebruiken in plaats van die Mysqli functies. PDO biedt namelijk toch wel een paar voordelen[/sup]

Frank Nietbelangrijk op 23/01/2020 18:06:42

[sup]Misschien moet je eens overwegen om juist PDO te gaan gebruiken in plaats van die Mysqli functies. PDO biedt namelijk toch wel een paar voordelen[/sup]

Ik denk dat ik op dat artikel al een keer had gereageerd, zal kijken of ik een linkje kan vinden, maar de argumenten die daar worden gebruikt gaan voor een (groot) deel niet echt op. Als je de argumenten voor PDO over mysqli afweegt in plaats van telt, dan is het helemaal niet zo vanzelfsprekend dat PDO "beter" zou zijn dan mysqli, zoals min of meer in dat artikel wordt gesuggereerd.

Thomas, ik wil liever niet spreken over "beter" maar meer over voordelen dat voor mijn gevoel dus minder zwaar weegt dan jouw "beter". Jij gebruikt zelf ook een wrapper voor mysqli omdat je de functionaliteit die standaard met php meegeleverd wordt enigszins te kort schiet. PDO biedt eigenlijk ook min of meer dat schilletje om de standaard mysqli functies. Zo worden er door PDO exceptions toegevoegd en hebben we een eenduidige aanpak en niet zoals in PHP met mysql een OOP en een Procedural stijl. Al bij al voor mij toch wat solider. En ik lees deze mening op alle andere forums.

Laat ik het anders verwoorden: de voordelen die in dat artikel worden genoemd zijn duidelijk gekleurd, en daarnaast zijn het geen echte voordelen. Het is overwegend propaganda uit het pro-PDO kamp.

Ik zeg ook niet dat je te allen tijde mysqli zou moeten gebruiken, beide hebben toepassingen en soms is PDO handiger dan mysqli en soms ook andersom.

Het vervelende van zo'n artikel is dat veel mensen die met deze materie beginnen waarschijnlijk de bomen door het bos niet kunnen zien en vervolgens zo'n artikel lezen en dan op grond van deze (wat mij betreft foutieve of op zijn minst zeer gebrekkige) informatie besluiten om dan maar PDO te gebruiken.

En vervolgens gebruiken zij op hun beurt deze referentie op het moment dat anderen aan hun vragen wat zij zouden moeten gebruiken of waar zij de voorkeur aan geven, en mogelijk ook waarom. Daarbij herhalen ze braaf de argumenten die in dat artikel werden genoemd, maar die argumenten snijden echt weinig hout.

Op den duur is het een echt gevestigd misverstand dat dit artikel een soort van autoriteit is die zou "aantonen" dat je toch moet concluderen dat PDO uiteindelijk een "betere" keuze zou zijn.

Laten we heel snel door de speerpunten heenrennenlopen:
API support
Beide ondersteunen OOP. Uit oogpunt van modulair werken (en omdat je andere code ook deze aanpak heeft etc.) verdient de OOP-aanpak meestal de voorkeur. In dit opzicht maakt het dus niet uit wat je kiest.

Database support
PDO ondersteunt 12+ drivers, mysqli enkel Mysql.
Okay. Maar als je voor de keuze PDO vs MySQLi stond, dan maak je dus blijkbaar gebruik van een MySQL-database, anders was je meteen met PDO aan de slag gegaan nietwaar? Of je gebruikt een andere database in welk geval je aangewezen bent op vendor-specifieke code/libraries in welk geval PDO (alsook mysqli) om te beginnen geen optie was.

Dan het volgende, aangenomen dat je dus een MySQL-database gebruikt. Heb je dan ondersteuning voor andere database(driver)s nodig? Hoe vaak komt het in de levensloop van een applicatie voor dat je van database schakelt? Ik heb het nog niet meegemaakt.

En als daar sprake van is, dan is dit nog steeds een traject, en niet een of andere schakelaar die je omgooit waarbij je alles in de andere db kiepert en je meteen kunt gaan ofzo.

En zelfs als je in die situatie PDO had gebruikt, dan had je dat nog steeds niet gered (los van het feit dat je alles zou moeten testen, tenzij je het leuk vindt om gevaarlijk te leven), omdat PDO niet uit zichzelf voorziet in Database-Abstractie - de SQL-code die je schrijft is nog steeds toegespitst op de database en is niet per definitie compatibel met de andere database die worden ondersteund door PDO. Tenzij je dus object relational mapping toepast zoals later in het relaas wordt voorgesteld, maar dat wordt hier gemakshalve even vergeten. En het feit dat je nog steeds maatwerk moet schrijven voor een specifieke database om het verschil te overbruggen, dus wat was ook alweer het voordeel van al deze abstractie?

Named Parameters
Okay, dit kan handig zijn (in PDO), maar als je je variabelen omschrijvende namen geeft dan hoeft dit niet echt een issue te zijn? En in MySQLi zijn prepared statements... niet optimaal.
Los daarvan, al dat binden van variabelen etc., aint nobody got time for that!
Dan zou je een debat kunnen voeren over het gebruik van prepared statements, en of je dat verplicht zou moeten stellen. Ik denk dat het belangrijker is dat je snapt wat je doet, in plaats van dat je je redding zoekt in een specifieke methodiek, en je jezelf dan veilig waant voor potentiële gevaren, en vervolgens dingen doet die zeer onveilig zijn (variabelen concateneren in prepared statements, het gebeurt nog steeds). Er zijn meerdere manieren om queries te beveiligen en in ALLE gevallen moet je weten waarom dit queries veilig maakt. Dit wordt dan verankerd in een procedure of werkwijze.

Maar je zult nog steeds altijd moeten waken voor de gevaren op elk moment dat je met "(user) data" werkt (informatie uit een of andere interne of externe bron, ook de data in je eigen database zou je als "niet veilig" moeten bestempelen).

Object Mapping
Lijkt me handig als je betaald wordt per geschreven regel code :p.
Of je mappers gebruikt staat verder ook los van welke aanpak je kiest. Dus ook in dit geval maakt het niet uit.

Security
Het injectie-voorbeeld wat daar gegeven wordt werkt volgens mij niet eens (meer), standaard staat MySQLi (en ook PDO, of liever gezegd de MySQL driver van PDO) niet toe dat meerdere queries tegelijkertijd worden uitvoeren (en waarschijnlijk is dit standaard in MySQL zelf). Daarnaast is het toestaan en/of gebruiken van multiqueries of aanverwante functionaliteit heel erg onveilig (heeft iig haken en ogen ten aanzien van escaping) en ook nogal onverstandig. Ook bieden multiqueries geen garanties voor het volledig uitvoeren van alle queries in zo'n batch en daarbij is er een veel beter alternatief hiervoor: transacties.

Het maakt niet uit of je PDO gebruikt of mysqli, als je niet snapt wat je aan het doen bent en/of je geen procedure hebt die voorschrijft hoe je hier veilig mee om zou moeten gaan dan is het hek sowieso van de dam.

Performance
Maakt niet uit, dit wordt zelfs op de PHP-site zelf verkondigd.

Om dan vervolgens te kunnen concluderen:

Ultimately, PDO wins this battle with ease.

Alsof PDO in alle/de meeste opzichten met kop en schouders uitsteekt boven de rest?

Nou nee, niet echt. Of liever gezegd, echt niet.

Gemakshalve vergeet het artikel ook te vermelden dat de leercurve niet zit in het handjevol classes en methoden waar PDO over beschikt, maar in de database driver-specifieke instellingen die bepalen hoe de database reageert op bepaalde queries. PDO is namelijk niet op voorhand "gebruiksklaar" voor wat voor database dan ook. mysqli daarintegen is "ready to go".

Het enige wat PDO "echt" brengt is enige standaardisatie in het communiceren met je database, maar de communicatie zelf is nog steeds database-specifiek.

Het enige wat ik eigenlijk wil overdragen is dat je je niet moet laten overrompelen (of "omlullen") door dit soort artikelen maar dat je zelf kritisch blijft nadenken over wat er wordt gezegd en wat er wordt beweerd. Schat dit vervolgens op waarde in plaats van enkel de pro's en contra's te tellen want in dit artikel zijn de (uitvergrote) pro's nogal zwak. Daarom is wat mij betreft dit artikel nou niet bepaald een goede advocaat voor PDO.

Zowel PDO alsook mysqli hebben hun sterktes en gebreken, maar zolang ik (uitsluitend) van MySQL/MariaDB gebruik maak is er voor mij geen enkele reden om PDO te gebruiken... tenzij ik hier een specifieke reden voor heb. Zo waren fatsoenlijke prepared statements handig om een importscript snel te laten verlopen (en om dit in mysqli te programmeren voelt onwijs omslachtig aan). Maar dat is precies het ding: laat deze keuze een ontwerpbeslissing zijn, de klus bepaalt het gereedschap, niet andersom.

Dat hele artikel is zoiets als "Hamer vs Zaag", en de hamer komt als winnaar uit de bus. Dan heb ik zoiets van "Toegepast op welke situatie?". Een hamer is lang niet altijd het betere stuk gereedschap.