NetBash.ru

Блокировки

PostgreSQL предоставляет различные режимы блокировки для управления одновременным доступом к данным в таблицах. Большинство команд PostgreSQL используют блокировки, чтобы гарантировать, что обрабатываемые таблицы не будут удалены или изменены во время выполнения команды

---

Блокировки могут быть:
исключительными (exclusive) — одновременно могут удерживаться только одной транзакцией;
разделяемыми (shared) — могут удерживаться несколькими транзакциями.

---

Строки блокируются только для изменений, но не для чтения
Таблицы блокируются при изменении, удалении, запрещается чтение таблицы при ее перестроении или перемещении
Время жизни блокировок устанавливается вручную, снимаются автоматически при завершении транзакции
Многоверсионность позволяет обойтись минимумом блокировок увеличивая производительность системы
Основные блокировки устанавливаются на уровне строк без блокировки читающи или записывающих транзакций
Строка будет заблокирована в том случае, если она уже изменена другой незафиксированной транзакцией
Блокировка может происходить на более высоком уровне, например на таблицах, чтобы нельзя было удалить таблицу пока из нее читают данные
Список активных блокировок можно получить в представлении pg_locks
Приложения могут самостоятельно управлять режимом бокировки, когда MVCC не дает желаемый результат

---

ACCESS SHARE (AccessShareLock) - блокировку в этом режиме получает любой запрос который только читает таблицу. Конфликтует только с режимом блокировки ACCESS EXCLUSIVE.
ROW SHARE (RowShareLock) - используется для команд SELECT FOR UPDATE и SELECT FOR SHARE. Конфликтует с режимами блокировки EXCLUSIVE и ACCESS EXCLUSIVE.
ROW EXCLUSIVE (RowExclusiveLock) - для команд которые вносят изменения в таблицу, UPDATE, DELETE и INSERT. Конфликтует с режимами блокировки SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE и ACCESS EXCLUSIVE
SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) - для команд VACUUM (без FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY и некоторыми видами ALTER INDEX и ALTER TABLE. Конфликтует с режимами блокировки SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE и ACCESS EXCLUSIVE.
И т.д.
Полный список блокировок с описанием можно увидеть на странице официальной документации: https://postgrespro.ru/docs/postgresql/13/explicit-locking
Блокировка обычно сохраняется до конца транзакции
Помимо блокировок на уровне таблиц, существуют блокировки на уровне строк
Блокировки на уровне строк блокируют только запись в определенные строки, но никак не влияют на выборку
Как и в случае блокировок на уровне таблиц, на уровне строк существуют свои виды блокировок
Например режимы FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE и т.д.
С полным списком и определением можно ознакомиться на странице официальной документации: https://postgrespro.ru/docs/postgresql/13/explicit-locking

---

Существуют также блокировки страниц
Частое применение явных блокировок может увеличить вероятность взаимоблокировок, то есть ситуаций, когда две (или более) транзакций держат блокировки так, что взаимно блокируют друг друга. PostgreSQL выявляет эту ситуацию и прерывает одну из транзакций
Если взаимоблокировка не будет выявлена, ожидающая блокировки на уровне таблицы или строки будет ждать неограниченное время. Приложения не должны оставлять транзакции открытыми долгое время, например ожидая ввода пользователя

---

Рекомендательные блокировки определяют сами приложения, система не форсирует их использование
Рекомендательные блокировки могут быть полезны для реализации режимов блокирования, которые плохо вписываются в модель MVCC
Рекомендательные блокировки можно использовать на уровне сеанса или на уровне транзакции
Рекомендательные блокировки на уровне сеанса требуют явного освобождения
И рекомендательные и обычные блокировки сохраняются в области общей памяти, размер которой определяется параметрами конфигурации max_locks_per_transaction и max_connections. Если этой памяти будет недостаточно, сервер не сможет выдать блокировку

Важно учитывать, что получаемые блокировки зависят от порядка вычислений sql-выражений. В некоторых случаях блокировки могут оказаться в подвешенном состоянии и будут отображаться в pg_locks
Источник: https://postgrespro.ru/docs/postgresql/13/explicit-locking

--Первая транзакция testbase=# BEGIN; BEGIN testbase=*# UPDATE t SET s = 'Третья версия'; UPDATE 1 testbase=*# testbase=# --Вторая транзакция testbase=# BEGIN ; BEGIN testbase=*# testbase=*# UPDATE t SET s = 'Четвертая версия'; Вторая транзакция «повисла»: она не может изменить строку, пока первая транзакция не снимет блокировку. --Фиксируем изменения в первой транзакции commit; --После фиксации первой ( снятие блокировки ), вторая транзакция завершит изменения UPDATE 1 COMMIT;

Две транзакции не могут одновременно удерживать блокировки конфликтующих типов для одной и той же таблицы. При этом разные транзакции могут одновременно владеть блокировками не конфликтующих режимов. Некоторые режимы блокировки конфликтуют сами с собой.
После получения блокировка обычно удерживается до конца транзакции. Но если блокировка получена после установки точки сохранения, она немедленно снимается, если происходит откат до точки сохранения.

---

Источник: https://docs.arenadata.io/ru/ADPG/current/concept/concurrency-control/locks.html

---

Команда LOCK

Команда LOCK устанавливает блокировку на уровне таблицы.
Можно использовать команду LOCK в тех случаях, когда нужна более строгая блокировка. Если режим блокировки в команде не указан, используется ACCESS EXCLUSIVE — наиболее ограничительный режим.

#Пример блокировки таблицы в терминале 1 LOCK TABLE account ; ERROR: LOCK TABLE can only be used in transaction blocks BEGIN; BEGIN megabase=*# megabase=*# LOCK TABLE account ; LOCK TABLE

В пример выше, пока транзакция не закрыта, таблицу нельзя будет использовать в других транзакциях, даже для чтения
LOCK при необходимости ожидает снятия любых конфликтующих блокировок. Вы можете изменить это поведение, используя опцию NOWAIT

Просмотр блокировок

Для просмотра текущих блокировок на сервере базы данных, используется системное представление pg_locks
Блокировки удобно просматривать для определенной сессии, для этого получите ID обслуживающего процесса сессии, используя функцию pg_backend_pid()

megabase=# SELECT pg_backend_pid(); pg_backend_pid ---------------- 21117 (1 row) #Список блокировок для сессии, в которой была выполнена транзакция megaplan=# megaplan=# SELECT locktype, relation::regclass, transactionid AS xid, mode, granted FROM pg_locks WHERE pid = 21117; locktype | relation | xid | mode | granted ---------------+----------+--------+---------------------+--------- virtualxid | | | ExclusiveLock | t relation | account | | AccessExclusiveLock | t transactionid | | 535372 | ExclusiveLock | t (3 rows) megaplan=#

locktype — тип блокируемого объекта
relation — имя отношения, получаемое через приведение OID из поля relation к типу regclass
mode — режим блокировки
xid — ID транзакции
granted — имеет значение t, если блокировка удерживается, и f, если блокировка ожидает

---

Откроем вторую сессию и попробуем сделать выборку этой же таблицы:

#Получим id сессии где будет выпоняться выборка megabase=# SELECT pg_backend_pid(); pg_backend_pid ---------------- 5271 #Получим список блокировки для данной сессии megaplan=# SELECT locktype, relation::regclass, transactionid AS xid, mode, granted FROM pg_locks WHERE pid = 5271; locktype | relation | xid | mode | granted ------------+----------+-----+-----------------+--------- virtualxid | | | ExclusiveLock | t relation | account | | AccessShareLock | f (2 rows)

В полученном выше списке, видим, что для сессии 5271, блокировка находится в статусе ожидания ( granted = f )
Завершим первую транзакцию и увидим, что транзакция во второй сессия завершилась и в списке блокировок отсутствуют строки для первой и второй сессий

---

Источники