Обсуждение: Dead Locks
I am receiveing dead locks like this:
SEVERE: SELECT * FROM ONGOING WHERE STATUS='FILA' ORDER BY TRUNK_ID, PRIORIDADE_TRONCO, ID;
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 508 waits for AccessShareLock on relation 2662 of database 16384; blocked by process 8504.
Process 8504 waits for AccessExclusiveLock on relation 2663 of database 16384; blocked by process 508.
What seems odd is that relations 2662 and 2663 are not user tables but postgres objects.
select oid, relname from pg_class where oid in (2662,2663)
2662;"pg_class_oid_index"
2663;"pg_class_relname_nsp_index"
Futhermore, this system don't use transactions (I mean it is autocommit) and we don't use triggers, rules or even foreign keys contraints.
Thanks for any help.
Valter
Valter Nogueira <vgnogueira@gmail.com> writes:
> I am receiveing dead locks like this:
> Jul 22, 2014 11:00:29 PM br.com.fastway.fastdialer.FastDialerDB query
> SEVERE: SELECT * FROM ONGOING WHERE STATUS='FILA' ORDER BY TRUNK_ID,
> PRIORIDADE_TRONCO, ID;
> org.postgresql.util.PSQLException: ERROR: deadlock detected
> Detail: Process 508 waits for AccessShareLock on relation 2662 of
> database 16384; blocked by process 8504.
> Process 8504 waits for AccessExclusiveLock on relation 2663 of database
> 16384; blocked by process 508.
> What seems odd is that relations 2662 and 2663 are not user tables but
> postgres objects.
Yeah, they're indexes of pg_class. What PG version is that? Are you
perhaps running maintenance operations that try to VACUUM FULL the system
catalogs? (If so, I'd counsel against it.)
regards, tom lane
[ please keep the list cc'd, and avoid top-posting ]
Valter Nogueira <vgnogueira@gmail.com> writes:
> I get the error in different server with different pg versions.
> In this server PG is:
> PostgreSQL 9.1.13 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro
> 4.6.3-1ubuntu5) 4.6.3, 32-bit
Well, that has the only fix I can think of that might be relevant (commit
1192ba8b6).
> Maybe I have autovaccum? I am not sure
AFAIK, autovacuum would never take an exclusive lock on an index; nor
would any plain DML operation. There must be some command you are issuing
that tries to grab exclusive lock on that index, and I'd bet it's
something along the line of VACUUM FULL, CLUSTER, or REINDEX applied to
pg_class.
You might try looking into the postmaster log, which I think will include
the text of all SQL commands involved in the deadlock report. If it
doesn't, try turning on log_statements so you can see what the other
process is running.
regards, tom lane