Forum » Programiranje » [MariaDB] UPDATE with no index LOCK. Kako?? Deadlock found when trying to get lock; try restarting transaction
[MariaDB] UPDATE with no index LOCK. Kako?? Deadlock found when trying to get lock; try restarting transaction
HotBurek ::
Dober večer.
Evo, še kar isti dan, a je nov izziv že tu.
Setup je sledeč. Database je MariaDB (InnoDB).
Na strežniku poganjam več Python skript, vsaka od teh pa dela s podatki iz svojega set-a (shop_id), vse pa uporabljajo (berejo, pišejo, spreminjajo) v isto tabelo "pages".
Se pravi, Python process za shop_id=4 ne bo nikoli posodabljal vrstice, kjer je shop_id=5. Med drugim pa vsak process pred štartom preveri, da process za isti shop_id trenutno ne teče.
Tabela "pages" vsebuje tri stolpce: id, date_time, shop_id
Tabela "pages" vsebuje dva indexa: pages_id_IDX (BTree, unique), pages_shop_id_IDX (BTree, ni unique)
No, in težava je sledeča. Ob večji obremenitve, se pravi več hkratnih skript, ter posledično več hkratnih vpisov, dobim sledeč error:
Deadlock found when trying to get lock; try restarting transaction
Na strežniku uporabim sledeč ukaz:
In v izpisu najdem, da se težava povaji, ko ena od skript poizkuša narediti single row UPDATE, za podatek v date_time stolpcu, v WHERE pogojue pa je med drugim tudi shop_id.
In tu mi ni jasno, zakaj takšen update naredil LOCK na id_IDX index? Nampreč vrednost id-ja ta UPDATE ne spremeni, ampak spremeni zgolj vrednost date_time.
In sedaj iščem opcije, kako to uredit.
Tu pa me zanima, kako omejit UPDATE ukaz, da ne izvede LOCK na pages_id_IDX index, v primeru, da ta UPDATE izvede spremembo podatka samo v stolpcu date_time?
Razmišljal sem že o tem, da bi UPDATE nadomestil z DELETE + INSERT. A to je malo too much. Morajo obstajati druge, bolj elegantne rešitve.
Evo, še kar isti dan, a je nov izziv že tu.
Setup je sledeč. Database je MariaDB (InnoDB).
Na strežniku poganjam več Python skript, vsaka od teh pa dela s podatki iz svojega set-a (shop_id), vse pa uporabljajo (berejo, pišejo, spreminjajo) v isto tabelo "pages".
Se pravi, Python process za shop_id=4 ne bo nikoli posodabljal vrstice, kjer je shop_id=5. Med drugim pa vsak process pred štartom preveri, da process za isti shop_id trenutno ne teče.
Tabela "pages" vsebuje tri stolpce: id, date_time, shop_id
Tabela "pages" vsebuje dva indexa: pages_id_IDX (BTree, unique), pages_shop_id_IDX (BTree, ni unique)
No, in težava je sledeča. Ob večji obremenitve, se pravi več hkratnih skript, ter posledično več hkratnih vpisov, dobim sledeč error:
Deadlock found when trying to get lock; try restarting transaction
Na strežniku uporabim sledeč ukaz:
SHOW ENGINE INNODB STATUS;
In v izpisu najdem, da se težava povaji, ko ena od skript poizkuša narediti single row UPDATE, za podatek v date_time stolpcu, v WHERE pogojue pa je med drugim tudi shop_id.
*** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 21256 page no 7 n bits 64 index pages_id_IDX of table `database_1`.`pages` trx id 95790615 lock_mode X locks rec but not gap waiting *** CONFLICTING WITH: RECORD LOCKS space id 21256 page no 7 n bits 64 index pages_id_IDX of table `database_1`.`pages` trx id 95790748 lock_mode X
In tu mi ni jasno, zakaj takšen update naredil LOCK na id_IDX index? Nampreč vrednost id-ja ta UPDATE ne spremeni, ampak spremeni zgolj vrednost date_time.
In sedaj iščem opcije, kako to uredit.
Tu pa me zanima, kako omejit UPDATE ukaz, da ne izvede LOCK na pages_id_IDX index, v primeru, da ta UPDATE izvede spremembo podatka samo v stolpcu date_time?
Razmišljal sem že o tem, da bi UPDATE nadomestil z DELETE + INSERT. A to je malo too much. Morajo obstajati druge, bolj elegantne rešitve.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
- spremenilo: HotBurek ()
darkolord ::
Če uporabljaš SELECT FOR UPDATE, potem zaklene vse vrstice, ki jih prebere, ne glede na to, ali jih update-aš ali ne.
Lahko transaction isolation level spremeniš na "READ COMMITED" (namesto REPEATABLE READ) in bo zaklenil samo tiste, ki jih update-aš.
Lahko transaction isolation level spremeniš na "READ COMMITED" (namesto REPEATABLE READ) in bo zaklenil samo tiste, ki jih update-aš.
kuall ::
razbij velik update na manjše, magar v cursorju.
uporabi try catch. v catch ujameš deadlock in poženeš query še 1x, če se deadlocki ponavljajo.
druga opcija je, da reorganiziraš sql kodo, da se ne bo izvrševala v takem nerodnem zaporedju, ki povzroči deadlock.
deadlock je to, ko 2 procesa čakata 1 na druzga neskončno dolgo. enega je treba ubiti, da se temu neskončnemu čakanju izognemo. to se lahko dogodi tudi v cestnem prometu, ko imaš 2 koloni avtov, oba hočeta zaviti levo in čakata, da se kolona sprosti, pa teoretično pol čakajo en na drugega neskončno dolgo. sm se enkrat fural na šiht in doživu tak cestni deadlock, kr smešno.
https://www.apress.com/us/blog/all-blog...
uporabi try catch. v catch ujameš deadlock in poženeš query še 1x, če se deadlocki ponavljajo.
druga opcija je, da reorganiziraš sql kodo, da se ne bo izvrševala v takem nerodnem zaporedju, ki povzroči deadlock.
deadlock je to, ko 2 procesa čakata 1 na druzga neskončno dolgo. enega je treba ubiti, da se temu neskončnemu čakanju izognemo. to se lahko dogodi tudi v cestnem prometu, ko imaš 2 koloni avtov, oba hočeta zaviti levo in čakata, da se kolona sprosti, pa teoretično pol čakajo en na drugega neskončno dolgo. sm se enkrat fural na šiht in doživu tak cestni deadlock, kr smešno.
https://www.apress.com/us/blog/all-blog...
Zgodovina sprememb…
- spremenilo: kuall ()
HotBurek ::
In točno za ta isolation level, READ COMMITTED, sem se odločil.
Iz dokumentacije:
READ COMMITTED
For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100).
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
Vir: https://mariadb.com/kb/en/set-transacti...
In jaz v tem primeru uporablaj unique index in hkratni unique search condition. Skratka, to bi morala bit ta prava rešitev.
Koda, ki jo trenutno uporabljam:
Če kdo uporablja kaj podobnega zgornjem primeru, bi me zanimalo, kjer nastavite TRANSACTION ISOLATION LEVEL; preden odprete transakcijo, ali znotraj transakcije?
Nekako logično je, da pred transakcijo. Me pa vseeno zanima, da naredim duble-check.
Iz dokumentacije:
READ COMMITTED
For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100).
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
Vir: https://mariadb.com/kb/en/set-transacti...
In jaz v tem primeru uporablaj unique index in hkratni unique search condition. Skratka, to bi morala bit ta prava rešitev.
Koda, ki jo trenutno uporabljam:
# set session isolation level to read committed SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; # start transaction read write START TRANSACTION READ WRITE; # now make update UPDATE `pages` SET `date_time` = @date_time WHERE `id` = @id; # commit COMMIT; # set session isolation level back to repeatable read SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Če kdo uporablja kaj podobnega zgornjem primeru, bi me zanimalo, kjer nastavite TRANSACTION ISOLATION LEVEL; preden odprete transakcijo, ali znotraj transakcije?
Nekako logično je, da pred transakcijo. Me pa vseeno zanima, da naredim duble-check.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
hbgqzR ::
Bravo, vroč burek, koncno postaja forum bolj zanimiv... Se je upanje!
Nivo izolacije transakcije nastavis pred zacetkom transakcije, normalno; izgleda lahko dolocis tudi na samem zacetku.
Nekatere baze dovoljujejo tudi gnezdenje transakcij, nekatere ne.
Nivo izolacije transakcije nastavis pred zacetkom transakcije, normalno; izgleda lahko dolocis tudi na samem zacetku.
Nekatere baze dovoljujejo tudi gnezdenje transakcij, nekatere ne.
Zgodovina sprememb…
- spremenilo: hbgqzR ()
Vredno ogleda ...
| Tema | Ogledi | Zadnje sporočilo | |
|---|---|---|---|
| Tema | Ogledi | Zadnje sporočilo | |
| » | Mariadb (InnoDB) istočasni insert v tabelo iz različnih procesov (strani: 1 2 )Oddelek: Programiranje | 7267 (3625) | 2g00d4u |
| » | [SQL] primary key inkrementalno dodajanje (strani: 1 2 )Oddelek: Programiranje | 5346 (4536) | ejresnevem |
| » | [C#] današnji datum in rojsni datumOddelek: Programiranje | 4684 (4171) | vojko20 |
| » | c# - mssql kombinacija / problem pri zapisu row-a v bazoOddelek: Programiranje | 1588 (1397) | darkolord |
| » | MSSQL row lockOddelek: Programiranje | 1564 (1452) | inferno666 |