Server timeouts

Programmer Guide : Timeout controls : Server timeouts

Server timeouts

Timeouts related to the database server are introduced in this topic.

SQL statement execution timeout

The server can control the amount of time spent on the execution of one SQL statement. When the time expires, the server terminates the statements and returns a corresponding error code. This timeout applies to the following calls (ODBC 3.5 specifications):

▪SQLExecute()

▪SQLExecDirect()

▪SQLPrepare()

▪SQLForeignKeys()

▪SQLColumns()

▪SQLProcedureColumns()

▪SQLSpecialColumns()

▪SQLStatistics()

▪SQLPrimaryKeys()

▪SQLProcedures()

▪SQLTables()

▪SQLTablePrivileges()

▪SQLColumnPrivileges()

▪SQLGetTypeInfo()

The timeout also applies to the corresponding JDBC calls.

INI parameter	Overridden with SQL	Driver	Connection string
	SET STATEMENT MAXTIME minutes	ODBC: SQL_ATTR_QUERY_TIMEOUT (in seconds) SQL_ATTR_QUERY_TIMEOUT_MS (in milliseconds, non-standard) JDBC: statement.setQueryTimeout() (in seconds) Note: ▪In version prior to V7.0 Fix Pack 4, the timeout unit in SMA connections is milliseconds. ▪As of V7.0 Fix Pack 4, the timeout unit in SMA connections is seconds.

Timeout error code and message:

HYT00, Timeout expired

See also:

SOLID Server Error 14518:

Connection to the server is broken, connection lost.

SOLID Server Error 14529: The operation timed out.

Lock wait timeout

The lock wait timeout specifies the time in seconds (or milliseconds) that the engine waits for a lock to be released. When the timeout interval is reached, solidDB^® terminates the timed-out transaction. The default value is 30 seconds.

Lock wait timeout is used in deadlock resolution. In that case, the oldest transaction participating in a deadlock is aborted.

INI parameter	Overridden with SQL	Driver	Connection string
[General] LockWaitTimeOut=seconds	SET LOCK TIMEOUT {seconds \| milliseconds MS}

Timeout error code and message:

SOLID Database Error 10006:
Concurrency conflict, two transactions updated or deleted the same row.

Optimistic lock timeout

The optimistic lock timeout specifies the time in seconds (or milliseconds) for optimistic lock timeout. Optimistic lock is an additional lock that can be enacted in order to ensure that SELECT FOR UPDATE will always lead to successful updates in the optimistic concurrency mode. The default is zero; no optimistic lock is used, and a transaction may be aborted after each statement as a result of early transaction validation. When the timeout is set to a non-zero value, SELECT FOR UPDATE will wait until the lock is obtained, or it is timed-out and aborted. When set, the timeout affects also all DELETE and UPDATE statements.

INI parameter	Overridden with SQL	Driver	Connection string
	SET OPTIMISTIC LOCK TIMEOUT {seconds \| milliseconds MS}

Timeout error code and message:

SOLID Database Error 10006:
Concurrency conflict, two transactions updated or deleted the same row.

Table lock wait timeout

Occasionally, the transaction will acquire an exclusive lock to a table. This may be result of a lock escalation, an attempt to execute the ALTER TABLE statement, or as a side effect of some advanced replication commands. If there is a table-level conflict, this setting provides the transaction’s wait period until the exclusive or shared lock is released. The unit is seconds, the default value is 30 seconds, and the parameter access mode is read/write.

To be more specific, table level locks are used when the PESSIMISTIC keyword is explicitly provided in the following commands:

IMPORT SUBSCRIPTIONMESSAGE message_name EXECUTE (only with NO EXECUTE option) MESSAGE message_name FORWARD MESSAGE message_name GET REPLY DROP SUBSCRIPTION.

INI parameter	Overridden with SQL	Driver	Connection string
[General] TableLockWaitTimeout=seconds

Timeout error code and message:

SOLID Database Error 10006:
Concurrency conflict, two transactions updated or deleted the same row.

Transaction idle timeout

The transaction idle timeout specifies the time in minutes after an idle transaction is aborted; a negative or zero value means infinite. The default value is 120 minutes.

INI parameter	Overridden with SQL	Driver	Connection string
[Srv] AbortTimeOut

Timeout error code and message:

SOLID Database Error 10026: Transaction is timed out.

Connection idle timeout

The connection idle timeout specifies the continuous idle time in minutes (or seconds/milliseconds in a statement) after which a connection is dropped (by the server); negative or zero value indicates an infinite value. The default value is 480 minutes.

INI parameter	Overridden with SQL	Driver	Connection string
[Srv] ConnectTimeOut=minutes	SET IDLE TIMEOUT {seconds \| milliseconds MS}	JDBC: Connection property (non-standard): "solid_idle_timeout_min"

Timeout error code and message:

SOLID Communication Error 21308: Connection is broken (protocol read/write operation failed with code internal code).