Czy SQLite3 bezpiecznie obsługuje równoczesny dostęp przez wiele procesów odczytujących / zapisujących z tej samej bazy danych? Czy są od tego wyjątki dotyczące platform?

Jeśli większość tych jednoczesnych dostępów to odczyty (np. SELECT), SQLite może je bardzo dobrze obsłużyć. Ale jeśli zaczniesz pisać jednocześnie, rywalizacja o blokady może stać się problemem. Wiele wtedy zależałoby od szybkości twojego systemu plików, ponieważ sam silnik SQLite jest niezwykle szybki i ma wiele sprytnych optymalizacji, aby zminimalizować rywalizację. Szczególnie SQLite 3.

W przypadku większości aplikacji na komputery stacjonarne / laptopy / tablety / telefony SQLite jest wystarczająco szybki, ponieważ nie ma wystarczającej współbieżności. (Firefox intensywnie używa SQLite do zakładek, historii itp.)

W przypadku aplikacji serwerowych ktoś jakiś czas temu powiedział, że wszystko poniżej 100 000 odsłon dziennie może być doskonale obsłużone przez bazę danych SQLite w typowych sytuacjach (np. Blogi, fora), a ja nie widziałem jeszcze żadnych dowodów na to, że jest inaczej. W rzeczywistości przy nowoczesnych dyskach i procesorach 95% witryn i usług internetowych działałoby dobrze z SQLite.

Jeśli chcesz naprawdę szybkiego dostępu do odczytu / zapisu, użyj bazy danych SQLite w pamięci . Pamięć RAM jest o kilka rzędów wielkości szybsza niż dysk.

Tak, SQLite dobrze radzi sobie ze współbieżnością, ale nie jest najlepszy z punktu widzenia wydajności. Z tego, co wiem, nie ma od tego wyjątków. Szczegóły znajdują się na stronie SQLite: https://www.sqlite.org/lockingv3.html

To stwierdzenie jest interesujące: "Moduł pagera zapewnia, że ​​zmiany zachodzą od razu, że albo wszystkie zmiany zachodzą, albo żadna z nich, że dwa lub więcej procesów nie próbuje uzyskać dostępu do bazy danych w niekompatybilny sposób w tym samym czasie"

Tak. Dowiedzmy się, dlaczego

SQLite jest transakcyjny

Wszystkie zmiany w ramach pojedynczej transakcji w SQLite następują w całości lub wcale

Taka obsługa ACID, jak również współbieżne odczyty / zapisy są zapewniane na 2 sposoby - za pomocą tzw. Dziennikowania (nazwijmy to „ starym sposobem ”) lub rejestrowania z wyprzedzeniem (nazwijmy to „ nowym sposobem ”)

Dziennik (dawny sposób)

W tym trybie SQLite używa blokowania DATABASE-LEVEL . To jest kluczowy punkt do zrozumienia.

Oznacza to, że kiedykolwiek musi coś przeczytać / zapisać, najpierw uzyskuje blokadę na CAŁYM pliku bazy danych. Wielu czytelników może współistnieć i czytać coś równolegle

Podczas pisania upewnia się, że uzyskano blokadę na wyłączność i żaden inny proces nie odczytuje / nie pisze jednocześnie, a zatem zapisy są bezpieczne.

Dlatego tutaj mówią, że SQlite implementuje transakcje z możliwością serializacji

Kłopoty

Ponieważ za każdym razem musi blokować całą bazę danych i wszyscy czekają na proces obsługujący współbieżność zapisu, a takie współbieżne zapisy / odczyty mają dość niską wydajność

Wycofania / przestoje

Przed zapisaniem czegoś do pliku bazy danych SQLite najpierw zapisywałby fragment do zmiany w pliku tymczasowym. Jeśli coś ulegnie awarii w trakcie zapisywania do pliku bazy danych, pobierze ten plik tymczasowy i cofnie wprowadzone w nim zmiany

Rejestrowanie z wyprzedzeniem lub WAL (nowy sposób)

W tym przypadku wszystkie zapisy są dołączane do pliku tymczasowego ( dziennik zapisu z wyprzedzeniem ) i plik ten jest okresowo łączony z oryginalną bazą danych. Kiedy SQLite czegoś szuka, najpierw sprawdza ten plik tymczasowy i jeśli nic nie zostanie znalezione, kontynuuje pracę z głównym plikiem bazy danych.

W rezultacie czytelnicy nie konkurują z pisarzami, a wydajność jest znacznie lepsza w porównaniu z Old Way.

Ostrzeżenia

SQlite w dużym stopniu zależy od podstawowej funkcji blokowania systemu plików, więc należy go używać ostrożnie, więcej szczegółów tutaj

Prawdopodobnie napotkasz również błąd w bazie danych jest zablokowany , szczególnie w trybie kronikowania, więc Twoja aplikacja musi być zaprojektowana z uwzględnieniem tego błędu

Wygląda na to, że nikt nie wspomniał o trybie WAL (Write Ahead Log). Upewnij się, że transakcje są odpowiednio zorganizowane, a przy włączonym trybie WAL nie ma potrzeby blokowania bazy danych, podczas gdy ludzie czytają rzeczy podczas aktualizacji.

Jedynym problemem jest to, że w pewnym momencie WAL musi zostać ponownie włączony do głównej bazy danych i robi to po zamknięciu ostatniego połączenia z bazą danych. W przypadku bardzo obciążonej witryny może minąć kilka sekund, zanim wszystkie połączenia zostaną zamknięte, ale 100 000 odwiedzin dziennie nie powinno stanowić problemu.

W 2019 roku pojawiły się dwie nowe opcje jednoczesnego zapisu, które nie zostały jeszcze wydane, ale są dostępne w oddzielnych oddziałach.

„PRAGMA journal_mode = wal2”

Zaletą tego trybu dziennika nad zwykłym trybem „wal” jest to, że piszący mogą kontynuować zapisywanie do jednego pliku wal, podczas gdy drugi jest w punkcie kontrolnym.

BEGIN CONCURRENT - link do szczegółowego dokumentu

Ulepszenie BEGIN CONCURRENT umożliwia wielu programistom jednoczesne przetwarzanie transakcji zapisu, jeśli baza danych jest w trybie „wal” lub „wal2”, chociaż system nadal serializuje polecenia COMMIT.

Kiedy transakcja zapisu jest otwierana z "BEGIN CONCURRENT", w rzeczywistości blokowanie bazy danych jest odraczane do momentu wykonania polecenia COMMIT. Oznacza to, że dowolna liczba transakcji rozpoczętych za pomocą BEGIN CONCURRENT może przebiegać jednocześnie. System stosuje optymistyczne blokowanie na poziomie strony, aby zapobiec zatwierdzaniu sprzecznych współbieżnych transakcji.

Razem są obecne w begin-concurrent-wal2 lub każdy w oddzielnej własnej gałęzi .

SQLite ma blokadę czytelników i zapisów na poziomie bazy danych. Wiele połączeń (prawdopodobnie należących do różnych procesów) może odczytywać dane z tej samej bazy danych w tym samym czasie, ale tylko jedno może zapisywać w bazie danych.

SQLite obsługuje nieograniczoną liczbę jednoczesnych czytników, ale w danym momencie pozwala tylko jednemu pisarzowi. W wielu sytuacjach nie stanowi to problemu. Kolejka pisarzy w górę. Każda aplikacja działa szybko i działa w bazie danych, a żadna blokada nie trwa dłużej niż kilkadziesiąt milisekund. Istnieją jednak aplikacje, które wymagają większej współbieżności i mogą one wymagać szukania innego rozwiązania. - Odpowiednie zastosowania SQLite @ SQLite.org

Blokada czytnik-zapis umożliwia niezależne przetwarzanie transakcji i jest realizowana przy użyciu wyłącznych i współdzielonych blokad na poziomie bazy danych.

Blokada na wyłączność musi zostać uzyskana, zanim połączenie wykona operację zapisu w bazie danych. Po uzyskaniu blokady na wyłączność, operacje odczytu i zapisu z innych połączeń są blokowane do ponownego zwolnienia blokady.

Szczegóły implementacji w przypadku współbieżnych zapisów

SQLite ma tabelę blokad, która pomaga blokować bazę danych tak późno, jak to możliwe podczas operacji zapisu, aby zapewnić maksymalną współbieżność.

Stan początkowy to ODBLOKOWANY iw tym stanie połączenie nie uzyskało jeszcze dostępu do bazy danych. Gdy proces jest połączony z bazą danych i nawet transakcja została rozpoczęta z BEGIN, połączenie jest nadal w stanie ODBLOKOWANY.

Po stanie ODBLOKOWANE następnym stanem jest stan WSPÓLNY. Aby móc odczytać (nie zapisywać) danych z bazy danych, połączenie musi najpierw wejść w stan SHARED, uzyskując blokadę SHARED. Wiele połączeń może jednocześnie uzyskiwać i utrzymywać WSPÓLNE blokady, więc wiele połączeń może jednocześnie odczytywać dane z tej samej bazy danych. Ale tak długo, jak tylko jedna blokada SHARED pozostaje nie zwolniona, żadne połączenie nie może pomyślnie zakończyć zapisu do bazy danych.

Jeśli połączenie chce zapisywać do bazy danych, musi najpierw uzyskać blokadę ZAREZERWOWANA.

Jednocześnie może być aktywna tylko jedna ZAREZERWOWANA blokada, chociaż wiele WSPÓLNYCH blokad może współistnieć z jedną ZAREZERWOWANĄ blokadą. ZAREZERWOWANE różni się od OCZEKUJĄCEGO tym, że nowe WSPÓLNE blokady można nabyć, gdy istnieje blokada ZAREZERWOWANA. - Blokowanie plików i współbieżność w SQLite w wersji 3 @ SQLite.org

Gdy połączenie uzyska blokadę ZAREZERWOWANĄ, może rozpocząć przetwarzanie operacji modyfikacji bazy danych, chociaż te modyfikacje można wykonać tylko w buforze, a nie w rzeczywistości zapisać na dysku. Modyfikacje zawartości odczytu zapisywane są w buforze pamięci. Gdy połączenie chce złożyć modyfikację (lub transakcję), konieczne jest uaktualnienie blokady RESERVED do blokady EXCLUSIVE. Aby uzyskać zamek, musisz najpierw podnieść zamek do blokady PENDING.

Blokada PENDING oznacza, że ​​proces utrzymujący blokadę chce zapisywać do bazy danych tak szybko, jak to możliwe i po prostu czeka na wyczyszczenie wszystkich bieżących blokad SHARED, aby mógł uzyskać blokadę EXCLUSIVE. Żadne nowe blokady SHARED nie są dozwolone w bazie danych, jeśli blokada PENDING jest aktywna, chociaż istniejące blokady SHARED mogą być kontynuowane.

Do zapisu w pliku bazy danych potrzebna jest EKSKLUZYWNA blokada. Tylko jedna blokada EXCLUSIVE jest dozwolona dla pliku i żadne inne blokady jakiegokolwiek rodzaju nie mogą współistnieć z blokadą EXCLUSIVE. Aby zmaksymalizować współbieżność, SQLite działa tak, aby zminimalizować czas utrzymywania blokad EXCLUSIVE. - Blokowanie plików i współbieżność w SQLite w wersji 3 @ SQLite.org

Można więc powiedzieć, że SQLite bezpiecznie obsługuje równoczesny dostęp wielu procesów piszących do tej samej bazy danych tylko dlatego, że go nie obsługuje! Otrzymasz SQLITE_BUSYlub SQLITE_LOCKEDdla drugiego pisarza, gdy osiągnie limit ponownych prób.

Ten wątek jest stary, ale myślę, że dobrze byłoby udostępnić wyniki moich testów wykonanych na sqlite: uruchomiłem 2 wystąpienia programu w Pythonie (różne procesy ten sam program) wykonujących instrukcje SELECT i UPDATE polecenia sql w ramach transakcji z blokadą EXCLUSIVE i limitem czasu ustawionym na 10 sekund na zablokowanie, a wynik był frustrujący. Każda instancja zrobiła w pętli 10000 kroków:

• Połącz się z db z wyłączną blokadą
• wybierz w jednym wierszu, aby odczytać licznik
• zaktualizuj wiersz o nową wartość równą licznikowi zwiększoną o 1
• zamknąć połączenie z db

Nawet jeśli sqlite przyznał blokadę transakcji na wyłączność, całkowita liczba faktycznie wykonanych cykli nie była równa 20 000, ale była mniejsza (całkowita liczba iteracji na jednym liczniku liczona dla obu procesów). Program w Pythonie prawie nie zgłosił żadnego wyjątku (tylko raz podczas wyboru dla 20 wykonań). Wersja sqlite w momencie testu to 3.6.20, a Python v3.3 CentOS 6.5. Moim zdaniem lepiej jest znaleźć bardziej niezawodny produkt do tego rodzaju pracy lub ograniczyć zapisy do sqlite do jednego unikalnego procesu / wątku.