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'.
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
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.