Home Timeout on select query from external service after making an update on the same table
Reply: 0

Timeout on select query from external service after making an update on the same table

user2345
1#
user2345 Published in June 20, 2018, 10:45 pm

This is such a weird situation. I have Microsoft SQL Server database, app written in java (queries run using javax.persistence.Query) and external service written in C#.

I have a procedure that makes update on table MyTable. There are only two update statements in this procedure:

UPDATE
    MyTable
SET
    status = dbo.getStat(id)
WHERE
    EXISTS
    (
        ...
    )

UPDATE
    MyTable
SET
    status = 3
WHERE
    status = 2 
    AND EXISTS
    (
        ...
    )

EXISTS part is the same for both statements. In my java app I use following code to launch this procedure:

Query query = em.createNativeQuery(recources.getString("updateStatuses"));
query.setHint("toplink.refresh", "true");
query.setParameter(1, sessId);
query.executeUpdate();

Under updateStatuses I have following sql:

EXEC updateStatusesP ?

After that I select records from this table if they have status equal to 3:

SELECT 
    id 
FROM
    MyTable
WHERE 
    status = 3
    AND EXISTS
    (
        ...
    )

I iterate through ids and I make a call to external service written in C# which modifies data in db.

The thing is that currently I'm getting a timeout on a select query launched from this external service. It's a query selecting data from MyTable.

If I remove first update statement from my updateStatusesP procedure then it works fine (no timeout). If I modify first query to include "status = " condition in WHERE clause then it also works fine. But without it I get this timeout.

There is a trigger and index on this table. I removed them to check if maybe there is something happening there. No changes.

If I make a direct call to this external service (via postman) then I get correct response. Only when calling it from application code: procedure, select query to select ids and then call to external service - I'm getting a response from this service (with info about timeout) and I can see in db monitoring tool that it didn't go pass first select query launched inside this service.

I don't understand what is happening. I tried putting those updates into transaction and committing it at the end of the procedure but it didn't help. Any ideas?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.315503 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO