Wait time

Introduction

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.

Usage

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

SET TRANSACTION {[NO] WAIT};

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:

SET TRANSACTION WAIT [<number> [SECONDS]];

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
SQL>

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:

SHOW SQL DIALECT