Killing Database Transactions

Description

The cubrid killtran is used to check transactions or abort specific transaction. Only a DBA can execute this utility.

Syntax

cubrid killtran options database_name

options:

[{-i|--kill-transaction-index=}index] [--kill-user-name=id] [--kill-host-name=host] [--kill-program-name=program_name]

[{-p|--dba-password=}password]

[-d|--display-information]

[-q|--query-exec-info]

[-f|--force]

• cubrid : An integrated utility for the CUBRID service and database management
• killtran: A utility that manages transactions for a specified database
• options : Some options refer to killing specified transactions; others refer to outputting active transactions. If no option is specified, -d is specified by default so all transactions are displayed on the screen. -p A value followed by the -p option is a password of the DBA, and should be entered in the prompt.
• database_name: The name of database whose transactions are to be killed

Options

Displaying all transactions (no option)

cubrid killtran testdb 

 

Tran index      User name      Host name      Process id      Program name

-------------------------------------------------------------------------------

      1(+)            dba      myhost             664           cub_cas

      2(+)            dba      myhost            6700              csql

      3(+)            dba      myhost            2188           cub_cas

      4(+)            dba      myhost             696              csql

      5(+)         public      myhost            6944              csql

-------------------------------------------------------------------------------

Killing transactions in a specified index (-i or --kill-transation-index)

cubrid killtran -i 1 testdb

 

Ready to kill the following transactions:

 

Tran index      User name      Host name      Process id      Program name

-------------------------------------------------------------------------------

      1(+)            dba      myhost            4760              csql

-------------------------------------------------------------------------------

Do you wish to proceed ? (Y/N)y

Killing transaction associated with transaction index 1

Displaying all transactions (-d or --display)

cubrid killtran -d testdb

 

Tran index      User name      Host name      Process id      Program name

-------------------------------------------------------------------------------

      2(+)            dba      myhost            6700              csql

      3(+)            dba      myhost            2188           cub_cas

      4(+)            dba      myhost             696              csql

      5(+)         public      myhost            6944              csql

-------------------------------------------------------------------------------

Killing transactions for a specified OS user ID (--kill-user-name)

cubrid killtran --kill-user-name=os_user_id testdb

Killing transactions of a specified client host (--kill- host-name)

cubrid killtran --kill-host-name=myhost testdb

Killing transactions for a specified program (--kill-program-name)

cubrid killtran --kill-program-name=cub_cas testdb

Displaying query-running status of transactions (-q or --query-exec-info)

Displays the query-running status of transactions. The following shows to display the query-running status.

cubrid killtran --query-exec-info testdb

 

Tran index Process id Program name Query time Tran time  Wait for lock holder   SQL Text

---------------------------------------------------------------------------------------------

      1(+)       8536    b1_cub_cas_1    0.00      0.00  -1                     *** empty ***

      2(+)       8538    b1_cub_cas_3    0.00      0.00  -1                     *** empty ***

      3(+)       8537    b1_cub_cas_2    0.00      0.00  -1                     *** empty ***

      4(+)       8543    b1_cub_cas_4    1.80      1.80  3, 2, 1                update [ta] [ta] set [a]=5 wher

      5(+)       8264    b1_cub_cas_5    0.00      0.60  -1                     *** empty ***

      6(+)       8307    b1_cub_cas_6    0.00      0.00  -1                     select [a].[index_name], ( cast

      7(+)       8308    b1_cub_cas_7    0.00      0.20  -1                     select [a].[index_name], ( cast

      .....

 

---------------------------------------------------------------------------------------------

• Tran index : the index of transaction.
• Process id :  client’s process id
• Program name : program name of a client.
• Query time : total execution time for the running query (unit: second)
• Tran time : total run time for the current transaction (unit: second)
• Wait for lock holder : the list of transactions which own the lock when the current transaction is waiting for a lock.
• SQL Text : running  SQL text (maximum 30 characters)

After the toal information of transactions is displayed as above, the query which occurred the lock waiting is displayed as follows.

Tran index : 4

update [ta] [ta] set [a]=5 where (([ta].[a]> ?:0 ))

Tran index : 5, 6, 7

select [a].[index_name], ( cast(case when [a].[is_unique]=0 then 'NO' else 'YES' end as varchar(3))), ( cast(case when [a].[is_reverse]=0 then 'NO' else 'YES' end as varchar(3))), [a].[class_of].[class_name], [a].[key_count], ( cast(case when [a].[is_primary_key]=0 then 'NO' else 'YES' end as varchar(3))), ( cast(case when [a].[is_foreign_key]=0 then 'NO' else 'YES' end as varchar(3))), [b].[index_name], ( cast(case when [b].[is_unique]=0 then 'NO' else 'YES' end as varchar(3))), ( cast(case when [b].[is_reverse]=0 then 'NO' else 'YES' end as varchar(3))), [b].[class_of].[class_name], [b].[key_count], ( cast(case when [b].[is_primary_key]=0 then 'NO' else 'YES' end as varchar(3))), ( cast(case when [b].[is_foreign_key]=0 then 'NO' else 'YES' end as varchar(3))) from [_db_index] [a], [_db_index] [b] where (( CURRENT_USER ='DBA' or {[a].[class_of].[owner].[name]} subseteq (select set{ CURRENT_USER }+coalesce(sum(set{[t].[g].[name]}), set{}) from [db_user] [u], table([u].[groups]) [t] ([g]) where ([u].[name]= CURRENT_USER )) or {[a].[class_of]} subseteq (select sum(set{[au].[class_of]}) from [_db_auth] [au] where ({[name]} subseteq (select set{ CURRENT_USER }+coalesce(sum(set{[t].[g].[name]}), set{}) from [db_user] [u], table([u].[groups]) [t] ([g]) where ([u].[name]= CURRENT_USER )) and [au].[auth_type]= ?:0 ))) and ( CURRENT_USER ='DBA' or {[b].[class_of].[owner].[name]} subseteq (select set{ CURRENT_USER }+coalesce(sum(set{[t].[g].[name]}), set{}) from [db_user] [u], table([u].[groups]) [t] ([g]) where ([u].[name]= CURRENT_USER )) or {[b].[class_of]} subseteq (select sum(set{[au].[class_of]}) from [_db_auth] [au] where ({[name]} subseteq (select set{ CURRENT_USER }+coalesce(sum(set{[t].[g].[name]}), set{}) from [db_user] [u], table([u].[groups]) [t] ([g]) where ([u].[name]= CURRENT_USER )) and [au].[auth_type]= ?:1 ))))

The displayed queries are from the query plan cache, so they cannot be displayed if they are not plan-cached or INSERT statement which was run on the application.

For example, if you run below query,

UPDATE ta SET a=5 WHERE a > 0

Below query is displayed.

update [ta] [ta] set [a]=5 where (([ta].[a]> ?:0 ))

Omitting a prompt to check transactions to be stopped (-f or --force)

cubrid killtran -f -i 1 testdb