В основе данной публикации лежит интересная статья «Exploring Query Locks in Postgres». Статье уже 10 лет, но она не потеряла актуальности. От понимания того как работают блокировки зависит написание правильных запросов, которые способны выполняться параллельно и не мешать друг другу. Давайте рассмотрим наглядный пример.
Для начала создадим базу «песочницу» с тремя полями:
create database sandbox;
create table toys ( id serial not null, name character varying(36), usage integer not null default 0, constraint toys_pkey primary key (id) );
И запишем туда данные
insert into toys(name) values('car'),('digger'),('shovel');
Откроем два терминала, в каждом из них подключимся к только что созданной базе данных sandbox. Дадим им имена для того, чтобы пример был наглядным. Например Алиса и Боб. В консоли пишем команду sandbox=# \SET PROMPT1 '[Alice] %/%R%# '
Сначала приходит Алиса и осматривает какие есть игрушки:
[Alice] sandbox> begin;
BEGIN
[Alice] sandbox> select * from toys;
id | name | usage
----+--------+-------
1 | car | 0
2 | digger | 0
3 | shovel | 0
(3 rows)
Оператор begin начинает транзакцию явно и она она будет продолжаться до тех пор, пока мы не зафиксируем её, сделаем commit, или не откатим, сделаем rollback, т.е. вернем все изменения обратно до begin.
Теперь приходит Боб посмотреть на игрушки и видит то же самое что и Алиса:
[Bob] sandbox> begin;
BEGIN
[Bob] sandbox> select * from toys;
id | name | usage
----+--------+-------
1 | car | 0
2 | digger | 0
3 | shovel | 0
(3 rows)
Получается что параллельное выполнение двух операторов select не мешает работе каждого из них с теми же данными. Именно такого поведения мы ожидаем от надёжной и высокопроизводительной базы данных.
pg_lock
Транзакции Алисы и Боба всё ещё открыты. Чтобы посмотреть какие блокировки были установлены, откроем третий терминал и назовём его Ева
sandbox=# \SET PROMPT1 '[eve] %/%R%# '
select lock.locktype, lock.relation::regclass, lock.mode, lock.transactionid as tid, lock.virtualtransaction as vtid, lock.pid, lock.granted from pg_catalog.pg_locks lock
left join pg_catalog.pg_database db on db.oid = lock.database
where (db.datname = 'sandbox' or db.datname is null) and not lock.pid = pg_backend_pid()
order by lock.pid;
Представление pg_lock показывает активные блокировки во всех базах. Условие (db.datname = 'sandbox' or db.datname is null) оставляет только наши блокировки которые относятся к "песочнице", а условие not pid = pg_backend_pid() убирает записи текущей сессии. Для лучшего отображения данных в колонке relation было приведение типа к regclass.
Посмотрим на пятую строку:
Виртуальной транзакцией 1/282 на таблицу toys наложена блокировка AccessShareLock. Блокировка считается выданной (is granted). Пока всё отлично, Боб и Алиса рады, они оба видят игрушки, которые можно взять и им никто не мешает. Обе транзакции удерживают блокировку ExclusiveLock на своей виртуальной транзакции virtualxid.
И вот наступает момент когда Алиса решает взять машинку:
[Alice] sandbox=# UPDATE toys SET usage = usage+1 WHERE id = 1; UPDATE 1
И ничего страшного не случается. Посмотрим как выглядит таблица блокировок.
transactionid
В наше таблице данных toys на записи с машинкой теперь стоит блокировка RowExclusiveLock. Ещё появился идентификатор транзакции transactionid на котором стоит блокировка ExclusiveLock. Идентификатор transactionid появляется у каждой транзакции, которая собралась менять состояние базы данных.
MVCC
Но Боб все также видит прежние данные, потому что транзакция Алисы не зафиксирована. Ни он не мы не знаем, будет ли Алиса фиксировать свою транзакцию или откатит ее обратно. Поэтому Боб видит содержимое таблицы неизменным. PostgreSQL использует механизм управления конкурентным доступом с помощью многоверсионности MVCC (Multi Version Concurrency Control) чтобы каждый пользователь видел согласованное состояние базы.
Продолжение в следующей статье Какие бывают блокировки в PostgreSQL на простом примере. Часть 2.(Блокирующие запросы, pg_stats_activity, явные блокировки)