MySQL >5.3 create table + locking

Adatbázis: SQL, XML DB

Üdv,

MySQL v5.3-astól felfelé változik a locking kezelés: már nem lehet táblát létrehozni, ha bármely másik tábla lock-olva van.

Ref.: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/create-table.html

"Beginning with MySQL 5.5.3, you cannot execute CREATE TABLE or CREATE TABLE ... LIKE while a LOCK TABLES statement is in effect."

5.5 alatt kellene megoldanom, hogy míg más táblák lock-olva vannak, létrehozzak táblákat a műveletek közben, de úgy, hogy a többin a lock-ot nem oldom fel.

Lenne erre ötlete valinek? Köszi.

  • 8428 megtekintés

( pityulaman1983 v | 2012. 08. 16., cs – 14:50 )

Mindenképpen fizikai tábla kellene?

-----------
"640GB sokmindenre elég"

Temporary táblára gondolsz? Az nekem nem jó, mert igazából 2 dolog miatt kellene nekem a fenti:

- egyrészt temporary táblának, csak éppen session után is kell még, és én döntöm el mikor törlöm - tehát a beépített "create temporary table" megoldás nem jó, mert az a session lezárásakor (vagyis a weboldal legenerálásának végeztekor) törli azt automatikusan ugye

- másrészt megnézem, hogy egy tábla már létezik-e, és ha nem, akkor létrehozom - itt meg olyan a helyzet, hogy kényelmetlen és kicsit áttekinthetetlen lenne, ha előre hoznám létre azokat

Ez miatt a kérdésedre a válasz: igen.

Hmm...

mysql> create database A;
mysql> create database B;
mysql> use A;
mysql> create table table1 (id int(6) auto_increment);
mysql> lock table A.table1;
mysql> use B;
mysql> create table table1 (id int(6) auto_increment);

Így az utolsó create table statementet sem tudja végrehajtani a B adatbázisban, mert A-ban lockoltam egy táblát?
Mysql 5.1 FTW!

-----------
"640GB sokmindenre elég"

Mondjuk nekem 1 db-n belül kell az egész. Egy weboldalhoz használom, de csak 1 db-t.

5.1 alatt ez lefut rendben: 


mysql> use mydb;
mysql> unlock tables;
mysql> drop table if exists t1;
mysql> drop table if exists t2;
mysql> create table t1 (id int);
mysql> lock table t1 read;

mysql> create table t2 like t1;
vagy
mysql> create table t2 (id int);

5.5-ön az utolsó parancsnál hibát dob:

"ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES"

Nem vagyok egy db szaki, de a menetközbeni folyamatos tábla törlés/létrehozás nem tűnik túl meggyőzőnek. Ha már mindenképp táblát akarsz űríteni, akkor neked a TRUNCATE lenne a hasznos és tranzakcióval kombinálva.


BEGIN;
TRUNCATE TABLE IF EXISTS T1;
TRUNCATE TABLE IF EXISTS T2;
COMMIT;

(Nem tudom a truncate mennyire tranzakció barát, de ki fog derülni asszem.)

( log69 | 2012. 08. 16., cs – 15:13 )

Egy megjegyzés: a fenti úgy kellene nekem, hogy nem szeretnék a "create table" parancsaim előtt "unlock tables"-öt futtatni, mert akkor az egész locking megoldásom értelmét veszti.

Azt nem értem, hogy ez miért így van? Nem életszerű, hogy egy weboldal legenerálásakor locking-ot használok bizonyos táblákra (hogy ezek állapota ne változhasson más user-ek interakciója miatt a műveletem alatt), és ez közben van szükségem egy tábla létrehozására?

Egyaltalan nem eletszeru az, hogy tablakat hozol letre a _normal mukodes reszekent_. Setup/upgrade az nyilvan mas. Ha neked a mukodes reszekent kell tablakat gyartanod, akkor el kell gondolkodnod azon, hogy lehet, hogy rossz az adatbazisod szervezese.

Illetve nem ertem, miert gond a tabla lete/nemlete. Ha ures, akkor annyira mindegy, hogy ott van-e nem?

Egyekbent meg: CREATE TABLE IF NOT EXISTS.
-- 

Ki oda vagyik, hol szall a galamb, elszalasztja a kincset itt alant. | Gentoo Portal

( andrej_ v | 2012. 08. 16., cs – 16:01 )

5.5-től emlékeim szerint InnoDB a default táblatípus, amivel remekül lehet tranzakciókat kezelni. Minek használsz külön LOCK-ot? A címet pedig 5.5.3-ra lenne érdemes javítanod, mert 5.3-as MySQL tudtommal sosem volt kiadva, csak 5.4 (asszem mint tech preview).

A remek mysql autocommit állapotban van MyISAM esetén és sokan ehhoz vannak szokva. Az érdemi adatbázis kezelős feature-ők fel sem szoktak sajnos merülni. A MyISAM + autocommit + LOCK megoldást pedig sokan kvázi tranzakciózásnak fogják fel.

Nemrég szomorúan tudtam meg, (ugyanis annó LOCK TABLE jogot valamiért nem kapott minden dbuser a webszerveren, de ez nem volt szándékos), hogy a remek új Joomla nekiállt LOCK TABLES-özni... hát fogtam a fejem.

PHP-ból remekül ment:

BEGIN;
UPDATE...
-- phpból ellenőrzöm hogy sikeres-e, ha igen akkor
DELETE...
-- megint jön a PHP..., ha sikeres
INSERT...
-- ha ez is jó, akkor
COMMIT;

A PHP kódként írt részekben remekül elférne más is, és szerintem volt is a kedvenc projektemben, és a false ágakon végig ott volt szépen a ROLLBACK, hogy ne érjen meglepetés.

Az életben nem foglalkoztam DB-vel, ezért könnyen lehet hogy rosszul látom a dolgokat. Mégis úgy gondolom, hogy ez nekem nem jó, mert a legelső SQL művelet és az legutolsó között nem szabad hogy változzanak a szóban forgó tábláim.

Ha jól értem a tranzakció lényegét, az az, hogy egyben be tudod küldeni a műveleteket - tehát sebességben sokat számíthat, illetve ott a rollback. De nekem az a célom, hogy egy másik user-t várakoztasson a lock-om, amíg az előző user-nek le nem generáltam az oldalt. Különben csúnya adat korrupciók lehetnek.

Amúgy köszi mindenkinek a segítséget.

Ez eddig is elég egyértelmá volt:
http://dev.mysql.com/doc/refman/5.0/en/commit.html

"With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK."

Az isolation level-t is nézegetem, de még nem igazán értem hogy ez hogy segít rajtam. Így is úgyis meg kell mondanom előre, hogy a művelet kezdete előttől mely táblák nem változhatnak. A lock-on kívül még mindig nem értem hogy erre miért lenne jobb a tranzakció, vagy mi más lenne jobb?

Így is úgyis meg kell mondanom előre, hogy a művelet kezdete előttől mely táblák nem változhatnak.

Nem jó irányból közelíted meg a problémát. Hogy egy klasszikust idézzek: ha kalapács van a kezedben, minden tárgy szögnek látszik.

Mi az a szituáció, ami tábla szintű lock nélkül problémát okozna?

Pl az, ha a tranzakcióm lépései között egy másik user megváltoztatná a használt tábláim adatait. Lépések közt azt értem, hogy a parancsok közt kell script-et futtatnom, és felhasználnom táblák adatait. Tehát vakós időben előfordulthat, hogy a következő műveletnél rossz adatot kapok - mivel az adatok is kapcsolatban állnak egymással.

Újra végig kell gondolnom az egész megoldásomat. Egyelőre nem látom át azonnal, hogy megtudnám-e oldani a problémakört úgy, hogy összeszedem az összes SQL műveletet 1 csokorba és tranzakcióként adom át. Már elég nagy a kódbázisom, de átgondolom és megírom majd mire jutottam.

Nem kell mindenképp egy csokorba gyűjtened, de tényleg nem egészséges sokáig variálni tranzakció közben, ha több egyidejű felhasználó is lehetséges az adatbázison. A tranzakciók óriási előnye a ROLLBACK opciód a végén, mert ha valahol gebaszba ütközöl, amit előre nem tudhatsz, akkor egyszerűen aszondod ROLLBACK és ennyi. A LOCK TABLES-ös megoldásnál viszont esélyed sincs visszaállni, hacsak nem követed valahogy a változásokat.

Nem kapsz. Ettől tranzakció a tranzakció. A LOCK TABLE-ös játékot direkt tranzakció szimulálásra szokták használni, ahelyett hogy rendes adatbázis kezelőt vagy esetleg táblatípus használnának. A tranzakciók izolálása az adatbázis kezelő dolga, neked nem kell vele törődni.

repeatable read és serializable izolációs szintnél ez nem történhet meg.
plz read carefully: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

Azért ez a neve, mert ha pl egy tranzakción belül többször is lekérdezed ugyanazt, ugyanazt az eredményt fogod kapni + te módosításaid. Ezért "ismételhető".

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions.

A sorosítható pedig úgy lockol implicite, hogy hatás szinten úgy nézzen ki, mintha a két, egymást hatásban átfedő (pl. uganazt a táblát használják), egymás után futottak volna le, nem párhuzamosan.

Tippem szerint téged a serializable izoláció tuti kisegít, viszont a DDL-ek tranzakciókra kifejtett hatásait nézd át alaposan.

Most kezdtem el mélyebben nézegetni:
http://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-model.html

Ha minden úgy megy ahogy gondolom, akkor kidobom az egész saját locking megoldásomat a francba, és csak transactions-szel oldom meg a lekéréseket. Még értelmezem a dolgot, hogy tudom átültetni a megoldásomba.

Pont ezt nézegetem..

Kezd az lejönni, hogy a tranzakcióval valóban megoldódnak a locking kérdések, tehát eleve rossz irányba indultam. De azért nem is olyan egyszerű.

Annyi már látszik, hogy az 5.5-ban az InnoDB a default tábla, és akkor átállok erre. Itt ugye row locking-ot csinál automatikusan és ez is gyorsíthat akkor ha jól gondolom, mert ha olyan műveletet végzek, amely más sorokat nem érint, és ezekhez akar párhuzamosan másik user hozzáférni, akkor nem fogja várakoztatni őt.

Azt még nem látom át teljesen, hogy az bekavarhatna-e nekem, hogy a tranzakció idejére a tranzakció előtti állapotot látják a user-ek. Ez még nem világos, ezt végig kell gondolnom. Mert ugye habár íráshoz lock-olva lesznek és várakozásba kerülnek, az olvasáskor régi adatot kapnak - habár lehet már új van. Ezt még nem látom át hogy rossz-e nekem vagy sem.

Ha egy sorból olvasol, azt nem fogja módosítani más tranzakció, ha olyannak adod meg.
Read lock lehet shared, tx-ek között, de egy write lock sorba fog állni, mert az exkluzív.
Amint valaki írna, az a tx vagy beáll és vár amíg minden readlock el nem tűnik, vagy a többi tranzakció a saját snapshotjával dolgozik tovább. Te döntésed.

Úgy látom nem fog kelleni a "serializable" mód, hanem az 5.5-től default "repeatable read" mód is megfelelő lesz nekem, mert ha jól értem, itt is csinálja a locking-ot szépen automatikusan az InnoDB. És nem kell minden select-emhez "lock in share mode", csak az írásokhoz. Azt meg lock-olja amúgy is.

A kettő mód között egyetlen egy apró különbség van:
A repeatable read csak annyit garantál, hogy egy SELECT által visszaadott sor nem változhat meg és nem tűnhet el alólad (a DELETE vagy UPDATE megvárja, hogy befejezd a tranzakciót), a serializable ezen felül azt is garantálja, hogy új sor nem "nőhet" be a SELECT alá (azaz minden olyen INSERT is várni fog, ami a select WHERE feltételeibe beleesne). A második verzió teljesítményben sokkal "drágább", mivel minden tranzakció minden SELECT-jét memóriában tárolni kell, és minden INSERT-nél az összes releváns SELECT WHERE feltételét kiértékelni. A repeatable read-hez elegendőek a SELECT által kiolvasott sorokra/mezőkre vonatkozóan lockokat tárolni.

igen.
a repeatable read a tx által megkezdett verziójú page-ről olvas végig.

a serializable lockolja is a sorokat, hogy a háttérben se változhasson meg. (ekkor a többi serializable vár, a repeatable read-ek meg a legutolsó kommitolt verziót fogják el maguknak, azaz azt, amit a sor elején említett tx is használ)

( petya | 2012. 08. 16., cs – 17:31 )

http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

Ez egy remek dolog, hogy igy van, te pedig rosszul csinalod. Esetedben ennek ugy kene kineznie hogy db, kulso muvelet, db helyett kulso muvelet, ellenorzom a kodomban, hogy sikerult e a kulso muvelet, majd db. Tranzakciokat hosszu ideig nyitvatartani eleg koltseges dolog. Az, hogy alkalmazas csinal es eldob tablakat szinten rossz design, aminek MySQL 5.1-ben csunya mellekhatasai vannak. Egyreszt drop table-kor tortenik egy szinkron unlink(), masreszt ha InnoDB-t hasznalsz, akkor a MySQL vegig fogja scannelni a buffer poolt, hogy van a tablahoz tartozo page, es ha igen, akkor kidobja belole. Nagy buffer poolnal ez sokat szamit. Amit csinaltal sok usernel egyebkent sem mukodik, keves usernel meg tudod legalabb remelni, hogy ugysem lesz race condition:).

Percona server 5.5-ben levo innodb_lazy_drop_table nevu feature megoldja a fenti drop table problemat egyebkent.

( Jason v | 2012. 08. 16., cs – 19:31 )

Kérdés:
Miért kell lock-olnod egy táblát a táblalétrehozáshoz?

Más:
GET_LOCK és RELEASE_LOCK?

( log69 | 2012. 08. 22., sze – 12:35 )

Megoldottam. Kidobtam az összes "lock tables ..." és "unlock tables" hivatkozásomat. A kódom legelejére betettem egy "begin"-t, a legutolsó kilépő exit részemhez meg egy "commit"-ot, azt annyi. Ha mindent jól gondolok, akkor a db majd gondoskodik a locking-ról. És így megoldottam, hogy MySQL 5.1-től 5.5>-ig is gond nélkül menni fog a cuccom.

Lépésenként végig gondolva a progimat elvileg akkor így mennie kell. Frissítem majd a bejegyzéseimet itt, most meg indul a tesztelés. Később talán majd dobok ide is infót arról, hogy mihez kellett.

Mindenkinek kösz a segítséget. Egy számomra új területen akartam gyorsan produkálni valamit, és ezek sokat segítettek.