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 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 will wait indefinitely until the transaction lock is allowed. Therefore, the user can change this value depending on the transaction pattern of the application. 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 stored by the variable.
Example 1

vi $CUBRID/conf/cubrid.conf

lock_timeout_in_secs = 10

Example 2

SET TRANSACTION LOCK TIMEOUT 10;

Checking the Lock Timeout
Description

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

Syntax

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

Example

GET TRANSACTION LOCK TIMEOUT;

         Result

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

  1.000000e+001

Checking and Handling Lock Timeout Error Message

The following message is displayed if lock timeout occurs in a transaction that has been waiting for another transaction's lock to be released.

Your transaction (index 2, user1@host1|9808) timed out waiting on IX_LOCK lock on class tbl. You are waiting for

user(s) user1@host1|csql(9807), user1@host1|csql(9805) to finish.

  • Your transaction(index 2 …): This means that the index of the transaction that was rolled back due to timeout while waiting for the lock is 2. 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.
  • (… user1@host1|9808): 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.
  • IX_LOCK: This means the exclusive lock set on the object to perform data update. For details, see Lock Mode Types And Compatibility.
  • user1@host1|csql(9807), user1@host1|csql(9805): Another transactions waiting for termination to lock IX_LOCK

That is, the above lock error message can be interpreted as meaning that "Because another client is holding X_LOCK on a specific row 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 row 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.

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.

Also, you can forcefully stop problematic transactions by using the cubrid killtran utility. For details, see Killing Transactions.