Query to check deadlock status of table in postgres
I want to check whether deadlock cleared or not in system tables of Postgres and ORACLE.
Kindly suggest me on how to check the deadlock status .
Answer
Deadlocks don't hang around, as soon as Postgres or Oracle finds a deadlock it will abort one of the transactions. This is because a deadlock, by its very nature, won't progress without intervention.
What you can do is look at what normal locks exist in the system and how long they've been there.
Locks that are held for a long time might indicate slow running transactions, or code that isn't committing at the correct place etc.
Long held locks also increase the likelihood that a deadlock will occur in the future.
Postgres
You can do this via the pg_locks view.
The Postgresql wiki has a page about looking at locks at https://wiki.postgresql.org/wiki/Lock_Monitoring
And the pg_locks view is described at https://www.postgresql.org/docs/current/static/view-pg-locks.html
Oracle
Oracle also provides views to show the current locks, but they're a bit more complex to use.
An intro is available at http://www.dba-oracle.com/t_grid_rac_db_locks_latches.htm
With reference at https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm#REFRN30121
In both cases though you'll need to spend a bit of time identifying what you're actually after as locks typically aren't held on the whole table at once, so its likely that different parts of the table will have locks on them at the same time by different transactions. Also its is not just tables that will show up in these views.
Similarly the different lock types (shared, exclusive, etc.) will block different types of concurrent activity.