8.6.6 Setting the database timeout

(1) Timeout in HiRDB

You can set the following three types of timeout values in HiRDB:

• Unlock waiting timeout

  Set this timeout for preventing deadlocks and extended exclusive use of the resources. Set the timeout value in the pd_lck_wait_timeout parameter of the common system definition of the HiRDB server. The timeout value set here is the maximum time for monitoring the exclusion waiting time. The exclusion waiting time is the time period from when the exclusion request is in a queue until it is released from the queue.

  When this timeout occurs, the Application Server and HiRDB execute the following operations:

  • An exception (java.sql.SQLException) is notified to the user application.

  • The HiRDB message KFPA11770-I, indicating that timeout has occurred, or the message KFPA11911-E, indicating that a deadlock has occurred, are output.

  • The active transactions are rolled back.

  • After terminating the business method of the user application, the connection is closed and deleted from the connection pool.

• Response timeout

  Set this timeout for detecting a failure on the server-side of the database system.

  Set the timeout value in the environment variable PDCWAITTIME of the HiRDB client. The timeout period set here is the maximum waiting time of the HiRDB client from when it makes a request to the HiRDB server until a response is returned. Specify this timeout value in cases such as monitoring the time for a long-term SQL.

  When this timeout occurs, the Application Server and HiRDB execute the following operations:

  • An exception (java.sql.SQLException) is notified to the user application.

  • The HiRDB message KFPA11732-E, indicating that timeout has occurred, is output.

  • The active transactions are rolled back.

  • The connection is closed and deleted from the connection pool.

• Request interval timeout

  Set this timeout for detecting a failure on the client-side of the database system.

  Set the timeout value in the environment variable PDSWAITTIME of the HiRDB client. The timeout value set here is the maximum waiting time of the HiRDB server from when the HiRDB server returns a response to the request from the HiRDB client until the next request is sent by the HiRDB client. The time is monitored for the transactions that are being processed (from the start of SQL execution until commit or rollback). The timeout value is reset when the request from the HiRDB client reaches the HiRDB server.

  When this timeout occurs, the Application Server and HiRDB execute the following operations:

  • An exception (java.sql.SQLException) is notified to the user application.

  • The HiRDB message KFPA11723-E, indicating that timeout has occurred, is output.

  • The active transactions are rolled back.

  • The connection is closed and deleted from the connection pool.

• Connection timeout in the non-block mode

  This timeout is used to detect a LAN error quickly.

  Specify the timeout value in the HiRDB client environment variable PDNBLOCKWAITTIME. The timeout value specified here monitors the connection between the HiRDB server and the HiRDB client.

  Application Server and HiRDB execute the following operation when this timeout is detected:

  • An exception (java.sql.SQLException) is reported to the user application.

To Page Top

(2) Timeout in Oracle (for local transaction)

When you use the local transaction in Oracle, you can set up the following timeout values:

• Query timeout

  Query timeout is a timeout that you can set up only when you use the Oracle JDBC Thin Driver as a JDBC driver. You use the setQueryTimeout method of the java.sql.Statement interface to specify a timeout value. For details on the notes when Oracle JDBC Thin Driver is used to connect to Oracle, see 3.6.6 Preconditions and notes on connecting to Oracle in the uCosminexus Application Server Common Container Functionality Guide.

If a deadlock occurs, the Oracle message ORA-00060 is output. After the Application Server terminates the business method of the user application, the connection is closed and is deleted from the connection pool.

To Page Top

(3) Timeout in Oracle (for global transaction)

You can set up the following timeout values when the global transaction is being used in Oracle:

• Query timeout

  For details about the query timeout, see the explanation related to the query timeout in (2) Timeout in Oracle (for local transaction).

• Unlock waiting timeout

  You set up this timeout for preventing deadlocks and extended exclusive use of resources. Set up the timeout value in the DISTRIBUTED_LOCK_TIMEOUT parameter of the Oracle server definition. When this timeout occurs, the Application Server and Oracle execute the following operations:

  • An exception (java.sql.SQLException) is reported to the user application.

  • The Oracle message ORA-02049, indicating that the timeout has occurred or the Oracle message ORA-00060 indicating that a deadlock has occurred, is output.

  • After terminating the business method for the user application, the connection is closed and deleted from the connection pool.

  The active transactions are not rolled back.

To Page Top

(4) Timeout in SQL Server

You can set the following two types of timeout values in the SQL Server:

• Timeout in acquiring memory

  Set this timeout for monitoring the waiting time for acquiring the memory for SQL execution. Set the timeout value in the query wait parameter of the environment settings option of the SQL Server. The timeout period set here is the waiting time for acquiring the memory, when the memory required for executing SQL is not obtained.

  When the timeout occurs, the Application Server and the SQL Server execute the following operations:

  • An exception (java.sql.SQLException) is notified to the user application.

  • The SQL Server message 8645, indicating that timeout has occurred, is output.

  • The active transactions are rolled back.

  • After terminating the business method of the user application, the connection is closed and deleted from the connection pool.

• Unlock waiting timeout

  Set this timeout for preventing deadlock and extended exclusive use of the resources. Set the timeout value by executing the SET LOCK_TIMEOUT statement of the SQL Server. The timeout period set here is the waiting time until the lock is released.

  When timeout occurs, the Application Server and the SQL Server execute the following operations:

  • An exception (java.sql.SQLException) is notified to the user application.

  • The SQL Server message 1222, indicating that timeout has occurred, is output.

  • After terminating the business method of the user application, the connection is closed and deleted from the connection pool.

  When a deadlock occurs in the SQL Server, the Application Server and the SQL Server execute the following operations:

  • An exception (java.sql.SQLException) is notified to the user application.

  • The SQL Server message 1205, indicating that a deadlock has occurred, is output.

  • The active transactions are rolled back.

  • After terminating the business method of the user application, the connection is closed and deleted from the connection pool.

To Page Top

(5) Timeout in XDM/RD E2

You can set the following five types of timeout values in XDM/RD E2:

• Unlock waiting timeout

  Set this timeout for preventing deadlock and extended exclusive use of resources. Set the timeout value in the TIMER parameter of the system option definition of XDM/BASE. The timeout value set here is the maximum time for monitoring the exclusion waiting time. The exclusion waiting time is the time from which the exclusion request is pending in a queue until it is released from the queue.

  When timeout occurs, the Application Server and XDM/RD E2 execute the following operations:

  • An exception (java.sql.SQLException) is notified to the J2EE application.

  • The message JXZ1911I of XDM/RD E2, indicating that a timeout or deadlock has occurred, is output.

  • The active transactions are rolled back.

  • After terminating the business method of the J2EE application, the connection is closed and deleted from the connection pool.

  The operations executed when a deadlock occurs in XDM/RD E2 are similar to the operations that are executed when a timeout occurs in unlock waiting timeout.

• CPU timeout during SQL execution

  Set this timeout for monitoring the CPU processing time during SQL execution. Set the timeout value in the SQLCTIME parameter of the control statement for invoking control space or the control statement for invoking server space of the DB connection server. The timeout period set here is the maximum time for monitoring the CPU processing time during the execution of one SQL. Specify this timeout value when monitoring the time for the long-term SQL.

  When timeout occurs, the Application Server and XDM/RD E2 execute the following operations:

  • An exception (java.sql.SQLException) is notified to the J2EE application.

  • A message indicating that timeout has occurred is output. The message differs depending on the value specified in the VPARTOPTION parameter of the control statement for invoking control space of the DB connection server. When the value is not specified or when ERROR NORMAL is specified, the HiRDB client message KFPA11723-E is output. If a value other than that given above is specified, the XDM/RD E2 message JXZ1874I is output.

  • The active transactions are rolled back.

  • If VPARTOPTION parameter is not specified or if ERROR NORMAL is specified, the connection is closed and deleted from the connection pool. If a value other than that given above is specified, the connection is closed and deleted from the connection pool when the database is accessed for the first time after the timeout occurs, or after the business method of the J2EE application terminates.

• SQL execution timeout

  Set this timeout for monitoring the time elapsed during SQL execution. Set the timeout value in the SQLETIME parameter of the control statement for invoking control space or the control statement for invoking server space of the DB connection server. The timeout period set here is the maximum time for monitoring the time elapsed in the execution of one SQL. Specify this timeout value when monitoring the time for the long-term SQL.

  When timeout occurs, the Application Server and XDM/RD E2 execute the following operations:

  • An exception (java.sql.SQLException) is notified to the J2EE application.

  • The message indicating that timeout has occurred is output. The message differs depending on the value specified in the VPARTOPTION parameter of the control statement for invoking control space of the DB connection server. When the value is not specified or when ERROR NORMAL or ERROR SQLCTIME is specified, the HiRDB client message KFPA11723-E is output. If a value other than that given above is specified, the message JXZ1874I of XDM/RD E2 is output.

  • The active transactions are rolled back.

  • When VPARTOPTION parameter is not specified, or if ERROR NORMAL or ERROR SQLCTIME is specified, the connection is closed and deleted from the connection pool. If a value other than that given above is specified, the connection is closed and deleted from the connection pool when the database is accessed for the first time after the timeout occurs, or after the business method of the J2EE application terminates.

• Transaction timeout

  Set this timeout for monitoring the elapsed time from the starting of the transaction. Set the timeout value in the SVETIME parameter of the control statement for invoking control space or the control statement for invoking server space of the DB connection server. The timeout value set here is the maximum time for monitoring the elapsed time of the transaction.

  When timeout occurs, the Application Server and XDM/RD E2 execute the following operations. If an SQL is being executed when timeout occurs, it is executed at that time. If the SQL is not being executed, it is executed during the SQL execution after the timeout occurs.

  • An exception (java.sql.SQLException) is notified to the J2EE application.

  • The message indicating that timeout has occurred is output. The message differs depending on the value specified in the VPARTOPTION parameter of the control statement for invoking control space of the DB connection server. When the value is not specified, or when ERROR NORMAL or ERROR SQLCTIME is specified, the HiRDB client message KFPA11723-E is output. If a value other than that given above is specified, the XDM/RD E2 message JXZ1874I is output.

  • The active transactions are rolled back.

  • If the VPARTOPTION parameter is not specified, or if ERROR NORMAL or ERROR SQLCTIME is specified, the Application Server and XDM/RD E2 the connection is closed and deleted from the connection pool. If a value other than that given above is specified, the connection is closed and deleted from the connection pool when the database is accessed for the first time after the timeout occurs, or after the business method of the J2EE application terminates.

• Response timeout

  Set this timeout for detecting failures on the server-side of the database system.

  Set the timeout value in the environment variable PDCWAITTIME of the HiRDB client. The timeout value set here is the maximum waiting time of the HiRDB client from when it makes a request to the XDM/RD E2 server until the response is returned. Specify this timeout value when monitoring the time for the long-term SQL.

  When timeout occurs, the Application Server and HiRDB execute the following operations:

  • An exception (java.sql.SQLException) is notified to the J2EE application.

  • The HiRDB client message KFPA11732-E, indicating that timeout has occurred, is output.

  • The active transactions are rolled back.

  • The connection is closed and deleted from the connection pool.

To Page Top

(6) Processing of the user application when timeout or deadlock occurs during database access

When an exception occurs in the user application due to a deadlock or timeout of the database, roll back the active transactions and suspend the processing of the business method. Check and, if necessary, revise the tuning parameters explained in this section.

To Page Top