SQL: Find and remove / delete all the locks / deadlocks / blockers in an Oracle or PostgreSQL database

If your executing program which updates records in the database hangs, a good chance is that the record it is trying to update is locked by another program.

To prevent concurrent modification of a record, Oracle locks a record when it is being modified. However, if a program, after it has locked some records for modification, hangs due to some reason, it will cause failure or hang of other programs that need to access the records. To find out the locked records in the database, you can get the sid, serial#, status, username, osuser, machine, terminal from the session view, and the owner, object_name and object_type from the dba_objects table, and use them in conjunction with the locked_object view.

Here is the SQL:

A. Oracle

SELECT a.sid, a.serial#, a.username, a.status, a.osuser, a.machine, a.terminal,
          b.owner, b.object_id, b.object_name, b.object_type,
          c. os_user_name
From v$session a, dba_objects b, v$locked_object c
WHERE a.sid = c.session_id and b.object_id = c.object_id;

Or use the one below to get what is holding a lock on an object for which another process is waiting.

SELECT sid, serial# From v$session where sid in (select HOLDING_SESSION from DBA_BLOCKERS);

Or find only the locks that have blocked another session for at least 30 seconds

select v1.sid, v1.serial# from v$session v1 where v1.sid in (select blocking_session from v$session v2 where v2.WAIT_TIME = 0 and v2.seconds_in_wait >= 30);

To remove a lock:
ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

(If not enough privilege, login as sysdba and execute the command below.

               SQL> grant alter system to userid;

                        Grant succeeded.
)

B. PostgreSQL

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t
WHERE l.relation=t.relid order by relation asc;

SELECT p.pid, p.usename, p.datname, l.relation::regclass,
l.granted, p.query, p.query_start
FROM pg_stat_activity AS p
JOIN pg_locks AS l ON l.pid = p.pid
WHERE l.relation IN (SELECT relation FROM pg_locks WHERE granted IS FALSE)
ORDER BY l.relation;

SELECT pid, (now() - query_start) AS duration, query, state
FROM pg_stat_activity
WHERE (now() - query_start) > interval '30 seconds';

To remove the lock:
select pg_terminate_backend(<blocking_pid>);


----------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.