Locks in Hive

Database has locks and Hive is no different. Locks in database can be either Read Lock or Write Lock. Locks are used when concurrent applications tries to access the same table. Locks prevents data from being corrupted or invalidated when multiple users try to reach while others write to database.

There may be specific scenarios in which when we try to delete the table, the table does not respond at all. Please check the hive.log in /tmp/$user and you can see that the table is being Locked.
First test in confirming that a table is being locked is by running the
“Show Locks” Command in the Hive CLI. It will display the Locks for all the tables in the database. Please check for the table’s name against the Show Locks Command’s output.

For Example:

hive>show locks; 

For Mysql:-

mysql> select hl_lock_ext_id from HIVE_LOCKS where HL_TABLE=’prcs_task’;

Then delete locks:

delete from hive_locks where hl_lock_ext_id = 125542 
delete from hive_locks where hl_lock_ext_id = 127397 
delete from hive_locks where hl_lock_ext_id = 127399 
delete from hive_locks where hl_lock_ext_id = 127405

NOTE:-

(i) Another easy way is to either restart the HS2 which needs a down time or truncate the HIVE_LOCKS table, just make sure no jobs are running.

(ii) Make sure you are only clearing the Stale locks after confirming that no Queries are running which are using the locks. It will corrupt the Table.