So, it turns out in SQLServer if a row is locked (in a transaction etc) then it also blocks queries by default.
To me that's kind of astonishing, as it means queries in PDI and in the front end will appear to hang, pretty much randomly.
In fact, i only discovered this because our legacy app servers make extensive use of the
Syntax - Turns out, this is exactly why.
But; I'm not really a fan of putting hints into SQL queries, especially not routinely. So do others do this? or is there a better way?
You can read detail about it here:
PS: There's an even more bizarre thing - if you use NOLOCK you can read dirty data, because it sends you the UNCOMITTED data. That's what the article says anyway, which frankly is just astonishing, so much so i can't help wondering if I've misunderstood it!