Query marked as active in MON$STATEMENTS after cancelling the query [CORE1578] #1996
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovFix Version: 2.1 RC1 [ 10201 ] |
|
Commented by: @dyemanov After investigating this issue more, I tend to conclude that everything is as designed. An active state (RDB$STATE = 1) doesn't necessarily mean a statement running inside the engine. This state reflects also open cursors that are being fetched by the clients. And this state remains until the cursor is closed. In particular, even if the client fetches just the first record and stalls forever (keeping the cursor open), the statement will be marked as active. Practically, this means that a canceled fetch will be also marked as active until the client processes the error code and closes the cursor (releases the statement handle). This is exactly what happens with the local protocol or with an older fbclient -- the monitoring shows you RDB$STATE = 0, because ISQL immediately closes the cursor in the case of error. However, if you use v2.1 fbclient and TCP connection, you see RDB$STATE = 1, as reported in this ticket. This is due to the deferred packets delivery implemented in v2.1 -- the client doesn't send the op_dsql_free packet immediately, instead it's queued to be sent along with the next packet. If you leave the ISQL session intact, it doesn't happen and RDB$STATE remains active. If you issue commit or disconnect, RDB$STATE changes. In the real world, most applications would rollback in the case of cancellation, thus the problem will not be visible. I think a possible solution for this issue would be a more detailed states encoding, i.e. distinguish between really ACTIVE and STALLED/SUSPENDED, or to introduce a CANCELED state. But I'm afraid this has to wait until v2.5. Thomas, would you prefer this ticket to be closed as "won't fix" or kept open but scheduled for v2.5? |
Modified by: @dyemanovFix Version: 2.1 RC1 [ 10201 ] => |
Modified by: @pcisarWorkflow: jira [ 13393 ] => Firebird [ 14062 ] |
|
Commented by: Thomas Steinmaurer (tsteinmaurer) Feel free to close it as "won't fix", if you add a new one for a more detailed state encoding. ;-) |
|
Commented by: @dyemanov v2.5 has introduced the "stalled" state mentioned in this ticket, thus I resolve it. |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.5 Beta 2 [ 10300 ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovQA Status: No test |
Submitted by: Thomas Steinmaurer (tsteinmaurer)
Query cancelling seems to work fine by invoking a DELETE statement on
MON$STATEMENTS, but I don't get the expected result when re-querying the
MON$STATEMENTS table, because the statement is still listed there,
although possibly with another MON$STATEMENT_ID than the one which has
been used when executing the DELETE statement.
Test case:
SET TERM ^^ ;
CREATE PROCEDURE P_ENDLESSLOOP (
TO_VALUE Integer DEFAULT 1000000)
returns (
I Integer)
AS
begin
i = 1;
while (i <= to_value) do
begin
suspend;
i = i + 1;
end
end
^^
SET TERM ; ^^
COMMIT;
Running the selectable SP with e.g.:
SELECT * FROM P_ENDLESSLOOP;
In another isql session, I cancel the query with a DELETE statement on
MON$STATEMENTS. I get the follow cancelling message in the first isql
session:
ERROR:
Statement failed, SQLCODE = -901
operation was cancelled
Now, I re-query the MON$STATEMENTS table in another isql session. I
still see the cancelled statement with MON$STATE = 1, but possibly with
another MON$STATEMENT_ID. This guy will disappear when I commit the
transaction in the isql session with the cancelled query.
The text was updated successfully, but these errors were encountered: