I encounter a weird problem while trying to perform a sql query from a java call to sqlserver.
A single call to the java function goes well, but when I do the same within a loop (from 2 to 30 iterations), I sometimes (not always) got such an error :
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Le délai imparti à la requête a expiré. at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:5918)
The few things I noticed :
This is what I tried to perform, with no success :
the java call
(It is embedded in a single transaction)
final SessionImpl sess = (SessionImpl) this.getSessionFactory().openSession();
try (Connection conn = sess.connection(); Statement st = conn.createStatement();) {
final NamedParameterPreparedStatement ps = NamedParameterPreparedStatement.createNamedParameterPreparedStatement(conn, aStringWithTheRequestBelow);
// ... set parameters here ...
ps.setQueryTimeout(15);
final ResultSet rs = ps.executeQuery();
} catch (final SQLException ex) {
LOG.error("parameter 1 :" + parameter1 + "etc.");
} finally {
sess.close();
}
the TSQL query
(I made it brain-and-eyes-friendly, but it is a little more tricky that what it looks like; but basicely, you have the idea of it)
--DECLARE @date as DATE = '2017-09-03'; -- one of the input parameter set up from java.
IF OBJECT_ID('tempdb.dbo.#MATCHINGDAYS718567154','U') is not null
DROP TABLE #MATCHINGDAYS718567154; -- RANDOM TOKEN HERE TO AVOID CONCURRENCY
WITH startDateTable AS (
SELECT TOP 1 a.dateStart
FROM someSelection
),
endDateTable AS (
SELECT TOP 1 endDate
FROM anotherSelection
),
AllDays AS (
SELECT myFunc_getMaxDate(DATEADD(DAY,1,startDateTable.dateStart), DATEADD(dd, -30,@date))AS [Date]
FROM startDateTable
UNION ALL
SELECT
DATEADD(DAY, 1, [Date])
from AllDays
join endDateTable on 1=1
WHERE [Date] < myFunc_getMinDate(DATEADD(dd, +7, @date),endDateTable.endDate))
-- build a temporary table with all days from startDate to endDate the recursive way
-- with a min -30 days before input date and 7 days after output date
SELECT [Date]
INTO #MATCHINGDAYS718567154
FROM AllDays
OPTION (MAXRECURSION 37)
SELECT
manyFields
from MainTable
-- various joins
join #MATCHINGDAYS718567154 as MD on 1 = 1
where 1 = 1
and -- etc... many clauses including some computed over MATCHINGDAYS718567154
order by someField
DROP TABLE #MATCHINGDAYS718567154
Some other info
Edit 28/11 : THE CAUSE
somewhere in my loop, an object from the same table of the the queried item is inserted from time to time. It is performed in a separated transaction (though I also tried to perform it in the same transaction). The problem does not occur on the iteration when the object is inserted, but in the following iteration... Now, I have the cause... I still do not understand why this causes a failure on the next iteration... Though I suspect it will be perfectly obvious once I will understand it!
someIterationOver(List<SomeObject> manyObjects){
from(SomeObject mo : manyObjects){
List<MyTableObject> myTableObjects = performMyBigQueryOver(mo.getSomeId());
for(MyTableObject myTableSingleObject : myTableObjects){
if(myTableSingleObject.matchesSomeCondition()){
TableSingleObject aNewTableSingleObject = new TableSingleObject("newTableObject");
// this will cause the problem in the next iteration
saveTableObject(aNewTableSingleObject);
}
}
}
}
@Transactional(propagation = Propagation.REQUIRES_NEW)
private void saveTableObject(ableSingleObject aNewTableSingleObject){
this.sessionFactory.getCurrentSession.save(aNewTableSingleObject); // works fine
}
... any tips are welcome here!
Eventually, I managed to have it work : moving the "saveTableObject" method from within the loop to outside of it did the trick... But although I found a solution, I still do not understand why a previous insert, embedded in its own devoted transaction, caused this lock to be maintained to the next iteration and in another transaction!... So if you have tips : they are still welcome!
By the way, this post helped me a lot to find out which object was locked!
@Jean : thanks a lot for your many questions that helped me focus on the core cause!