If client takes long time to receive data and in turn send
acknowledgement to SQL Server that it has received the data SQL Server
has to wait, due to this wait SQL Server will not release the locks
held by the query unless acknowledgement is received from client.
This is not accurate, it is dependent on the isolation level.
At the default
READ COMMITTED locks are not held for the duration of the statements execution.
READ COMMITTED does not provide statement level read consistency, the only guarentee is that you cannot read uncommitted data. A shared lock is acquired and held to read the row and then released.
Unless you have LOB types.
LOB types, being potentially very large, cannot be buffered. A shared lock must be acquired and held until the statement completes, essentially giving you
REPEATABLE READ behavior at
If I'm making a single call to an MSSQL database over a high-latency
network, will table locks occur due to that latency?
The latency isn't causing the table lock, no. However, if a table lock has been acquired the latency is going to prolong it.
To quote someone that knows the mechanics of this better than I (@RemusRusanu):
Results are returned back to the client program as the execution
proceeds. As rows ‘bubble’ up the execution tree, the top operator is
usually tasked with writing these rows into network buffers and
sending them to back to the client. The result is not created first
into some intermediate storage (memory or disk) and then sent back to
the client, instead it is sent back as is being created (as the query
executes). Sending the result back to the client is, of course,
subject to the network flow control protocol. If the client is not
actively consuming the result (eg. by calling SqlDataReader.Read())
then eventually the flow control will have to block the sending side
(the query that is being executed) and this in turn will suspend the
execution of the query. The query resumes and produces more results
(continue iterating the execution plan) as soon as the network flow
control relieves the required network resources. [source]
Where results aren't consumed as quickly as SQL Server can deliver them, be it due to the client or the network, we see
ASYNC_NETWORK_IO waits accumulating. To reiterate, this will not influence the locks that are acquired, just the duration they are held.