Product Documentation

SQL Operations Guide

Previous Topic

Next Topic

fc_set_blockinglock( )

Sets the blocking lock strategy for FairCom DB SQL queries.

Description

By default, SQL connections block on locks. Situations can arise with mixing SQL operations with existing non-SQL applications. An application may acquire a non-blocking lock, and hold that lock for a length of time, thereby blocking an SQL query which might be expected to return sooner. An "abandoned" lock might indefinitely block the query. To avoid these situation, options are available to change the blocking lock behavior for SQL connections.

Parameters (mode)

mode SMALLINT

Set mode = 1 to enable blocking locks; set mode = 0 to disable blocking locks

A negative mode value enables a blocking lock with a defined timeout value (in seconds) from the absolute value of the parameter.

For example, a value of -2 with a SELECT query times out if it waits on an existing lock for more than 2 seconds.

The scope of the locking behavior is only within the current user session (that is, the current executing SQL thread); it is not global across other connections. Each SQL thread of operation can specify it's own unique locking behavior.

Examples

  • Disable blocking locks. Set blocking mode to 0 to cause a SQL statement to fail immediately when it encounters a record that another transaction has locked, updated, or deleted but not yet committed. It returns error -17042: CT - Could not obtain data record lock.

call fc_set_blockinglock( 0 );

  • Enable blocking locks. Set blocking mode to 1 to cause a SQL statement to block. The statement will not return when it encounters a record that another transaction has locked, updated, or deleted. It waits indefinitely for the lock to be released or an update or delete to be committed or rolled back. This is the default setting.

call fc_set_blockinglock( 1 );

  • Enable blocking locks with a timeout.. Set blocking mode to a negative integer, which is the maximum number of seconds you want a SQL statement to block before it fails with error -17156: CT - timeout. The statement will not return when it encounters a record that another transaction has locked, updated, or deleted but not yet committed. It waits until the lock is released, a pending update or delete is committed or rolled back, or the maximum number of seconds has expired. The following examples sets the timeout to 2 seconds.

call fc_set_blockinglock( -2 );

TOCIndex