In any relational database, if you didn’t close the session properly, then it’ll lock your DDL queries. It’s applicable to RedShift as well. A few days back I got a scenario that we have to run some DROP TABLE commands to create some lookup tables. But every time while triggering this DDL it got stuck. Then we realize there were some sessions that are still open and those sessions are causing this locking. There we 30+ sessions. I know we can fix this by properly closing the session from the application side. But in some emergency cases, we need to kill all open sessions or locking session in Redshift.
Then my DBA brain was telling me to create a stored procedure to get all the locking sessions and kill them in one shot. I never recommend running this all the time. But if you are a DBA or RedShift Admin, then you need to have these kinds of handy toolkits.
Testing the Procedure:
Open multiple sessions for a table and don’t close them.
Do open a few more sessions. Then run the stored procedure.