Trying to find a way to detect and release locks to avoid stuck queries in Amazon Redshift? Relax, The Support Engineers at Bobcares have a solution just for you.

Are the table locks blocking your queries in Amazon Redshift? Read on to find out how our Support Team tracks down the stuck queries and resolves them.

How to detect and release locks to avoid stuck queries in Amazon Redshift

Locking controls the number of sessions that can access a table simultaneously. It is a protection mechanism. Furthermore, it also determines the operations performed in these sessions.

While most relations databases rely on row-level locks, Amazon Redshift relies on table-level locks. This is why you may come across locking conflicts while performing DDL statements or DML queries on user tables.

Let’s take a look at Amazon Redshift’s three lock modes:

• AccessExclusiveLock: This lock is acquired mostly during DDL operations like DROP, TRUNCATE or ALTER TABLE. Furthermore, it blocks other locking attempts.
• AccessShareLock: This lock is acquired during SELECT, UNLOAD, DELETE or UPDATE operations. It blocks only AccessExclusiveLock attempts.
• ShareRowExclusiveLock: This lock is acquired during INSERT, COPY, DELETE or UPDATE operations. Moreover, it blocks only AccessExclusiveLock.

Once a transaction or a query locks a table, the lock stays in place for the duration of the transaction or query. Any other transaction or query in wait for the table will be blocked during this duration.

The solution: detect and release locks to avoid stuck queries in Amazon Redshift

According to our Support Engineers, you need to identify the session, PID responsible for the lock. Then you have to terminate that specific session to solve locking problems. In case this doesn’t work, reboot your cluster.

Run the following query to identify sessions that are currently holding locks:

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration from svv_transactions a left join (select pid,relation,granted from pg_locks group by 1,2,3) b on a.relation=b.relation and a.granted='f' and b.granted='t' left join (select * from stv_tbl_perm where slice=0) c on a.relation=c.id left join pg_class d on a.relation=d.oid where a.relation is not null;

This will result in an output similar to this:

txn_owner | txn_db |   xid   |  pid  |         txn_start          |      lock_mode      | table_id | tablename | granted | blocking_pid |        txn_duration         | 
----------+--------+---------+-------+----------------------------+---------------------+----------+-----------+---------+--------------+-----------------------------+
 usr1     | db1    | 5556898 | 19813 | 2018-06-30 10:51:57.486722 | AccessExclusiveLock |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |
 usr1     | db1    | 5558927 | 20450 | 2018-06-30 10:52:19.751199 | AccessShareLock     |   351959 | lineorder | f       |        19713 | 0 days 0 hrs 0 mins 30 secs |
 usr1     | db1    | 5552898 | 19813 | 2018-06-30 10:51:57.483722 | AccessShareLock     |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |

An f in the granted columns indicates a transaction from another session holds the lock currently. You can verify the process ID of the session in the blocking_pid column.

For instance, in the above example, PID 19713 is holding the lock currently.

You can either wait for the transaction holding the lock to finish or you can manually stop the session with this command:

select pg_terminate_backend(PID);

When you terminate a PID, it rolls back the running transactions. Furthermore, it releases all locks in the session. These are then claimed by other waiting transactions.
In case the termination of the PID is not successful, reboot the cluster and terminate the process.

Need help with Server Management? Give us a call.

Conclusion

To summarize, we learned how to detect and release locks to avoid stuck queries in Amazon Redshift with ease. With Bobcares by your side, you can relax.