Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Transaction Timeout

CUBRID provides the  lock timeout feature, which sets the waiting time for the lock until the transaction lock setting is allowed.

If the lock is allowed within the lock timeout, CUBRID rolls back the transaction and outputs an error message when the timeout has passed. If a transaction deadlock occurs within the lock timeout, CUBRID rolls back the transaction whose waiting time is closest to the timeout.

Setting the Lock Timeout
Description

The system parameter lock_timeout_in_secs in the $CUBRID/conf/cubrid.conf file or the SET TRANSACTION statement sets the timeout (in seconds) during which the application client will wait for the lock and rolls back the transaction and outputs an error message when the specified time has passed. The default value of the lock_timeout_in_secs parameter is -1, which means the application client will wait indefinitely until the transaction lock is allowed. Therefore, the user can change this value depending on the transaction pattern of the application client. If the lock timeout value has been set to 0, an error message will be displayed as soon as a lock occurs.

Syntax

SET TRANSACTION LOCK TIMEOUT timeout_spec [ ; ]

timeout_spec:

- INFINITE

- OFF

- unsigned_integer

- variable

  • INFINITE : Wait indefinitely until the transaction lock is allowed. Has the same effect as setting the system parameter lock_timeout_in_secsto -1.
  • OFF : Do not wait for the lock, but roll back the transaction and display an error message. Has the same effect as setting the system parameter lock_timeout_in_secsto 0.
  • unsigned_integer : Set in seconds. Wait for the transaction lock for the specified time period.  
  • variable : A variable can be specified. Wait for the transaction lock for the value saved by the variable.
Example 1

vi $CUBRID/conf/cubrid.conf

lock_timeout_in_secs = 10

Example 2

csql> SET TRANSACTION LOCK TIMEOUT 10;

csql> ;xr 

Checking the Lock Timeout
Description

You can check the lock timeout set for the current application client by using the GET TRANSACTION statement, or save this value in a variable.

Syntax

GET TRANSACTION LOCK TIMEOUT [ { INTO | TO } variable ] [ ; ]

Example

csql> GET TRANSACTION LOCK TIMEOUT;

csql> ;xr

 

=== <Result of GET LOCK TIMEOUT Command in Line 1> ===

 

         Result

===============

 

  1.000000e+001


Lock Timeout Error Message

Displays the following message if lock timeout occurs in a transaction that was waiting for another transaction's lock to be released. To increase the level of detail of the information displayed in the lock timeout error message, see the description of lock_timeout_message_type in Concurrency/Lock Parameters .

ERROR: Your transaction (index 3, cub_user@cdbs006.cub|15668) timed out waiting on    X_LOCK lock on instance 0|636|34 of class participant. You are waiting for user(s)  to finish.

  • Your transaction(index 3 …) : This means that the index of the transaction that was rolled back due to timeout while waiting for the lock is 3. The transaction index is a number that is sequentially assigned when the client connects to the database server. You can also check this number by executing the cubrid lockdb utility.
  • (…cub_user@cdbs006.cub|15668) : cub_user is the login ID of the client and the part after @ is the name of the host where the client was running. The part after| is the process ID (PID) of the client.
  • X_LOCK : This means the exclusive lock set on the object to perform data update. For details, see Lock Mode Types And Compatibility.
  • Instance 0|636|34 of class participant : This means that X_LOCK has been set on a certain tuple in the table named participant and the OID (unique ID assigned to the given object) of the tuple is 0|636|34.

That is, the above lock error message can be interpreted as meaning that "Because another client is holding X_LOCK on a certain tuple in the participant table, transaction 3 which running on the host cdbs006.cub waited for the lock and was rolled back as the timeout has passed." 

If you want to check the lock information of the transaction specified in the error message, you can do so by using the cubrid lockdb utility to search for the OID value (ex: 0|636|34) of a specific tuple where the X_LOCK is set currently to find the transaction ID currently holding the lock, the client program name and the process ID (PID). For details, see Checking Lock Status. You can also check the transaction lock information in the CUBRID Manager. For details, see Database Lock Information.

You can organize the transactions by checking uncommitted queries through the SQL log after checking the transaction lock information in the manner described above. For information on checking the SQL log, see Broker Log or SQL Log.

Also, you can force problematic transactions to quit by using the cubrid killtran utility. For details, see Killing Transactions.