The default lock timeout is controlled by using the General.LockWaitTimeOut parameter, see General section. By default, lock timeout is set to 30 seconds.
In Advanced Replication setups, you might also want to set the default lock time for table-level locks with the General.TableLockWaitTimeout parameter.
The following lock timeout settings can be modified: for a specific table by using the LOCK TABLE WAIT statement, or for a specific session by using the SET LOCK TIMEOUT statement.
▪ Use the LOCK TABLE WAIT statement to set the lock timeout for a specific table, see LOCK TABLE.
Note The LOCK TABLE WAIT command is effective on disk-based tables only.
▪ Use the SET LOCK TIMEOUT statement to set the lock timeout for both table-level and row-level locks in a session, see SET ... TIMEOUT.
Note The SET LOCK TIMEOUT statement does not change the timeout for those tables for which the table-level timeout is set with the LOCK TABLE WAIT statement.
Setting lock timeout for optimistic tables
When you use the SELECT FOR UPDATE statement, the selected rows become locked even if the locking mode of the table is optimistic. If the statement is successful, you can execute consecutive updates and deletes on those rows without a fear that they would fail on a conflict.
Statements that are competing in concurrent transactions for the write access to the rows (such as SELECT FOR UPDATE, UPDATE or DELETE) will fail immediately if the lock cannot be granted. You can introduce lock waits by setting the optimistic lock timeout to a non-zero value by using the SET OPTIMISTIC LOCK TIMEOUT statement, see SET ... TIMEOUT.
When the timeout is set within a session, the SELECT FOR UPDATE statement will block (wait) until the timeout expires, or until the lock is released.
The timeout does not affect the DELETE or UPDATE statements that will return immediately – either with a success or a conflict error.
The SET OPTIMISTIC LOCK TIMEOUT statement takes effect immediately. If needed, the default behavior can be reset by setting the timeout value to zero.