Sql – Does a MERGE INTO statement lock the entire table when merging into a partitioned table based on the partitioned columns

oracle11gsql

After some performance issues occurred in our production environment, I asked for assistance from our database administrators. While helping, they informed me that merging locks the table and suggested I use an UPDATE statement instead.

From everything I've read, I was under the impression that MERGE INTO and UPDATE have similar incremental locking patterns. Below is an example of the sort of MERGE INTO statement that our application is using.

MERGE INTO sample_merge_table smt
USING (
    SELECT smt.*, sjt.* 
    FROM sample_merge_table smt 
    JOIN some_join_table sjt 
        ON smt.index_value = sjt.secondary_index_value 
    WHERE smt.partition_index = partitionIndex
) umt ON (smt.partition_index = partitionIndex AND smt.index_value = umt.index_value)
WHEN MATCHED THEN
    UPDATE SET...
WHEN NOT MATCHED THEN
    INSERT VALUES...

Upon running this statement, what would the locking procedure actually be? Would each table involved in the USING select be locked? Would the sample_merge_table be completely locked, or only the partition that is being accessed? Would the UPDATE statement lock incrementally, or does the MERGE INTO itself already own the required locks?

Best Answer

The merge statement works on a row basis, but locks everything before hand i.e. when the statement execution is finished planing and the affected rows identified.

Readings:

https://forums.oracle.com/message/4372012

Related Topic