Пул соединений с БД и транзакции: с чем это кушают?

Question

[Карлиндоу Мэрлифи]

Это мой первый раз, когда я начал клепать сайт с использованием технологий node,express,pg.
И уже столкнулся с некоторыми проблемами.
На сервере 90% запросов это select (выбрать что-то и отобразить), остальные 10% это update,insert,delete запросы.
В случаях с select всё казалось легко, при старте сервера открывается одно постоянное соединение с БД, через которое всё и обрабатывается.
Но при написании функций, где надо использовать update,insert,delete запросы, я столкнулся с аномалиями (фантомное, грязное чтение и подобное). Для решения я открывал параллельные соединения с БД в таких функциях и закрывал их на финале. Использовал уровень изоляции serializable, как вдруг начали вылазить ошибки конкурентного доступа (Как она могла возникнуть, если по сути последняя транзакция должна ждать завершения предыдущей, если она ещё завершена!). И никак же не понять, как именно эта ошибка возникла...
И я убрал параллельные соединения и сделал транзакции через основное соединение с БД (то, которое при запуске открывается и больше не закрывается), и это заработало, но теперь так как соединение одно, каждый юзер вынужден ждать завершения транзакций всех остальных юзеров.

На форумах пишут "нужно грамотно управлять пулами соединений", но что это значит? Где про это прочитать?

Кто подобное реализовывал? Правильно ли я вопросы ставлю, в ту ли сторону вообще копаю?

Comments

[Slava Rozhnev]

Покажите пример вашего кода

[Карлиндоу Мэрлифи]

Slava Rozhnev,
Вот допустим подключение к БД при старте

import pg from 'pg'
export const pool = new pg.Pool({
    //host port db user
    max: 1000, // максимальное количество соединений в пуле
})
// затем идёт pool.connect

А в сами запросы идут от pool.query(sql,[arr])

А в функциях(роутерах, если открывать новые соединения, то так)

const client = await pool.connect();
// транзакция в блоке try,catch BEGIN ISOLATION LEVEL SERIALIZABLE
в finally освобождаю соединение client.release();

[Дмитрий]

мнээээ. вроде как грязное чтение в PG не доступно ни на одном уровне изоляции. Каким образом вы его получили?

Answer 1

[calculator212]

фантомное, грязное чтение и подобное

Для решения я открывал параллельные соединения с БД

Это не связанные друг с другом вещи. То что вы описали возникает при транзакциях, я знаю два способа пофиксить это. Первый - использовать FOR UPDATE/LOCK TABLE, в запросах, который будет ждать пока выполняется другая транзакция. Второй использовать serializable и политику ретраев.

Использовал уровень изоляции serializable

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

На форумах пишут "нужно грамотно управлять пулами соединений", но что это значит? Где про это прочитать?

Лучше там спросить, что они имеют ввиду.

И я убрал параллельные соединения и сделал транзакции через основное соединение с БД (то, которое при запуске открывается и больше не закрывается), и это заработало, но теперь так как соединение одно, каждый юзер вынужден ждать завершения транзакций всех остальных юзеров.

Как бы пользователь в любом случае должен ждать пока другие транзакции завершатся, пул соединений полезен тем, что другие запросы не связанные с этой таблицей могу параллельно выполняться.
Если вы используете транзакции, по хорошему это нужно нормально тестировать, в тесте быстро запускать 100-150 транзакций, которы едолжны выполниться без ошибок и при этом вы должны получить желаемый результат в БД после их выполнения.

Comments

[Карлиндоу Мэрлифи]

спасибо за ответ, проверю сегодня

Answer 2

[Карлиндоу Мэрлифи]

Фига се 6 человек подписалось на вопрос.
Для себя я этот вопрос решил.
И вот как:
Сам пул (pool.query) я использую для всех юзеров, если запрошен какой-то роутер, который просто выводит информацию (исключительно select запросы)
А для остальных роутеров(путей), где надо совершать какие-то действия со строками в БД (update,delete,insert) я внутри обработчика всё заворачиваю в try>catch>finaly
в try открываю новое соединение client = await pool.connect();
ставлю изоляцию serializable делаю все запросы, потом commit или rollback в зависимости от условий.
в finaly высвобождаю соединение
if (client) {client.release();}
И всё работает отлично, важно, чтобы внутри самого блока try, где есть транзакция не делать больше одного запроса update,insert к одним и тем же полям.

Ещё важно, если вы используете самую строгую изоляцию, во всех других путях тоже важно использовать такой же уровень изоляции.

Вот и всё