On 6/23/16 4:41 AM, Bergquist, Brett wrote:
So thoughts and some questions.
Looking at the derby network client code, the Connection is synchronized on
while a statement is being executed. For example from ClientConnection.java:
public ResultSet executeQuery(String sql) throws SQLException {
try
{
synchronized (connection_) {
if (agent_.loggingEnabled()) {
agent_.logWriter_.traceEntry(this, "executeQuery", sql);
}
ClientResultSet resultSet = executeQueryX(sql);
if (agent_.loggingEnabled()) {
agent_.logWriter_.traceExit(this, "executeQuery",
resultSet);
}
return resultSet;
}
}
catch ( SqlException se )
{
throw se.getSQLException();
}
}
So I don’t believe that it is really possible for the current driver
implementation to have multiple message sent simultaneously to the
DRDAConnThread associated with the connection. Even with multiple threads, it
does not seem possible to execute multiple independent statements. Testing
that I did implicitly verified this fact.
So recognizing this, one possible solution that does not break the
request/reply processing loop of the DRDAConnThread would be to add the ability
to “peek” into the DRDA request stream for the connection for a cancel request.
This would be doable by implementing new methods in DDMReader to be able to
peak for a specific message waiting on the connection. On the client side, the
“cancel” functionality would be implemented to send a message down the
connection out of band on the connection. Then on the server side, modify the
DDMReader to be able to “peek” for this message.
GenericStatementContext would have to be modified to have an interface that it
could call to see if the statement is canceled. One implementation of this
interface would be to “peek” into the DRDA request stream to see “cancel” is
present and if so cancel the statement.
Of course there is more to this but that is the general idea.
Following Rick’s thoughts, I see some complexities there as well. Somehow we
would need to get another connection while inside the Statement.cancel method.
I supposed we could try to “clone” the connection associated with statement or
try to track back to the DataSource and create a connection from there. But
what if there are no connections available? What if all connection are in use?
A second question is how does one identify a “statement” that exists on the
client side and associate that “statement” to one executing on the server? I
am trying to find how this can be identified. Note that this really should be
required for the solution I proposed above but from an implementation point of
view, the DRDAConnThread can only ever be executing one statement at a time, so
this can be potentially ignored. If someone can point me in the direction to
look for that, it will be appreciated.
I like the idea of a system procedure in general so that using this procedure
one could cancel a runaway statement from a tool, a monitoring application, etc.
Hi Brett,
At this point I'm just waving my hands. I think that some machinery
would need to be built in order to implement a killStatement procedure
which could be executed by the DBO:
1) PreparedStatements would need uuids.
2) We would need a system table function which returns the list of
executing statements, probably identified by the uuid in (1), the
statement text, and the user name.
I think that some security concerns could be addressed by restricting
the killStatement procedure and the showStatements table function to the
DBO (with ability to grant the privilege to other roles).
However, I haven't puzzled through how to securely allow
Statement.cancel() to invoke the killStatement() procedure over a
transient socket. How does the server verify that the out-of-band
message really originated with the client-side statement?
Thanks,
-Rick
On Jun 22, 2016, at 9:24 PM, Rick Hillegas<[email protected]> wrote:
On 6/22/16 6:05 PM, Bryan Pendleton wrote:
https://issues.apache.org/jira/browse/DERBY-3908
in network mode... seems to require that the DRDAConnThread be able
to receive a message from the network
client even while it is waiting for a statement to executed and the
result transported back to the client.
Hi Brett,
Ouch, I agree, this seems like a real challenge.
Even if the DRDAConnThread could be persuaded to do this,
I'm not sure there wouldn't be other complexities. For
instance, what if there are already some other messages "waiting in
the network" already?
Would it help to open a separate network connection instead?
That separate connection would then have to be able to indicate,
reliable, *which* connection it wanted to cancel, and then the
server, upon receiving that message, would have to hunt down that
connection and call cancel on it.
thanks,
bryan
I think that some such implementation is how IBM's DRDA client drivers
end up implementing Statement.cancel(). An out-of-band connection is
created in order to call a system procedure which looks up the
server-side session and cancels the statement. See
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM+i+Technology+Updates/page/QSYS2.FIND_AND_CANCEL_QSQSRVR_SQL+and+QSYS2.CANCEL_SQL+procedures
Hope this helps,
-Rick
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300
This e-mail and any attached document(s) is confidential and is intended only
for the review of the party to whom it is addressed. If you have received this
transmission in error, please notify the sender immediately and discard the
original message and any attachment(s).
