We are using Microsoft Access Front-End that uses 'Dynasets' (not my idea) with SQL Server back-end. And we're also using Phython Client Side code calling Selects.
In both cases we get wait types of ASYNC_NETWORK_IO for hours. Any idea what's going on? ASYNC_NETWORK_IO means that SQL Server has not received a message from the Client that it has consumed all of the records. In this case we think it's not a Network issue.
Could this be a case that SQL Server has sent all the records to the Client. But for whatever reason the Client hasn't returned a message that it has consumed (or received) all the records? How can we tell whether that is the case or if it's a Network issue?
We've had cases where these Select statements have caused blocking. Why doesn't SQL Server take the position: we've got all the records and sent them to the client, therefore we don't need to keep any locks here?
A diner walks into a restaurant, and sits down.
A waiter comes over with a menu and hands it to the diner.
The diner says "stay here, I'm not done with you yet..." while he reads the menu.
The waiter waits.
The diner says "I'll have a burger and fries."
The waiter goes back to the kitchen and returns with the burger and fries."
The diner says "stay here, I'm not done with you yet..." and he takes a bite.
The waiter waits.
The diner takes his time eating his food as he browses the web, replies to emails, makes phone calls...
Eventually, the diner finishes his meal, looks to the waiter and says "thanks, I'm done."
The waiter leaves.
The diner is MS Access.
The waiter is SQL Server.
This is what Access is doing to SQL Server.
3 Likes
Fair enough. But why does SQL Server go along with it? Why does it hold onto the locks?
If SQL Server is working on joining the tables in a Select query, it makes sense to have the Read locks. But once it's finished the Select, why should it be waiting around and holding onto locks just because it hasn't got a response from the Client? If sending it to the client, I'm not sure why it would hold onto locks.
The answer may lie in the connection/cursor options. Maybe dynaset is holding locks (maybe it's using REPEATABLE READ or SERIALIZABLE?). You can probably check that by looking at the transaction isolation level in sys.dm_exec_sessions. sp_whoisactive can help you see what's being blocked while your Access front end is running. If your database is currently set to READ COMMITTED, it might be time to consider RCSI.