what is the correct way to clear obsolete locks under DbTxnManager?

michalLi — Fri, 16 Sep 2022 14:46:31 GMT

It seems that when we use DbTxnManager in CDH6.X, if SQL query does not complete successfully or is terminated abruptly (ex. ctrl+c), then the locks it implicitly acquired will not be automatically released, and other new SQL queries which references the same table/partition will be blocked trying to acquire necessary locks, and hence can't be executed successfully.

we can't manually clear the locks by issuing unlock statements under DbTxnManager,because it will pop up error: Current transaction manager does not support explicit lock requests.Transaction manager:org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

it seems currently the only way to clear above obsolete locks is to log into the metastore db and use sql statements like below to delete records from table hive_locks:

select hl_lock_ext_id from HIVE_LOCKS where HL_TABLE=’prcs_task’;
delete from hive_locks where hl_lock_ext_id = 125542;

I am wondering, if this is the correct way to manually clear obsolete locks under DbTxnManager?

And are there any progress in the hive community, regarding how to automatically clear obsolete locks caused by failed or terminated sql queries? like some sort of timeout and house keeping mechanism?

Re: what is the correct way to clear obsolete locks under DbTxnManager?

michalLi — Thu, 21 Jul 2022 08:24:13 GMT

some more information to add:

it seems that in DbTxnManager, failed or terminated sql queries will not leave stale locks behind, as there are heartbeat mechanisms involved. Only when the hs2 process is not functioning properly, like when the hs2 host crashed, will there be staled locks left behind, and we have to manually clear them by logging into the metastore db.

question Re: what is the correct way to clear obsolete locks under DbTxnManager? in Support Questions

what is the correct way to clear obsolete locks under DbTxnManager?

Re: what is the correct way to clear obsolete locks under DbTxnManager?