hive - FAILED: Error in acquiring locks

Error log¶

FAILED: Error in acquiring locks: Lock acquisition for LockRequest(component:[LockComponent(type:EXCLUSIVE, level:TABLE, dbname:dw, tablename:dim1, operationType:INSERT, isAcid:false), LockComponent(type:SHARED_READ, level:TABLE, dbname:ods, tablename:ds_data2, operationType:SELECT), LockComponent(type:SHARED_READ, level:TABLE, dbname:ods, tablename:dim3, operationType:SELECT)], txnid:0, user:airflow, hostname:hubserver1, agentInfo:airflow_20190731065910_5be6e4a9-8e33-4b8d-aa0b-aa778cca3105) timed out after 5503478ms. LockResponse(lockid:69648, state:WAITING)

After careful analysis, it was found that the operation failed because of the lock of lockid:69648.

hive lock¶

There are two types of locks in hive, Shared (S) and Exclusive (X). Take the above log as an example, the original SQL executed is probably

insert overwrite table dim1 (bus_date='2019-07-29') select * from data2 t1 left join dim3 t2 on t1.id=t2.id;

The two tables read are both S locks, and the inserted dim1 is X locks. Operations that only trigger s locks can be executed concurrently. As long as an operation initiates an x lock on a table or partition, the table or partition cannot execute jobs concurrently.

SHOW LOCKS <TABLE_NAME>; --View locks

solution¶

Add the following statement to the error Sql

set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager;