Multiversion Concurrency Control MVCC — PostgreSQL vs MySQL InnoDB
TLTR, w tym artykule znajdziesz porównanie implementacji MVCC na przykładzie dwóch RDBMS PostgreSQL oraz MySQL. Warto dodać, że MySQL/MariaDB oferują wiele silników bazy danych, my skupimy się na tym najpopularniejszym jakim jest InnoDB.
Trochę teorii
Czym jest MVCC ? W dużym skrócie MVCC pozwala na wykonywanie wielu operacji w jednym czasie i zapewnia spójność danych/wyników jakie otrzymujemy. Tutaj musimy użyć troche wyobraźni, jeśli mamy dwie sesje to wyobraź sobie, że każda z nich pracuje na swoim własnym obrazie danych. Widocznym tylko dla tej konkretnej sesji. Teraz troche mniej wyobraźni, a więcej przykładów.
Dla ciekawych świata — lista baz danych implementujących MVCC.
PostgreSQL
Nasza tabela:
id | produkt | date
----+---------+------------
1 | | Krótkie wprowadzenie, tabela która widzisz powyżej ma więcej niż trzy kolumny, PostgreSQL ma również kilka ukrytych kolumn, my będziemy uzywali xmin i xmax, opis wszystkich znajdziesz tutaj.
Mamy dwie równolegle sesje, które chca zmienić ten sam rekord.
Sesja NR 1.
postgres=# start transaction ;
START TRANSACTION
postgres=*# select *,xmin,xmax from test where id=1;
id | produkt | date | xmin | xmax
----+---------+------+------+------
1 | | | 860 | 0
(1 row)
postgres=*# update test set produkt='czajnik' where id=1;
UPDATE 1
postgres=*# select *,xmin,xmax from test where id=1;
id | produkt | date | xmin | xmax
----+---------+------+------+------
1 | czajnik | | 864 | 0
(1 row)
postgres=*#Zwróć uwagę, że transakcja nie została zakończona, ciągle sesja NR 1 widzi swój obraz danych.
Sesja NR 2.
W tym samym czasie:
postgres=# start transaction ;
START TRANSACTION
postgres=*# select *,xmin,xmax from test where id=1;
id | produkt | date | xmin | xmax
----+---------+------+------+------
1 | | | 860 | 0
(1 row)
postgres=*# select *,xmin,xmax from test where id=1;
id | produkt | date | xmin | xmax
----+---------+------+------+------
1 | | | 860 | 864
(1 row)Sesja NR 2 ciagle widzi wartość ID=1. Jak dzieję się ta magia ?
To jest właśnie MVCC, czyli każda sesja pracuje na własnym obrazie ( czasem mówi się o snapshocie danych). PostgreSQL, żeby rozpoznać jakie dane powinien pokazać używa kolumn xmin i xmax. Popatrzmy na nie, w transakcji NR 1 mamy xmin 860 oraz xmax 0, w NR 2 xmin 860 i xmax 864. Dzięku temu PG wie, żę ktoś dokonały zmian ale ich nie pokazuje. Dodam, żę to czy te zmiany są widoczne w sesji czy nie zależy od konfiguracji zmiennej Transaction Isolation Level. To temat na tyle obszerny, że zostawie go na oddzielny blog.
Żeby nasze zmiany zostały zapisane na stałe w sesji NR 1 wykonujemy polecenie COMMIT.
postgres=*# commit;
COMMIT
postgres=#Co widzi sesja NR 2 ?
postgres=*# select *,xmin,xmax from test where id=1;
id | produkt | date | xmin | xmax
----+---------+------+------+------
1 | czajnik | | 864 | 0
(1 row)Jak widzicie wartość xmin zmieniła się 864 jest to wartość transaction ID która wykonała zmiane.
postgres=# SELECT txid_current();
txid_current
--------------
864
(1 row)
Bardzo podbna sytuacja zachodzi w momencie użycia DELETE/INSERT tylko tym razem zmiają się wartości w kolumnach cmax i cmin.
Ta sama sytuacja co poprzednio dwie sesje NR 1 i NR2.
postgres=# start transaction;
START TRANSACTION
postgres=*# select *,cmin,cmax from test where id=1;
id | produkt | date | cmin | cmax
----+---------+------+------+------
1 | czajnik | | 0 | 0
(1 row)
postgres=*# select *,cmin,cmax from test where id=1;
id | produkt | date | cmin | cmax
----+---------+------+------+------
1 | czajnik | | 0 | 0
1 | widelec | | 0 | 0
(2 rows)
Co widzi sesja NR2 ?
postgres=*# delete from test where id=1;
DELETE 2
postgres=*# select *,cmin,cmax from test where id=1;
id | produkt | date | cmin | cmax
----+---------+------+------+------
(0 rows)
Wracamy do sesji NR1
postgres=*# select *,cmin,cmax from test where id=1;
id | produkt | date | cmin | cmax
----+---------+------+------+------
1 | czajnik | | 1 | 1
1 | widelec | | 0 | 0
(2 rows)Jak widzicie wartośći cmin i cmax zmieniły się na 1.
Skoro juz jesteśmy przy Transaction ID to mamy trzy ciekawe polecenia:
postgres=*# SELECT txid_current();
txid_current
--------------
866
(1 row)
postgres=*# SELECT txid_current_snapshot();
txid_current_snapshot
-----------------------
866:866:
(1 row)
postgres=# SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
backend_xmin
--------------
866
(1 row)
Pierwsze pokazuje nam ID transakcji w której jesteśmy, drugie snapshot na którym działamy, a trzecie daje nam tą samą informacje co polecenie pierwsze tylko, że wyciagamy ID z pg_stat_activity.
Wszystko pieknie ładnie, jakie są więc wady MVCC ?
- Transaction ID Exhaustion ( Wraparound) pisałem już kiedyś o tym, dla ciekawskich polecam sprawdzić tutaj.
- Każdy UPDATE musi stworzyć nowy rekord, a DELETE nie kasuje fizycznie tego rekordu jedynie oznacza do jako niewidoczny. Tak, właśnie dlatego potrzebujemy VACUUM.
- Większe zużycie pamięci przy dużej ilości jednoczesnych transakcji, każda sesja widzi swój snapshot.
- Maksymalna ilość TX ID wynosi 2³².
MySQL INNODB
Tak naprawdę to skupimy się na InnoDB, niestety muszę wasz zawieść, nie da się go pokazać w tak spektakularny sposób. Tutaj tez mamy uktryte kolumny, ale są one nie widoczne dla użytkownika.
- 6-byte DB_TRX_ID field indicates the transaction identifier for the
last transaction that inserted or updated the row. Also, a deletion
is treated internally as an update where a special bit in the row is set
to mark it as deleted.
- 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points
to an undo log record written to the rollback segment. If the row was updated,
the undo log record contains the information necessary to rebuild the
content of the row before it was updated.
- 6-byte DB_ROW_ID field contains a row ID that increases monotonically as
new rows are inserted. If InnoDB generates a clustered index automatically,
the index contains row ID values. Otherwise, the DB_ROW_ID column does not
appear in any index.
Kolejną znaczącą róznicą jest poziom izolacji, w InnoDB jest to
mysql> SELECT @@transaction_ISOLATION;
+-------------------------+
| @@transaction_ISOLATION |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
mysql>w PostgreSQL jest to
postgres=# SHOW default_transaction_isolation;
default_transaction_isolation
-------------------------------
read committed
(1 row)
postgres=#Więcej o poziomach izolacji w InnoDB znajdziesz tutaj.
Czas na test, kolejność komend:
- Sesja NR 1.
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (1, 'czajnik','2024-05-06');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+------+---------+------------+
| id | produkt | date |
+------+---------+------------+
| 1 | czajnik | 2024-05-06 |
+------+---------+------------+
1 row in set (0.00 sec)2. Sesja NR 2.
mysql> select * from test;
Empty set (0.00 sec)3. Sesja NR 1.
mysql> commit;
Query OK, 0 rows affected (0.01 sec)4. Sesja NR 2.
mysql> select * from test;
+------+---------+------------+
| id | produkt | date |
+------+---------+------------+
| 1 | czajnik | 2024-05-06 |
+------+---------+------------+
1 row in set (0.00 sec)Implementacja MVCC w InnoDB używa kombinacji rows ID i Undo log. Kiedy transakcja modyfikuje wartości w tabeli, InnoDB nie zmienia istniejacych rekordów, tworzy nową wersję rekordu z nowymi wartościami. Mamy taki moment w który w jednej tabeli są dwa te same rekordy tylko z innymi wartościami. Każda transakcja ma dostęp do swojej wersji i mogą to robić równolegle. W Undo Logs zachowane są stare wartości jeszcze przed modyfikacją. W momencie wykonania COMMIT stare rekordy zostaną usunięte a ich miejsce zajmują nowe, które są widoczne dla wszystkich. W przypadku ROLLBACK watości z Undo Logs zostają użyte to odzyskania oryginalnych wartości.
Jak wspomniałem wcześniej InnoDB używa transaction ID jako indetyfikatora oraz timestamps. Każda transakcja ma swój unikalny ID, a każdy rekord przypisany timestamp z wartościa kiedy został on stworzony. Na podstawie timestamp InnoDB decyduje które rekordy są dostępne dla jakiej transakcji.
Tak jak wspomniałem DB_TRX_ID nie jest widoczny dla każdego rekordu, jedynym miejscem w którym można go zobaczyc jest InnoDB engine status
mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 2326
trx_state: RUNNING
trx_started: 2024-05-06 11:42:56
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 10
trx_query: select * from information_schema.INNODB_TRX
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1128
trx_rows_locked: 0
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: NULL
1 row in set (0.00 sec)Gratulacje, że dostałeś aż do tego miejsca!
Niech SELECT będzie z tobą.
Tomasz Gintowt jest Architektem/DevOps/DBA/Trenerem, głównie skupiony na dostarczaniu rozwiązań składowania i przetwarzania danych. Nie są mu obce wszelkiej maści bazy danych, systemy real-time data i streamingu. Obecnie pracuje z Apache Kafka, RabbitMQ, Elastic Stack i PostgreSQL. Organizator spotkań DataOps Poland.
https://www.linkedin.com/in/tomasz-gintowt/
https://dataops-academy.pl — kursy i szkolenia.
