When you work on large enterprise data warehouse, you will be working with multiple tables and those tables will be shared across multiple data marts within your application. Sometimes, users will explicitly lock the access to the table if they are refreshing or updating data. In this article, we will check how to identify and kill Redshift Table locks.
Redshift Identify and Kill Table Locks
You can use Redshift system tables to identify the table locks. One such table is STV_LOCKS, this table holds details about locks on tables in your Redshift database.
Explicit Table Lock in Redshift
If you are executing any update on table, you can explicitly lock table by using LOCK command..
Below is the syntax that you can use.
LOCK [ TABLE ] table_name [, ...]For example, use lock within transaction blocks.
begin;
lock event, sales;Identify Table Locks in Redshift
As mentioned earlier, you can use STV_LOCKS table to check for any locks on table that you are using in your query.
Amazon Redshift locks tables to prevent two users from updating the same table at the same time. You can use the STV_LOCKS table to view any current updates on tables in the database.
For example, below example shows current updates on tables in the database.
SELECT table_id,
last_update,
lock_owner,
lock_owner_pid
FROM stv_locks;
table_id | last_update | lock_owner | lock_owner_pid
----------+----------------------------+------------+----------------
100004 | 2019-12-23 10:08:48.882319 | 1043 | 5656
100003 | 2019-12-23 10:08:48.779543 | 1043 | 5656
100140 | 2019-12-23 10:08:48.021576 | 1043 | 5656
(3 rows)Query will return the tableid and user pid if in case DB locks.
Kill the Table Locks
Now use the user pid to kill the table lock. Note that, current update will be roll backed if you kill the session.
SELECT pg_terminate_backend(pid);Sometimes, multiple sessions will lock database table. Follow below steps to kill multiple sessions.
Kill the multiple sessions
Check if there are any multiples sessions for the same user. Kill those active sessions and then try running your DROP or TRUNCATE table command.
To list sessions, use below query:
SELECT * FROM STV_SESSIONS;
Kill the sessions using below query:
SELECT pg_terminate_backend(pid);
Note: you will get the PID from list of sessions.
Related Articles
Hope this helps 🙂