Início / dba / Perguntas / 136355
Accepted
Fake Name
Asked: 2016-04-24 23:58:07 +0800 CST

`ON CONFLICT DO UPDATE` causando impasses?

  • 772

Eu tenho um projeto onde estou tentando usar a ON CONFLICT DO UPDATEcláusula PostgreSQL e estou tendo um grande número de problemas de bloqueio.

Meu esquema é o seguinte:

webarchive=# \d web_pages
                                               Table "public.web_pages"
      Column       |            Type             |                              Modifiers
-------------------+-----------------------------+---------------------------------------------------------------------
 id                | integer                     | not null default nextval('web_pages_id_seq'::regclass)
 state             | dlstate_enum                | not null
 errno             | integer                     |
 url               | text                        | not null
 starturl          | text                        | not null
 netloc            | text                        | not null
 file              | integer                     |
 priority          | integer                     | not null
 distance          | integer                     | not null
 is_text           | boolean                     |
 limit_netloc      | boolean                     |
 title             | citext                      |
 mimetype          | text                        |
 type              | itemtype_enum               |
 content           | text                        |
 fetchtime         | timestamp without time zone |
 addtime           | timestamp without time zone |
 tsv_content       | tsvector                    |
 normal_fetch_mode | boolean                     | default true
 ignoreuntiltime   | timestamp without time zone | not null default '1970-01-01 00:00:00'::timestamp without time zone
Indexes:
    "web_pages_pkey" PRIMARY KEY, btree (id)
    "ix_web_pages_url" UNIQUE, btree (url)
    "idx_web_pages_title" gin (to_tsvector('english'::regconfig, title::text))
    "ix_web_pages_distance" btree (distance)
    "ix_web_pages_distance_filtered" btree (priority) WHERE state = 'new'::dlstate_enum AND distance < 1000000 AND normal_fetch_mode = true
    "ix_web_pages_id" btree (id)
    "ix_web_pages_netloc" btree (netloc)
    "ix_web_pages_priority" btree (priority)
    "ix_web_pages_state" btree (state)
    "ix_web_pages_url_ops" btree (url text_pattern_ops)
    "web_pages_state_netloc_idx" btree (state, netloc)
Foreign-key constraints:
    "web_pages_file_fkey" FOREIGN KEY (file) REFERENCES web_files(id)
Triggers:
    update_row_count_trigger BEFORE INSERT OR UPDATE ON web_pages FOR EACH ROW EXECUTE PROCEDURE web_pages_content_update_func()

Meu comando de atualização é o seguinte:

INSERT INTO
    web_pages
    (url, starturl, netloc, distance, is_text, priority, type, fetchtime, state)
VALUES
    (:url, :starturl, :netloc, :distance, :is_text, :priority, :type, :fetchtime, :state)
ON CONFLICT (url) DO
    UPDATE
        SET
            state     = EXCLUDED.state,
            starturl  = EXCLUDED.starturl,
            netloc    = EXCLUDED.netloc,
            is_text   = EXCLUDED.is_text,
            distance  = EXCLUDED.distance,
            priority  = EXCLUDED.priority,
            fetchtime = EXCLUDED.fetchtime
        WHERE
            web_pages.fetchtime < :threshtime
        AND
            web_pages.url = EXCLUDED.url
    ;

(Observação: os parâmetros são escapados por meio do SQLAlchemyestilo de consulta parametrizada)

Estou vendo dezenas de erros de deadlock, mesmo sob simultaneidade relativamente leve (6 trabalhadores):

Main.SiteArchiver.Process-5.MainThread - WARNING - SQLAlchemy OperationalError - Retrying.
Traceback (most recent call last):
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
psycopg2.extensions.TransactionRollbackError: deadlock detected
DETAIL:  Process 11391 waits for ShareLock on transaction 40632808; blocked by process 11389.
Process 11389 waits for ShareLock on transaction 40632662; blocked by process 11391.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (743427,2) in relation "web_pages"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/media/Storage/Scripts/ReadableWebProxy/WebMirror/Engine.py", line 558, in upsertResponseLinks
    self.db_sess.execute(cmd, params=new)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1034, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.extensions.TransactionRollbackError) deadlock detected
DETAIL:  Process 11391 waits for ShareLock on transaction 40632808; blocked by process 11389.
Process 11389 waits for ShareLock on transaction 40632662; blocked by process 11391.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (743427,2) in relation "web_pages"
 [SQL: '         INSERT INTO          web_pages          (url, starturl, netloc, distance, is_text, priority, type, fetchtime, state)         VALUES          (%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s, %(priority)s, %(type)s, %(fetchtime)s, %(state)s)         ON CONFLICT (url) DO          UPDATE           SET            state     = EXCLUDED.state,            starturl  = EXCLUDED.starturl,            netloc    = EXCLUDED.netloc,            is_text   = EXCLUDED.is_text,            distance  = EXCLUDED.distance,            priority  = EXCLUDED.priority,            fetchtime = EXCLUDED.fetchtime           WHERE            web_pages.fetchtime < %(threshtime)s          ;         '] [parameters: {'url': 'xxxxxx', 'is_text': True, 'netloc': 'xxxxxx', 'distance': 1000000, 'priority': 10000, 'threshtime': datetime.datetime(2016, 4, 24, 0, 38, 10, 778866), 'state': 'new', 'starturl': 'xxxxxxx', 'type': 'unknown', 'fetchtime': datetime.datetime(2016, 4, 24, 0, 38, 10, 778934)}]

Meu nível de isolamento de transação é REPEATABLE READ, então meu entendimento de como o banco de dados deve funcionar é que eu veria muitos erros de serialização, mas impasses não deveriam ocorrer porque se duas transações alterassem a mesma linha, a transação posterior simplesmente falharia.

Meu palpite aqui é que o UPDATE está de alguma forma bloqueando a consulta INSERT (ou algo assim), e preciso colocar um ponto de sincronização (?) Em algum lugar, mas não entendo o escopo dos vários componentes da consulta bem o suficiente para faça qualquer solução de problemas além de apenas mudar as coisas aleatoriamente e ver o efeito que isso tem. Eu fiz algumas leituras, mas a documentação do PostgreSQL é extremamente abstrata e a ON CONFLICT xxxterminologia ainda não parece ser amplamente usada, portanto, não há muitos recursos para solução de problemas práticos, principalmente para não especialistas em SQL.

Como posso tentar resolver esse problema? Também experimentei outros níveis de isolamento ( READ COMMITTED, SERIALIZABLE) sem sucesso.

deadlock locking

1 respostas

  1. Best Answer

    Os impasses não são causados ​​por uma instrução específica. É causado por problemas de simultaneidade. Então, basicamente, você deve começar a observar como uma sessão de seu aplicativo lida com outras sessões trabalhando simultaneamente.

    Aqui estão as diretrizes gerais para evitar impasses:

    1. Sempre mantenha as chaves primárias nas tabelas. Essa chave primária deve ser o meio para identificar um determinado registro na tabela. Isso evitará que muitas linhas fiquem dentro do escopo do bloqueio.

    2. Mantenha uma ordem em todas as transações. Por exemplo, uma das lógicas de seu aplicativo insere/atualiza dados na tabela A e depois na tabela B. Não deve haver outra lógica que insere/atualiza dados na Tabela B e depois na Tabela A.

    3. Monitore e pegue os culpados. O PostgreSQL fornece pg_stat_activty e pg_stat_statements como visualizações para monitorar sessões e consultas. Aqui estão alguns exemplos de consultas com as quais você pode monitorar os bloqueios / impasses. https://wiki.postgresql.org/wiki/Lock_Monitoring Você pode precisar ajustar os parâmetros log_lock_waits e deadlock_timeout.

    4. Adquira o bloqueio mais restritivo primeiro na transação. para que os menores não apareçam no caminho.

    5. Por último, mas menos importante, reduza o tamanho da transação. cometer mais vezes. transações de execução longa têm mais chances de acabar em impasses. Além disso, as transações longas no postgres contêm mais número de tuplas ativas devido à maneira como o MVCC é implementado no Postgres.

    • 5

relate perguntas