ERROR : FAILED: Error in acquiring locks: Error communicating with the metastore org.apache.hadoop.hive.ql.lockmgr.LockException

Getting the Error in acquiring locks, when trying to run count(*) on partitioned tables. The table has 365 partitions when filtered on <= 350 partitions, the queries are working fine. when tried to include more partitions for the query, it's failing with the error.

working on Hive-managed ACID tables, with the following default values

• hive.support.concurrency=true //cannot make it as false, it's throwing <table> is missing from the ValidWriteIdList config: null, should be true for ACID read and write.
• hive.lock.manager=org.apache.hadoop.hive.ql.lockmgr.zookeeper.ZooKeeperHiveLockManager
• hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
• hive.txn.strict.locking.mode=false
• hive.exec.dynamic.partition.mode=nonstrict

Tried increasing/decreasing values for these following with a beeline session.

• hive.lock.numretries
• hive.unlock.numretries
• hive.lock.sleep.between.retries
• hive.metastore.batch.retrieve.max={default 300} //changed to 10000
• hive.metastore.server.max.message.size={default 104857600} // changed to 10485760000
• hive.metastore.limit.partition.request={default -1} //did not change as -1 is unlimited
• hive.metastore.batch.retrieve.max={default 300} //changed to 10000.
• hive.lock.query.string.max.length={default 10000} //changed to higher value

Using the HDI-4.0 interactive-query-llap cluster, the meta-store is backed by default sql-server provided along.

We also faced the same error in HDInsight and after doing many configuration changes similar to what you have done, the only thing that worked is scaling our Hive Metastore SQL DB server.

We had to scale it all the way to a P2 tier with 250 DTUs for our workloads to work without these Lock Exceptions. As you may know, with the tier and DTU count, the SQL server's IOPS and response time improves thus we suspected that the Metastore performance was the root cause for these Lock Exceptions with the increase in workloads.

Following link provides information about the DTU based performance variation in SQL servers in Azure.

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu

Additionally as I know, the default Hive metastore that gets provisioned when you opt to not provide an external DB in cluster creation is just an S1 tier DB. This would not be suitable for any high capacity workloads. At the same time, as a best practice always provision your metastores external to the cluster and attach at cluster provisioning time, as this gives you the flexibility to connect the same Metastore to multiple clusters (so that your Hive layer schema can be shared across multiple clusters, e.g. Hadoop for ETLs and Spark for Processing / Machine Learning), and you have the full control to scale up or down your metastore as per your need anytime.

The only way to scale the default metastore is by engaging the Microsoft support.