Thread: Re: [pgsql-ru-general] борьба с дедлоками

Сортировка поможет.


Понедельник, 29 апреля 2013, 2:07 +04:00 от "Dmitry E. Oboukhov" <unera@debian.org>:
>
имеется табличка

objects: uuid - status - x - y - ...

далее по двум путям в эту табличку приходят статусы объектов (один
путь), а по другому пути приходят координаты объектов (второй путь)

соответственно поскольку оба потока довольно большие, то оба процесса
обновляют (они и получают) табличку пакетами.

то есть формируется некий запрос вида

WITH "list" AS (
    SELECT
        "column1" AS "uuid",
        "column2" AS "status"
    FROM (
        VALUES
            ( 'uuid1', 'status1' ),
            ( 'uuid2', 'status2' ),
            ...
    ) t
)
UPDATE
    "objects"
SET
    "status" = "list"."status"
FROM
    "list"
WHERE
    "list"."uuid" = "objects"."uuid"

аналогичный запрос составляется и по приходящим координатам (x, y).

Далее. Периодически возникают дедлоки. и оно понятно почему:

один процесс идет и меняет стасусы объектам 1 - 2 - 3 - 4
а второй процесс меняет координаты объектам 4 - 3 - 2 - 1

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

вопрос: как избавиться от дедлока но сохранить пакетные запросы?

по идее если бы все процессы всегда обновляли бы записи строго в
одинаковом порядке (всегда 1-2-3-4) то, очевидно, дедлока бы не было.

но можно ли обеспечить это при групповом запросе?
поможет ли тут сортировка в [псевдо]таблице "list"?

--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email:  unera@debian.org jabber: //UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537
>

> Сортировка поможет.

приписал в обоих запросах ORDER BY "uuid". как сыпались дедлоки так и
продолжают сыпаться.
постгря - 9.1.2

очень не хочется лочить таблицу вообще перед групповой записью.

есть еще варианты какие-либо? подскажите
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

> Или вспомнить про SELECT ... FOR UPDATE
> http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-FOR-UPDATE-SHARE


но у него та же проблема будет

два оператора делают SELECT .. FOR UPDATE

первый выбирает записи по порядку 1 2 3 4
а второй по порядку 4 3 2 1

и на третьей записи дедлок

или я не прав?
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

У всех та же проблема.

Из проблемы есть только один выход, все таблицы во всех транзакциях
должны блокироваться в одном порядке.


08.05.2013 13:10, Dmitry E. Oboukhov пишет:
>
>> Или вспомнить про SELECT ... FOR UPDATE
>> http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-FOR-UPDATE-SHARE
>
> но у него та же проблема будет
>
> два оператора делают SELECT .. FOR UPDATE
>
> первый выбирает записи по порядку 1 2 3 4
> а второй по порядку 4 3 2 1
>
> и на третьей записи дедлок
>
> или я не прав?


--
With b/r Viacheslav N Tararin.

Abonent Logic Land    http://abonent.logicland.com.ua/
Logic Land ltd.            http://logicland.com.ua/

Uralska st., 8, Kamenets-Podilskiy,
Khmelnitskiy reg., 32300, Ukraine

Tel/fax: +38-03849-3-63-80

> У всех та же проблема.

> Из проблемы есть только один выход, все таблицы во всех транзакциях
> должны блокироваться в одном порядке.

про блокировку таблиц я понимаю.
мне интересно есть ли способ блокировать _записи_ в одной таблице в
одном порядке?
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

Есть. - изменения должны затрагивать только одну запись. А прядок выдачи
запросов на изменения можно задать програмно в нужном порядке. Правда об
еффективности в этом случае можно забыть.
Может на проблему с другого угла посмотреть?

08.05.2013 14:57, Dmitry E. Oboukhov пишет:
>> У всех та же проблема.
>> Из проблемы есть только один выход, все таблицы во всех транзакциях
>> должны блокироваться в одном порядке.
> про блокировку таблиц я понимаю.
> мне интересно есть ли способ блокировать _записи_ в одной таблице в
> одном порядке?


--
With b/r Viacheslav N Tararin.

Abonent Logic Land    http://abonent.logicland.com.ua/
Logic Land ltd.            http://logicland.com.ua/

Uralska st., 8, Kamenets-Podilskiy,
Khmelnitskiy reg., 32300, Ukraine

Tel/fax: +38-03849-3-63-80

> Есть. - изменения должны затрагивать только одну запись. А прядок
> выдачи запросов на изменения можно задать програмно в нужном
> порядке. Правда об еффективности в этом случае можно забыть.
> Может на проблему с другого угла посмотреть?

Да другой угол - нормализация полей по двум таблицам, либо другой угол
- блокировка таблиц - понятны. я пока (для себя по кр. мере) хочу
  выяснить вопрос можно ли тут что-то сделать :)

кстати, насчет эффективности

если выдать огромную секцию

WITH
    "u1" AS (UPDATE "table" SET .. WHERE id = 1 AND key = 'abc')
    "u2" AS (UPDATE "table" SET .. WHERE id = 2 AND key = 'abc')
    "u3" AS (UPDATE "table" SET .. WHERE id = 3 AND key = 'abc')
     ...
SELECT
    123
;

то насколько это будет менее эффективно нежели

WITH "list" AS (SELECT ... собрать весь лист id / value )
UPDATE
    "table"
SET
    ...
FROM
    "list"
WHERE
    "list"."id' = "table"."id"


первый вариант не будет дедлочиться, а вот насколько он будет менее
эффективен?
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

2013/5/8 Dmitry E. Oboukhov <unera@debian.org>:
>> Из проблемы есть только один выход, все таблицы во всех транзакциях
>> должны блокироваться в одном порядке.
>
> про блокировку таблиц я понимаю.
> мне интересно есть ли способ блокировать _записи_ в одной таблице в
> одном порядке?

Можно с помощью plpgsql плюс FOR SELECT ... ORDER ... LOOP UPDATE ... END LOOP.


> --
>
> . ''`.                               Dmitry E. Oboukhov
> : :'  :   email: unera@debian.org jabber://UNera@uvw.ru
> `. `~'              GPGKey: 1024D / F8E26537 2006-11-21
>   `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlGKPbIACgkQq4wAz/jiZTf1PgCg2aHS2Sb6XrSMevxg0RdL1mCK
> F5kAn2BI879WQFkENGXOnTmFr1kbfeth
> =ZPQY
> -----END PGP SIGNATURE-----
>



--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com