Exclusive write lock for MERGE INSERT

Learn how you can enable EXCLUSIVE (X) lock for the MERGE INSERT operations, which helps in preventing duplicates during concurrent MERGE INSERT operations.

Before upgrade to CDP 7.1.4

MERGE INSERT operation is treated as regular INSERT and acquires a SHARED_READ lock, which does not prevent other INSERT operations. Two concurrent MERGE INSERT operations generate duplicates due to the lack of locking.

After upgrade to CDP 7.1.4

A new configuration, hive.txn.xlock.mergeinsert is introduced to ensure that MERGE INSERT operations acquire EXCLUSIVE or EXCL_WRITE lock for transactional tables.

When this property is set to 'true', it prevents duplicates when MERGE INSERT statements are running in parallel transactions. By default, the property is set to 'false'.

If hive.txn.xlock.write=false (optimistic concurrency control), EXCL_WRITE is acquired by MERGE INSERT and SHARED_WRITE is acquired by INSERT, else the operations acquire EXCLUSIVE and SHARED_READ respectively. Both of these combinations prevent concurrent execution.

Action required

Ensure that you enable hive.txn.xlock.mergeinsert if you want to provide EXCLUSIVE lock for the MERGE INSERT operation.

For more information, see HIVE-24000.