SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER
On further investigation I believe the update itself might be causing the locking. It is on a large table 500+ million rows. As I understand it the table will become locked as soon as SQL starts to find the join. Perhaps the way activity monitor displays the locking is a little misleading though