You need to add WITH (NOLOCK). Not entirely sure why but I just ran into this issue today and this solved my issue. SELECT * FROM [110.10.10.100].testdbname.dbo.ufn_getdata(‘4/25/2013’) AS tb WITH (NOLOCK);
There is no functional difference, but eventually the syntax without WITH will not work. This has been deprecated: select customer, zipcode from customers c (nolock) So you should be using this format: select customer, zipcode from customers c with (nolock) Not using the WITH keyword for table hints has been deprecated since at least SQL … Read more
A SELECT doesn’t lock any table in PostgreSQL, unless you want a lock: SELECT * FROM tablename FOR UPDATE; PostgreSQL uses MVCC to minimize lock contention in order to allow for reasonable performance in multiuser environments. Readers do not conflict with writers nor other readers.
Prior to working on Stack Overflow, I was against NOLOCK on the principal that you could potentially perform a SELECT with NOLOCK and get back results with data that may be out of date or inconsistent. A factor to think about is how many records may be inserted/updated at the same time another process may … Read more
I won’t address the READ UNCOMMITTED argument, just your original question. Yes, you need WITH(NOLOCK) on each table of the join. No, your queries are not the same. Try this exercise. Begin a transaction and insert a row into table1 and table2. Don’t commit or rollback the transaction yet. At this point your first query … Read more
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In … Read more