Ošetření chyb InnoDB

Školení, která pořádám

V tabulkách typu InnoDB může dojít k několika specifickým chybám, z nichž obzvláště nepříjemné jsou ty, které souvisí s transakcemi (1205 a 1213). Pokud totiž ovladač InnoDB zjistí, že na sebe některé transakce vzájemně čekají, začne je postupně ukončovat. Obvykle stačí, když danou transakci spustíme znovu, v aplikaci na to ale musíme myslet:

<?php
class InnoDB {
    public $attempts = 10;
    private $queries = array();
    private $errors;
    
    public function __construct() {
        $rollback_on_timeout = (version_compare(mysql_get_server_info(), '5.0.12') < 0
            || mysql_result(mysql_query("SELECT @@innodb_rollback_on_timeout"), 0)
        );
        $this->errors = array(
            1213 => true, // ER_LOCK_DEADLOCK
            1205 => $rollback_on_timeout, // ER_LOCK_WAIT_TIMEOUT
        );
    }
    
    /** Spuštění příkazu v rámci InnoDB transakce
    * @param string $query SQL dotaz
    * @return resource výsledek funkce mysql_query()
    * @copyright Jakub Vrána, https://php.vrana.cz/
    */
    public function query($query) {
        $this->queries[] = $query;
        for ($i=0; $i < $this->attempts; $i++) {
            $return = mysql_query($query);
            $errno = mysql_errno();
            if ($this->errors[$errno] !== false) { 
                break;
            }
        }
        if ($this->errors[$errno]) { 
            for (; $i < $this->attempts; $i++) {
                mysql_query("START TRANSACTION");
                foreach ($this->queries as $val) {
                    for (; $i < $this->attempts; $i++) {
                        $return = mysql_query($val);
                        $errno = mysql_errno();
                        if ($this->errors[$errno] !== false) {
                            break;
                        }
                    }
                    if ($i >= $this->attempts || $this->errors[$errno]) {
                        continue 2;
                    }
                }
                break; // OK
            }
        }
        return $return;
    }
    
    public function commit() {
        $this->queries = array();
        return mysql_query("COMMIT");
    }
    
    public function rollback() {
        $this->queries = array();
        return mysql_query("ROLLBACK");
    }
}
?>

Metoda query se pokusí provést dotaz. Pokud se to nepovede kvůli chybám souvisejícím se zamykáním, pokusí se znovu spustit všechny příkazy v transakci. Limit počtu pokusů je uveden ve vlastnosti $attempts. Metoda si příkazy ukládá do pole $queries, které se vyprazdňuje v metodách commit a rollback. Třída počítá s tím, že si transakci sami zahájíte a že nepoužíváte AUTOCOMMIT, pro reálné nasazení ji tedy bude nejspíš nutné ještě přizpůsobit. Stejně tak z této třídy možná raději vytvoříte potomka třídy mysqli.

V MySQL 5.0.13 došlo ke změně chování – při timeoutu se už nezruší celá transakce, ale jen poslední příkaz. Skript jsem upravil tak, aby s tím počítal. V MySQL 5.0.32 byla zavedena proměnná innodb_rollback_on_timeout, která dovolí zapnout původní chování. Skript jsem upravil tak, aby s touto proměnnou počítal.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Řešení problému, 21.3.2007, diskuse: 17 (nové: 0)

Diskuse

krteQ:

Pouzivam vetsinou autocommit, pro reseni techto chyb pouzivam v dlouhodobejsich skriptech vyhazovani vyjimek. Kdyz mi Inno hodi 1205, dam sleep par sekund (lepsi by asi bylo exponencialni cekani) a zkusim znovu pozdeji dokud neni DB volna. Deadlock jsem nastesti zatim nepotkal :)

Štěpán Svoboda:

Děkuji - toto je opravdu přínosný článek. Jak to děláš Jakube, že stále píšeš s větší či menší časovou přesností o problémech, kterým zrovna čelím :) To by mne opravdu zajímalo.

Přeji pěkný den

Štěpán Svoboda:

Já jsem před časem zkoušel některé tabulky převést na InnoDB kvůli tomu, že jsem potřeboval použít transakce (zdálo se mi to nejlepší) a nakonec jsem od toho upustil právě kvůli problémům s lock wait timeout. Přikládal jsem to mixu tabulek InnoDB a MyISAM o kterém jsem četl, že jej nelze použít současně - což mně šlo normálně.

ikona Jakub Vrána OpenID:

Tabulky MyISAM a InnoDB se vedle sebe používají běžně. Skoro všechny tabulky mohou být kvůli transakčnímu zpracování v InnoDB a jedna může být pro fulltextové vyhledávání v MyISAM.

Štěpán Svoboda:

Já jsem si to myslel. Každopádně děkuji za upřesnění.

ikona finc:

Ja třeba používám MyISAM na chybové tabulky, které nesplňují podímnky pro referenční integrity. Plním data z jednoho systému, vše je v pohodě, nplní se do tabulky InnoDB, ve chvíli,kdy nesplní podmínku, hodím jí do tabulky MyISAM. Výhodou pak je i rychlejší vyhledávání chyb, atd.

--==[FReeZ]==--:

Nebylo by vhodnejsi z hlediska nezavislosti na databazi a vyssiho vykonu zacit uzivat PDO?

ikona Jakub Vrána OpenID:

Co se výkonu týče - máte nějaké benchmarky? Já vím pouze to, že když se v PDO nezapne emulace prepared statements (místo jejich faktického provádění), tak je s MySQL mnohem pomalejší (ale na vině je asi spíš MySQL).

Jen podotknu, že i kdybychom použili PDO, tak chyby popisované v tomto článku musíme ošetřit stejně, protože PDO se nesnaží odlišnosti jednotlivých databází skrývat.

uzivatel:

http://dealnews.com/developers/php-mysql.html

ikona Jakub Vrána OpenID:

Řekl bych, že v tomto testu emulace prepared statements použita nebyla a ty se tedy nepřímo použily i ve variantě PDO (not prepared). Je potřeba nastavit volbu PDO::MYSQL_ATTR_DIRECT_QUERY (v novějších verzích PHP je defaultně nastavena).

--==[FReeZ]==--:

"chyby popisované v tomto článku musíme ošetřit stejně,
protože PDO se nesnaží odlišnosti jednotlivých databází skrývat".

Mate pravdu i nemate =) Metody tridy PDO slouzici k praci s transakcemi (BeginTransaction, Commit a Rollback) jsou nezavisle na konkretni databazi, neni liz pravda?

Uvedeny benchmark bez detailne vysvetlene metodiky mereni a realnych (napr. korporatnich) dat mi pripada velice demagogicky, vykon ma PDO principalne vyssi, nez vsechna databazova reseni napsana v PHP (protoze je psano v cpp) a muze tedy mit teoreticky jedine stejny, nebo vyssi vykon nez mysqli. Vzhledem k tomu, ze je to vcelku nove reseni, mozna jeste chvili potrva, nez se kod vice optimalizuje.

Osobne benchmark nemam, ani jej nemohu poskytnout, nejsem databazovy specialista a nevim o skutecne objektivnim zpusobu, jak zmerit vykon teto abstraktni vrstvy (se vsemi supportovanymi DB nikoliv jen s MySQL - ktere ani nesplnuje klicovy standard SQL-92).

ikona dgx:

ad popis metodiky: je tam zdrojový soubor ke stažení

ad PDO je napsano v cpp: není pravda, je napsano v C. Stejne jako mysql nebo mysqli extension.

ad principialnost: nativni řešení (mysql, mysqli) jsou vždy rychlejší, než univerzální řešení (PDO)

ikona spaze:

> ad principialnost:

PDO může být klidně stejně rychlý a i rychlejší, jako ext/mysql. Ale taky pomalejší. Je to driver-based a ty PDO drivery používají stejný funkce, jako používá ext/mysql, takže záleží na autorovi konkrétní ext/, ne na tom, jestli je to univerzální, nebo jednoúčelové (nativní asi není úplně správný slovo).

ikona spaze:

> Já vím pouze to, že když se v PDO nezapne emulace prepared statements (místo jejich faktického provádění), tak je s MySQL mnohem pomalejší (ale na vině je asi spíš MySQL).

Tipuju, že to platí jen pro SELECTy, pro INSERT a UPDATE asi ne. Při prepared statements se nepoužívá query cache (vina na straně MySQL), proto je to pomalejší. Při INSERTech to naopak z principu bude rychlejší, teda pokud kluci z MySQL zas neudělali někde nějakou kravinu ;)

ikona Jakub Vrána OpenID:

Platí to bohužel pro všechny typy dotazů. Problém je v tom, že data se od dotazu přenáší odděleně, takže je s tím větší režie.

Jen pro informaci: query cache se brzy bude používat i u prepared statements: http://www.mysqlperformanceblog.com/2007/…-support-comming/

ikona spaze:

> Problém je v tom, že data se od dotazu přenáší odděleně, takže je s tím větší režie.

To, že se data přenáší odděleně není problém, ale naopak obrovská bezpečnostní výhoda ;) Ale jinak chápu, všechno něco stojí.

Rychlost nelze poznat na jednom INSERTu, ale pokud potřebuju nalejt do databáze pár set řádek navíc (to jsem možná zapomněl poznamenat ;), tak při prepared statements se ušetří režie parseru, kterej jinak zbytečně dělá pořád to samý.

ikona Jakub Vrána OpenID:

O bezpečnosti není sporu, stejně jako o přehlednosti kódu. Bavíme se ale o výkonu - teoreticky to popisuješ správně, ale zkus to ověřit praxí - kde je hranice, od které se prepared statements vyplatí používat i z výkonnostních důvodů?

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.