Wait time

From InterBase



To acquire lockable resources, InterBase transaction lock can wait indefinitely, wait an specified period of time, or do not wait and return an error immediately. When a transaction holds a lock on a resource at a level incompatible with the requested lock level, this resource is inaccessible to other transactions. lockable resources can be tables, rows, or transaction entities.


This is the SQL syntax to specify a lock resolution mode:


WAIT implies wait indefinitely until a resource lock is acquired.

To specify a wait period use an optional WAIT clause in seconds. This is the time a transaction waits for a lock on a resource:


An isc_lock_timeout error code returns if the lock on the resource cannot be acquired during the wait period.

For example, consider attempting to truncate a table. Table truncate attempts to acquire an exclusive lock on the target table and referencing tables that have a foreign key constraint on the target table. It is desirable to specify a wait time for the transaction if other transactions are using the table actively.

SQL> set transaction wait 10 seconds;
SQL> truncate table salary_history;
Statement failed, SQLCODE = -901
lock time-out on wait transaction
-unsuccessful metadata update
-object SALARY_HISTORY is in use

There is a new transaction parameter block (TPB) parameter called isc_tpb_wait_time for use with InterBase transaction APIs: isc_start_transaction(), isc_reconnect_transaction(), and isc_start_multiple(). It is followed by the literal "4" denoting a byte count and four bytes in little endian format denoting the wait period in seconds. Here are two examples specifying a 30 second and 300 second (5 minute) wait period, respectively:

isc_tpb_wait_time, 4, 30, 0, 0, 0
isc_tpb_wait_time, 4, 44, 1, 0, 0

There is an InterClient/JDBC extension API method for class interbase.interclient.Connection: setLockResolution( int mode, int waitTime ) The existing method setLockResolution( int mode ) is equivalent to setLockResolution( int mode, 0 ).

/* Set transaction timeout to 1 minute */
Driver driver = interbase.interclient.Driver();
Connection connection = driver.connect(url, properties);
(interbase.interclient.Connection connection).setLockResolution(LOCK_RESOLUTION_WAIT, 60);

It is expected that FireDAC, IBX and ODBC frameworks will provide low-level integrated support for the feature.

Requirements and Constraints

  • The WAIT period is a positive integer between 1 and 32,767, inclusive. This is the equivalent of about 9 hours.
  • Underlying remote and local protocols pass a 32-bit integer so that this limit can be increased without modifying the protocols.
  • The feature is available through Dynamic SQL but not Static (Embedded) SQL.
  • The feature is available through InterClient/JDBC API.
  • The feature may not be visible as a transaction property by FireDAC, IBX or ODBC frameworks, but should be available as pass-through DSQL.

Migration issues

  • The WAIT optional clause is not recognized by SQL parsers in InterBase versions older than 2017.
  • The isc_tpb_wait_time TPB parameter is not recognized at the API level by InterBase versions older than 2017.

Advance To: