борьба с дедлоками

имеется табличка

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(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

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

Понедельник, 29 апреля 2013, 2:07 +04:00 от "Dmitry E. Oboukhov" <unera(at)debian(dot)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(at)debian(dot)org jabber: //UNera(at)uvw(dot)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(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

Думаю, что ORDER BY определяет только сортировку результатов, а оптимизатор
запросов может определить совсем другой порядок обработки строк.

Тут нужно пробовать блокировать таблицы целиком (
http://www.postgresql.org/docs/9.1/static/sql-lock.html)

BEGIN WORK;
LOCK TABLE objects IN EXCLUSIVE MODE;
...UPDATE
"objects"
SET
"status" = "..."
FROM
"list"
WHERE
"list"."uuid" = "objects"."uuid"
COMMIT WORK;

BEGIN WORK;
LOCK TABLE objects IN EXCLUSIVE MODE;
...UPDATE
"objects"
SET
"x" = "...", "y"="..."
FROM
"list"
WHERE
"list"."uuid" = "objects"."uuid"
COMMIT WORK;

В каждый момент времени только один процесс будет иметь возможность писать
в таблицу.

8 мая 2013 г., 2:34 пользователь Dmitry E. Oboukhov <unera(at)debian(dot)org>написал:

> > Сортировка поможет.
>
> приписал в обоих запросах ORDER BY "uuid". как сыпались дедлоки так и
> продолжают сыпаться.
> постгря - 9.1.2
>
> очень не хочется лочить таблицу вообще перед групповой записью.
>
> есть еще варианты какие-либо? подскажите
> --
>
> . ''`. Dmitry E. Oboukhov
> : :' : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)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)
>
> iEYEAREDAAYFAlGJgYMACgkQq4wAz/jiZTdE5wCgwXNqmM8xYOHfxhxT23ScdFDR
> 878AnRqCcuUy1ylSVDT6dfqJM5VAf8vz
> =gSe+
> -----END PGP SIGNATURE-----
>
>
--
С уважением
Муравьёв Сергей

8 мая 2013 г., 7:52 пользователь Сергей Муравьёв <smurav78(at)gmail(dot)com>написал:

> Думаю, что ORDER BY определяет только сортировку результатов, а
> оптимизатор
> запросов может определить совсем другой порядок обработки строк.
>
> Тут нужно пробовать блокировать таблицы целиком (
> http://www.postgresql.org/docs/9.1/static/sql-lock.html)
>
Или вспомнить про SELECT ... FOR UPDATE
http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-FOR-UPDATE-SHARE

--
// Dmitriy.

> Или вспомнить про 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(at)debian(dot)org jabber://UNera(at)uvw(dot)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(at)debian(dot)org jabber://UNera(at)uvw(dot)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(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

Думаю, первый вариант будет в разы медленнее.
Но лучше не гадать, а провести анализ запросов и замеры времени выполнения.

Тут есть информация и про анализ запросов, и про выгоду от блокировки таблиц
http://www.opennet.ru/base/dev/postgresql_tune.txt.html

8 мая 2013 г., 16:22 пользователь Dmitry E. Oboukhov <unera(at)debian(dot)org>написал:

> > Есть. - изменения должны затрагивать только одну запись. А прядок
> > выдачи запросов на изменения можно задать програмно в нужном
> > порядке. Правда об еффективности в этом случае можно забыть.
> > Может на проблему с другого угла посмотреть?
>
> Да другой угол - нормализация полей по двум таблицам, либо другой угол
> - блокировка таблиц - понятны. я пока (для себя по кр. мере) хочу
> выяснить вопрос можно ли тут что-то сделать :)
>
> кстати, насчет эффективности
>
> если выдать огромную секцию
>
> 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(at)debian(dot)org jabber://UNera(at)uvw(dot)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)
>
> iEYEAREDAAYFAlGKQ3gACgkQq4wAz/jiZTfINgCgnrZwwyxDiF5e/UAQxYjpj+yi
> v7IAoO1k5lA4VxvHDYNEEjWn3cYaXuwf
> =WkX3
> -----END PGP SIGNATURE-----
>
>
--
С уважением
Муравьёв Сергей

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

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

> --
>
> . ''`. Dmitry E. Oboukhov
> : :' : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)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(dot)ru(at)gmail(dot)com