4

I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's gets blocked by the select from the other application.

The wait code is "ASYNC_NETWORK_IO" which I found to be described as exactly whats happening by me, I quote:

This is usually where SQL Server is waiting for a client to finish consuming data. It could be that the client has asked for a very large amount of data or just that it’s consuming it really slowly because of poor programming.

Clients often process one row at a time, instead of caching the data on the client and acknowledging to SQL Server immediately.

My question is why is sql server waiting for the client to finish processing that select job, before allowing updates to occur on the table? is that a setting or a configuration that I could change?

4

SQL Server by default adheres to ACID database rules. The A in ACID stands for atomic, meaning that the whole transaction happens, or none of it does. The I in ACID stands for isolation, meaning that transactions are isolated from one another, so either all the results from one transaction are visible to another transaction, or none of them are. This is accomplished in SQL Server by using locks for both reads and writes (at least by default). Reads (SELECTs) obtain a read lock, which prevents any write (UPDATE or INSERT) operations on those items (or pages, or the whole table depending on what SQL's black magic lock escalation algorithm decides on). In order to preserve Isolation, read locks for queries must be held until all results have been sent or buffered. This prevents the writes from obtaining their locks (or rather causes them to wait to obtain them), unless the writes are otherwise isolated (see other answer about snapshot isolation). If not, some of the results from a subsequent write could end up in the results of the query that occurred before the write.

There are several solutions for this, each with trade-offs:

  1. Buffer results on the client side (read all rows immediately as quickly as possible and do further processing later). This may require lots of client-side memory if there are lots of results, and will still block updates until the client is done buffering (which is why it must be completely buffered before any subsequent processing).
  2. Buffer results on the server-side. You can do this by selecting the data into a temp table and then returning the temp table instead of the original query. This will require server-side memory and/or disk and could therefore interfere with other server-side operations.
  3. Use WITH (NOLOCK) on the SELECT of the read operation. This tells SQL Server to ignore isolation for this read, which means that you could get the original version of a row at the beginning of the results and a modified version of another row later in the results when both rows were modified by the same transaction and the versions returned were never in the database at the same time (ie. the rows were modified during the query, after the first row was returned but before the second row was). This answer has the least performance cost, but produces non-ACID results by breaking Isolation and/or Atomicity (depending on how you look at it).
  4. Use snapshot isolation (see other answer--I'm not an expert with snapshot isolation). Note that I've had significant performance issues with snapshot isolation, and it will require plenty of server-side resources as well.

There may be other solutions as well, but these are the most common.

  • Thank you for the detailed explanations and solutions. @otherAnswers were also very helpful, but I could only accept one.. – Ezi Mar 1 '15 at 19:05
  • I could be mistaken, but I am not sure that not waiting for client acknowledgement row by row would break ACID compliance. I wonder how Oracle or PostgreSQL deal with this. – xav Jul 27 '16 at 19:00
  • @xav, it has to block updates to the data being returned until all the results have been read and either buffered or given to the client. Otherwise it breaks isolation because results from the update that would be blocked are seen in the second part of the results, but not the first part. – James Jul 30 '16 at 15:24
2

SQL Server will send the client application some rows and wait for a response - in your case a request for another batch of rows.

Generally (but not always), the ASYNC_NETWORK_IO wait is due to application design. Rather than caching the data and them sending a response to SQL Server almost immediately, it performs some type of RBAR (Row By Agonizing Row) operation on the data it receives.

While data is being read in, a Share lock will be placed on the resource and nothing can update that resource until the S lock has been released. If the application is performing processing row by row as it is received (rather than processing on the 20k rows when the query is completed), you'll hold S locks for longer and the updates will unfortunately have to wait their turn.

If I see this, I look at the application before the network.

  • Sorry, but you didn't add anything to what I didn't already know and wrote in the question. I want to know how to prevent that lock from being placed on the table. if that's not possible I would like to understand why. a select is not modifying any data, so why is a lock necessary? – Ezi Feb 26 '15 at 17:00
  • I gave a very likely scenario as to why you are receiving the wait and that is the application processing rows 1 at a time rather than caching them. The final question, I did not answer - You could use a NOLOCK hint but this would need testing - this would get rid of the Share lock and in place, put an Sch-S lock on the table resource. – Clive Strong Feb 26 '15 at 17:08
  • Thank you for your comment, can I make NOLOCK on the server side via the SP? I'm trying to avoid changing the application as much as possible. also what is the risk of doing so? the data is never modified by that transaction. – Ezi Feb 26 '15 at 17:16
  • 1
    @Ezi NOLOCK still takes Sch-S locks, but no shared locks, so it might/might not cut it for you. Moreover, it has multiple undesired effects. See blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere for a quick list. – spaghettidba Feb 26 '15 at 17:25
  • 1
    Yes, you can modify the stored procedure and add in the NOLOCK hint at the risk of dirty reads (same as the read uncommitted isolation level). Have a look at this blog post from Jason Strate - Well worth reading and covers off NOLOCK nicely. jasonstrate.com/2012/06/the-side-effect-of-nolock – Clive Strong Feb 26 '15 at 17:32
2

If you use snapshot isolation levels, no S lock is placed on the rows, so UPDATEs are not blocked. Make sure you understand the difference between snapshot and read committed snapshot and that you test your application for these isolation levels. Also, test your workload for additional load in tempdb for the version store.

Edit: RE: downside of turning on snapshot isolation

Turning on snapshot isolation per se (ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION = ON) adds a hidden 14 byte extra column to every row in every table (when writes occur, rows not touched by writes are not versioned).
Using READ_COMMITTED_SNAPSHOT must be validated in the whole application(s) using the database (it's a database level setting), because the application could have been written with pessimistic locking in mind and optimistic locking could break some parts of it.
SNAPSHOT isolation (not READ_COMMITTED_SNAPSHOT) has optimistic locking conflicts control enabled, so you have to account for that and handle errors accordingly.

  • whats the downside of turning on snapshot isolation? – Ezi Feb 26 '15 at 20:27
  • See edits in my answer – spaghettidba Feb 26 '15 at 21:07

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.