We are using ScrollableResultSets to read through a large number of rows and, after some processing, update each row after it is read. This pattern works perfectly for us, and has worked with some extremely large data sets (1,000,000+ rows). We're using MS SQL Server 2008 R2 with the most recent MS JDBC Driver.

However, we are now running into a problem. In one program, this pattern works for up to about 400 or so rows. Anything larger than that, and we get a SQL Server lock. The SELECT statement puts a ASYNC_NETWORK_IO lock on the table, and this causes the update to lock and causes all sorts of issues. For this one specific table and process, it is absolutely consistent. We always get the ASYNC_NETWORK_IO lock on the table at 450+ rows. Any number of rows less than that, it works perfectly. Any other table or use of the identical pattern, it works perfectly.

Has anybody every experienced anything like this before?

I am replying to my own post, but I resolved this issue, and want to have this out here in case anyone else has the same issue!

The problem is the JDBC Driver setting; if you are using ScrollableResultSets you MUST make sure that selectMethod is set to "cursor" and not "direct" (which is the default). If you do have it set as "direct" then SQL pulls the dataset back to the driver and will not use server-cursors.