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.
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.
SET TRANSACTION LOCK TIMEOUT timeout_spec [ ; ]
timeout_spec:
- INFINITE
- OFF
- unsigned_integer
- variable
vi $CUBRID/conf/cubrid.conf
…
lock_timeout_in_secs = 10
…
SET TRANSACTION LOCK TIMEOUT 10;
You can check the lock timeout set for the current application by using the GET TRANSACTION statement, or store this value in a variable.
GET TRANSACTION LOCK TIMEOUT [ { INTO | TO } variable ] [ ; ]
GET TRANSACTION LOCK TIMEOUT;
Result
===============
1.000000e+001
The following message is displayed if lock timeout occurs in a transaction that has been waiting for another transaction's lock to be released. To configuration level information to be displayed in details, 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.
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.