Question: Sequential multiple DS Request execution and in the same DB Session

[pankaj.jain]

Hi Isomorphic,

We are currently using a DMI to fire multiple DSRequests within one fetch!

public class ExampleDMI {
    public DSResponse fetch(DSRequest dsRequest) {
        DSRequest req1 = new DSRequest();
        DSRequest req2 = new DSRequest();
        .
        .
        .
        req1.execute();
        req2.execute();
        return dsRequest.execute();
    }
}

Here, the intention is to execute these 3 DSRequests in the same DB Session/Transaction in the exact same sequence!
Q1. The expectation is that on the server side, all the requests execute sequentially. Is this understanding right?
Q2. Is there a guaranteed way to ensure that these 3 run in the same DB Session or even a same DB Transaction?

Thanks!

[Blama]

Hi pankaj.jain,

Q1: Yes, server processing is sequentially.
Q2: Yes, just use the DSRequest constructor with RPCManager. (This is true for all client-started requests, where someone clicked a link in the browser. For all other use cases, see the StandaloneDataSourceUsage-docs).

Also, read the docs for the different TransactionPolicies, which need to be set beforehand, best in your server.properties. Reasonable defaults for an OLTP application are ANY_CHANGE and ALL, IMHO.
Also, enable DEBUG logging for com.isomorphic.sql.SQLTransaction and related classes in order to see more Transaction information in the server logs.

Best regards
Blama

[pankaj.jain]

Hi @Blama,
Thanks for the response.
I think DSTransaction is better suited to our case currently.
However, what I do notice is that even after firing 'complete()' method, the next call to the same DMI results in the same DB Session being reused! Even 'freeAllResouces()' doesn't seem to end the DB session!

Is there a way to force close the DB Session via dsRequest?
Also, when are DB Sessions actually created new and when are they re-used? I am unable to find this in the Docs, though I may have overlooked it.

Thanks!

[Blama]

Are you talking about a DB Session or a DB Transaction? If session, why?

[Isomorphic]

We're likewise confused what you mean. As far as the SQLConnection, you would expect it to be reused - that's just pooling.

[pankaj.jain]

Well, I meant DB Session.
The reason for that being our use case.

Our use case is something of this kind:

public class ExampleDMI {
    public DSResponse fetch(DSRequest dsRequest) {
        try {
            DSRequest req1 = new DSRequest();
            DSRequest req2 = new DSRequest();
            .
            .
            .
            req1.execute();                                                    -> create temporary table temp1 using data from multiple tables
            req2.execute();                                                    -> create temporary table temp2 using data from temp1
            return dsRequest.execute();                                -> insert into a persistent table data from temp2
        }
        finally {
            endSession();                                                      -> This would drop the tables temp1 and temp2 as their lifespan is only for the db session!
        }
    }
}

We collect data from multiple tables and store in a Temporary Table, and then transfer this data from temp to a persistent table after performing some aggregation over it.
When multiple clients fire requests, in some cases, the temporary table is visible to other users as well (as its life is for the DB Session), which kind of messes up our approach since we use specific table name to create the temp table each time!
Thus, in simple terms, if we could force the session to end, we could to some extent control the auto-dropping of the table!

Either way, for now, we have found a workaround to this by using unique temp tables names and dropping the tables in the end, but it would be good to know if we have any control over ending the session if needed.
Also, would be good to know if there is a max time-limit or anything for a DB session after which it ends, or if it is alive as long as the user's web session.

Thanks!

[Isomorphic]

The "session" you keep referring to is, again, the same thing as a SQLConnection. SQLConnections are pooled so they will stay alive potentially a long time (and all of this is correct and desirable behavior).

For this reason, you should be dropping your temp tables when you are done with them.

In case it helps, this Stack overflow agrees on all of the above points.

[pankaj.jain]

Sounds right!
So we will stick to our current approach of dropping them.
Thanks!

[pankaj.jain]

Hi Isomorphic,

We, once again, are in a similar situation as this post, and have a question.
As per your last comment:

SQLConnections are pooled so they will stay alive potentially a long time (and all of this is correct and desirable behavior).

This makes sense. However, would the same connection be re-used for multiple web sessions?
Let me elaborate this a little:
We set up certain DB parameters in a few specific DB session requests (DSRequest is used to set up session level hints in the database). This is applicable only to certain cases, and stays 'alive' only as long as the DB session is alive.
A new user logging into our webapp should not be setting this db hint (unless it passes certain check).
Thus, if the session is re-used between users, it could create a problem for us!

[Isomorphic]

Yes, SQLConnections are pooled across multiple HTTP sessions. This is the normal, in fact near-universal behavior of connection pooling in HTTP environments. If it wasn't, you'd have tremendous and unacceptable connection churn as users started and abandoned sessions.

If you really need to set hints that are per-user and permanently affect a SQLConnection, then you need to undo the hinting before releasing the connection back to the pool.

[pankaj.jain]

Thanks for the quick response!