16

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.

  • Unless you specify a nolock hint, there will always be a lock. The latency just determines how long the lock will be held. – Brandon Sep 30 '14 at 16:50
  • 3
    And even with nolock, you'll still get locks – billinkc Sep 30 '14 at 17:00
  • @brandon Is that documented by Microsoft anywhere? My searches have turned up empty. – Evan M Sep 30 '14 at 17:15
  • 1
    @Brandon NOLOCK does not mean what you think it means. – Aaron Bertrand Sep 30 '14 at 17:16
  • 3
    @Brandon Unless you specify a nolock hint, there will always be a lock. <-- this implies if you use nolock there might not be locks. I was merely clarifying. – Aaron Bertrand Sep 30 '14 at 18:27
15

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.

| improve this answer | |
9

Mark's answer cleared up a lot of my confusion, but I wanted to post my findings after I tested this using NetBalancer to emulate latency.

I had my local machine call a remote SQL server and execute both SELECTs and INSERTs on a table within a small transaction. On the remote machine, I connected to the local SQL instance and used a WHILE loop to repeatedly iterate over the sys.dm_tran_locks table, looking for any locks on the table I was modifying and reading from. I installed NetBalancer on the server and used it to emulate network latency on the server's network connection.

Here's what I found:

  • For statements that don't return much data to the client, latency has no effect on locking. I was only returning a few hundred bytes of data at most. The transaction on my machine had a 250ms WAITFOR that kept the locks, and when I ramped the network latency to 5000ms the lock duration stayed close to 250ms.
  • For statements that return a lot of data, latency definitely impacts locking I returned tens of thousands of rows back to the client, and with no latency, the lock duration was short. When I increased the latency, the locks continued until I received all the data.

From this, I'm concluding that latency doesn't matter as long as the data fits in the network buffer. If SQL has to put a lot of data in the network buffer, latency will cause that buffer to back up and SQL will hold the table locks until it can put all of the query result into the buffer.

| improve this answer | |
  • Interesting results. What client program/library is this with? – James Lupolt Sep 30 '14 at 22:05
  • Good stuff. Any chance you can spend a bit more time on this and see if you can determine the result size at which this occurs? – Mark Storey-Smith Sep 30 '14 at 22:08
  • @MarkStorey-Smith I don't think I can get a precise value, and it would undoubtedly vary by machine. From vircom.com/security/improve-sql-nic-performance, it looks like it's a setting on your local NIC, and the one on my database server was set to 'auto' – Evan M Sep 30 '14 at 22:22
  • @James I just used SSMS on both machines – Evan M Sep 30 '14 at 22:23
0

If I'm making a single call to an MSSQL database over a high-latency network, will table locks occur due to that latency?

When a query is fired and completed by SQL Server it produces the the results ,place it in output buffer and send it to client which then fetch the result from the Output buffer. SQL Server will not release the locks held by the query unless Acknowledgement is received from Client. Which might cause blocking.

Edit: Evan you can refer to This MS support article

In section 3

Blocking Caused by a SPID Whose Corresponding Client Application Did Not Fetch All Result Rows to Completion

After sending a query to the server, all applications must immediately fetch all result rows to completion. If an application does not fetch all result rows, locks can be left on the tables, blocking other users. If you are using an application that transparently submits SQL statements to the server, the application must fetch all result rows. If it does not (and if it cannot be configured to do so), you may be unable to resolve the blocking problem. To avoid the problem, you can restrict poorly-behaved applications to a reporting or a decision-support database.

| improve this answer | |
  • Thanks for your answer Shanky! Do you happen to know if this is documented anywhere? – Evan M Sep 30 '14 at 18:09
  • 3
    Locking in the database engine – user507 Sep 30 '14 at 18:32
  • 5
    This is not correct. – Mark Storey-Smith Sep 30 '14 at 19:04
  • This is correct it seems 'application does not fetch all result rows, locks can be left on the tables, blocking other users. If you are using an application that transparently submits SQL statements to the server, the application must fetch all result rows. If it does not (and if it cannot be configured to do so), you may be unable to resolve the blocking problem. To avoid the problem, you can restrict poorly-behaved applications to a reporting or a decision-support database.' More over i was talking in general terms. You can read from here support2.microsoft.com/kb/224453 – Shanky Sep 30 '14 at 20:03
  • 4
    @Shanky Create a large table. SELECT * from it in at READ COMMITTED in one SSMS connection, monitor locks from another. At anyone time, how many locks do you see held? – Mark Storey-Smith Sep 30 '14 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.