Improve non-transactional metadata-sync experience considering clusters with large number of shards

1. Investigate how we end-up with large number of locks in non-transactional mode.
2. Make non-transactional metadata-sync truly idempotent:
e.g., the commands that we send to detach partitions is not truly idempotent even if we do `ALTER TABLE IF EXISTS DETACH PARTITION`.
This command guards us against the cases where the parent relation does not exist. However, it doesn't save us in the cases where:
* the child relation does not exist
* the child relation is not a partition of parent relation