Support Questions
Find answers, ask questions, and share your expertise

check if locks exist on a table in a query

Expert Contributor

I have

INSERT OVERWRITE

statements scheduled as hive queries through oozie. Before running INSERT OVERWRITE, I want to check if locks exist on the table

i.e something like show locks dbname.tablename

but it should basically return somethign that I can use to decide if I have to run UNLOCK TABLE dbname.tblname or not.

OR

Do we have somethign like UNLOCK TABLE IF EXISTS LOCK dbname.tblname?

It is really important as I have been tryign to find a way to update my tables in hive at the scheduled time but oozie gets stuck if a user was using select on the table and my scheduled queries tried to run right at the same time. I want to make sure that the query runs at its scheduled time even if the select may or may not give the updated results

3 REPLIES 3

Master Collaborator

Hive does provide a show locks command, for more details refer to Apache Hive documentation.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ShowLocks

Expert Contributor

@Deepesh: How do I use that in query scheduled through oozie?

I need to have something like this in hive:

IF(LOCK EXISTS ON TABLE)

unlock table

else

nothing

OR

UNLOCK TABLE IF LOCK EXISTS tablename

This is because the query is going to be scheduled through oozie and I need the INSERT OVERWRITE TO always execute and not fail because of any locks.

One liner answers like this barely help. Request the questions be read with attention before answering like this.

New Contributor

try to lock the table before and then unlock it

LOCK TABLE tablename SHARED;
UNLOCK TABLE tablename;