JDBC, DBCP and SQL Server "Lock request time out period exceeded"
I've got curious case.
How can I get com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded. on getMoreResults() ? What could cause such case?
Stacktrace:
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:1191)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.getMoreResults(DelegatingStatement.java:270)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.getMoreResults(DelegatingStatement.java:270)
at org.springframework.jdbc.core.JdbcTemplate.extractReturnedResults(JdbcTemplate.java:1045)
at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:988)
at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:928)
... 34 more
JdbcTemplate code looks like this:
return execute(csc, new CallableStatementCallback>() {
public Map doInCallableStatement(CallableStatement cs) throws SQLException {
boolean retVal = cs.execute();
int updateCount = cs.getUpdateCount();
if (logger.isDebugEnabled()) {
logger.debug("CallableStatement.execute() returned '" + retVal + "'");
logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
}
Map returnedResults = createResultsMap();
if (retVal || updateCount != -1) {
returnedResults.putAll(extractReturnedResults(cs, updateCountParameters, resultSetParameters, updateCount));
}
returnedResults.putAll(extractOutputParameters(cs, callParameters));
return returnedResults;
}
});
cs.execute() calls stored procedure this way
exec sp_prepexec @p1 output,N'@P0 int,@P1 int',N'exec my_sproc @P0,@P1', 1, 2
That call was successful and after that method extractReturnedResults() was called. It uses getMoreResults(). First iteration was successful and on second I got exception. It happened on my production system and I cannot reproduce it again. Stored procedure itself didn't throw lock timeout because code is wrapped in try/catch. Just to double check I've removed try/catch blocked, simulated lock timeout and I've received exception on call execute() - which makes sense. It's like lock timeout happened AFTER stored procedure execution and in the middle of printing results set? I only have defined lock_timeout inside try/catch block. I didn't set queryTimeout or anything like that in jdbc connection string?
Another theory is that network bandwidth was full at that precise moment (after successful calling of stored procedure and in the middle of printing result sets). The problem with that theory is that I don't have any other timeouts set.
链接地址: http://www.djcxy.com/p/74418.html