title: Does a MERGE INTO statement lock the entire table when merging into a partitioned table based on the partitioned columns?
body: <p>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. </p>
  
  <p>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.</p>
  
  <pre><code>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...
  </code></pre>
  
  <p>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?</p>