SQL Server Network Latency – Will Increased Network Latency Cause Table Locks?

lockingNetworksql server

If I'm making a single call to a SQL Server database over a high-latency network, will table locks occur due to that latency? Say I query table A for some records, and SQL Server has to return that data over a slow network – will there be a read lock on table A while the server sends the response over the network, or does SQL Server release the lock before sending the response?

Also, would the answer vary based on the size of the response? If it just has to return a few KB vs several hundred MB, would that make a difference?

Creating an explicit transaction, running queries, and closing the transaction would obviously cause the tables to lock, since the duration of the transaction is correlated with my latency.

Best Answer

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 READ COMMITTED.

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.

Related Question