#59 SQLServer 2005: Batch updates fail if too many rows

[Lothsahn]

I reproduced this issue against SQLServer 2005. My application attempted to do a batch execute with 402,000 rows added to the batch. In some cases, the system hangs and never completes, while in other cases, I get a socket read exception. The exception is given below.

I have attempted to set the batchSize parameter, but it appears to have no effect on this problem. I was able to workaround the problem by manually calling executeBatch after each 1000 statements that I add. I found that for my particular insert, it fails if I batch 50,000 statements together, but 40,000 statements succeeds.

I am using JTDS 1.2.2 and SQLServer 2005 SP3.

java.sql.BatchUpdateException: I/O Error: Connection reset by peer: socket write error
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:966)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.workforcesoftware.Util.LoggingInvocationHandler.invoke(LoggingInvocationHandler.java:70)
at com.workforcesoftware.Util.DB.SQLInvocationHandler.invoke(SQLInvocationHandler.java:48)
at com.workforcesoftware.Util.DB.StatementProxyHandler.invoke(StatementProxyHandler.java:106)
at $Proxy2.executeBatch(Unknown Source)
at com.workforcesoftware.Util.DB.StandardBatchExecutor.executeBatch(StandardBatchExecutor.java:40)
at com.workforcesoftware.Util.DB.StandardBatchExecutor.addToBatch(StandardBatchExecutor.java:35)

[momo]

I was able to "kind of" reproduce the problem you described. I can insert or update 150000 integer values in a batch without any problems. If I try larger batches, somewhere around a batch size of 200000 I hit a memory limit inside the SQL server itself. Sometimes this error is correctly reported by the server (and jTDS) and the sql server stays intact. But most times the whole server simply stops responding (what kind of scares me). That's your "system hangs" symptom, I think. At a batch size of around 350000 jTDS finally gives up due to an OutOfMemoryError and the server survives since the batch doesn't get executed.

In any case, setting the batchSize connection parameter solves all problems and allowes me to execute millions of batch operations without any problems.

[momo]

Could you please confirm you are still getting this error? If it can be reproduced, could you please attach a test case and your connection string to show what exactly you are trying to do?

[Lothsahn]

I can definitely still reproduce this problem. I'll try to give you a testcase to reproduce it.

It's possible I'm running out of memory--this is in a util class, so maybe that's the cause (we don't have gigabytes of memory available like in the actual server application itself). batchSize didn't help for me, however.

In all cases in my testing, SQLServer itself remained intact--for instance, even after causing the hang, I was able to restart my application without bouncing the database. However, if I hit an internal limit for my connection or something, it's possible that's what I ran into.

I will provide a testcase and my connection string in the next couple days or so. Unfortunately right now I'm swamped and we have a workaround so it's not critical.

Thanks for the followup.

[momo]

Great (or not, that actually depends on your point of view) to hear you can reproduce that error! In fact I think you are encountering a different error if the server stays intact in your case. In my (very limited) testing the SQL server actually stalled completely preventing any further connections...

That being said, it would be very helpful if you could provide a test case. Please don't spend too much time simplifying the test if you don't have that time. Anything reproducing that problem would be fine and would be highly appreciated.

[momo]

Did you already find some time to take a look into this manner? I'd really like to fix the problem if it's cause by jTDS (or at least make sure it isn't) since it looks like a serious issue.

Thanks,
momo

[Lothsahn]

Testcase

[Lothsahn]

Attached DateOverflowTest. Reproduces the issue.

I was unable to reproduce the issue again when batchSize was set. I'm not sure why.

When batchSize =0:
Caused by: java.sql.BatchUpdateException: I/O Error: Connection reset by peer: socket write error

I've also gotten a "not enough memory to process this request". I was unable to reproduce the hanging with this testcase.

This testcase does not pass with a BATCH_SIZE of 100,000 or 50,000, but does pass with a BATCH_SIZE of 10,000.

Perhaps instead of a default of 0 for SQL Server, JTDS should use a smaller default (1000 or 5000?) This would avoid stability problems if a batch got too large, but allow users who are concerned about maximum batching performance to tweak the setting if needed.

[momo]

Thanks for your efforts! Your test shows the described behavior if the batchSize parameter is not set, failing with larger batches. The error itself is caused by some memory problem inside the server and can also be reproduced with other versions of MS SQL Server and Sybase ASE.

In general, I'm not sure if using a default batch size is a feasible solution since it is somewhat out of the JDBC specs and might also break application logic (e.g. think about what that means if the connection is in auto-commit mode and the batch fails due to invalid data passed to the server at some point). I'm aware that jTDS already uses a default batch size of 1000 for Sybase, but that behavior has been introduced a long time ago, maybe without taking possible side effects into account.

I'm moving this to feature requests for the time being, assuming jTDS currently works as intended. Nevertheless, I'll do some further investigation concerning possible solutions or workarounds. I'd guess we might end up also setting a default batch size for MS SQL and completely ignore the batchSize parameter in auto-commit mode, but this may still leave some room for invalid behavior from an application's point of view.

Cheers,
momo