Lock mode and concurrency (Table/Partition?) - How is possible to run Copy and Update concurrently o

How can I run COPY and UPDATE concurrently on same table but locking different partitions ? I have a fact table with granular/detailed data, big data, partitioned by date. Every day I load new data creating a new partition. Simultaneously I need to run an UPDATE on same table but for a previous day partition, different than the one used by COPY. Lock mode for UPDATE is 'X' and COPY is 'I', so no concurrency is allowed. Is it possible to lock the specific partition instead of all table? Any workaround? Thanks in advance.


  • You could load the new partition into a different table, and use MOVE_PARTITIONS_TO_TABLE() to move it into the real table. That would give you at least some opportunity to execute both operations at the same time. --Sharon

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file