Forum Stats

  • 3,734,464 Users
  • 2,246,976 Discussions
  • 7,857,305 Comments

Discussions

jdbc.statementTimeout not compliant on Linux

PeeZu
PeeZu Member Posts: 21 Blue Ribbon

Hello,

We have identified issue on our side where jdbc.statementTimeout doesn't works as expected.

Technology stack

  • ORDS 19.4.6
  • Tomcat 9
  • Oracle 12.2
  • JRE 1.8
  • Linux

Context

We want to prevent our API service to consume database resource more that necessary, for that our requirement is to have services that should respond in less than 60 seconds. All calls that exceed 60 seconds is returned with a HTTP status 504 (timeout).

For that, we have set the HTTP timeout at web application server level and it works as excepted.

Even the HTTP timeout stop the client request, ORDS call continue to works until the SQL statement is completed or, according the documentation, until the value of jdbc.statementTimeout is reached.

In order to avoid long running ORDS call and recover the connection, we want to reduce the default statementTimeout to 90 seconds, I guess the purpose of this parameter was in that sense.

Issue

This parameter doesn't fully work as expected as ORDS wait the end of the SQL statement and not the jdbc.statementTimeout value before sending the 555 http status with the error message:

SQL Error Code: No more data to read from socket

Step to reproduce

  • Create ORDS resource that emulate long query: (grant the DBMS_LOCK package to your current schema)
DECLARE

  l_module   VARCHAR2(100) := 'demo.example';
  l_resource VARCHAR2(100);

BEGIN

  -- delete all REST services and disable REST services for this schema
  ords.drop_rest_for_schema();

  -- enable REST for this schema
  ords.enable_schema();

  -- delete module if exist
  ords.delete_module(p_module_name => l_module);

  -- define module
  ords.define_module(p_module_name    => l_module,
                     p_base_path      => '/',
                     p_items_per_page => 0,
                     p_status         => 'PUBLISHED',
                     p_comments       => NULL);


  -------------------------------------------------------------------------------------------------
  l_resource := 'sleep';

  -- define resource template    
  ords.define_template(p_module_name => l_module,
                       p_pattern     => l_resource,
                       p_priority    => 0,
                       p_etag_type   => 'NONE',
                       p_etag_query  => NULL,
                       p_comments    => NULL);


  -- define resource handler
  ords.define_handler(p_module_name    => l_module,
                      p_pattern        => l_resource,
                      p_method         => 'GET',
                      p_source_type    => ords.source_type_plsql,
                      p_source         => q'[DECLARE                                              
                                            BEGIN                                              
                                              dbms_lock.sleep(NVL(:seconds, 1));
                                            END;
                                            ]',
                      p_items_per_page => 0,
                      p_mimes_allowed  => NULL,
                      p_comments       => NULL);


  COMMIT;
END;
/
  • Configure ORDS to reach your database and add the jdbc.statementTimeout
<entry key="jdbc.statementTimeout">10</entry>
  • Run ORDS and invoke the resource to validate: /ords/your-schema/sleep
The result should be HTTP 200
  • Now invoke again the resource with a sleep value: /ords/your-schema/sleep?seconds=30

Expected:

HTTP 555 returned in 10 seconds (value of the jdbc.statementTimeout)

Got:

HTTP 555 returned in 30 seconds (value of the end of statment)

Important note: The behavior is correct if I run ORDS on Windows instead of Linux platform with the same technology stack.

HTTP 555 returned in 10 second with SQL Error Code: ORA-01013: user requested cancel of current operation

Answers

Sign In or Register to comment.