[HIVE-6460] Need new "show" functionality for transactions

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.13.0
• Component/s: SQL

Release Note

A new command SHOW TRANSACTIONS has been added. This will show a list of all currently open and aborted transactions in the system. It is intended for use by administrators. It will print out:
• transaction id
• transaction state
• user who started the transaction
• machine where it was started

A new command SHOW COMPACTIONS has been added. This will provide a list of all tables and partitions currently being compacted or scheduled for compaction. For details on compaction see Basic Design below. This will print:
• database name
• table name
• partition name (if this is a partitioned table)
• whether it is a major or minor compaction
• the state the compaction is in, which can be:
⁃ 'initiated' - waiting in the queue to be done [could use dashes instead of commas for these 3 state definitions] [I never understand when to use dashes versus commas.]
⁃ 'working' - being compacted
⁃ 'ready for cleaning' - the compaction has been done and the old files are scheduled to be cleaned
• thread id of the worker thread doing the compaction (only if in working state)
• the time at which the compaction started (only if in working or ready for cleaning state)

The SHOW LOCKS command has been altered to provide information about the new locks associated with transactions. If you are using the ZooKeeper or in-memory lock managers you will notice no difference in the output of this command. If you are using transactions then SHOW LOCKS will return:
• database name
• table name
• partition (if this is a partitioned table)
• the state the lock is in, which can be:
⁃ 'acquired' - the requestor holds the lock
⁃ 'waiting' - the requestor is waiting for the lock
⁃ 'aborted' - the lock has timed out but not yet been cleaned up
• the type of lock, which can be:
⁃ 'exclusive' - no one else can hold the lock at the same time (obtained mostly by DDL operations such as drop table)
⁃ 'shared_read’ - any number of other shared_read locks can lock the same resource at the same time (obtained by reads; confusingly, an insert operation also obtains a shared_read lock)
⁃ 'shared_write' - any number of shared_read locks can lock the same resource at the same time, but no other shared_write locks are allowed (obtained by update and delete)
• id of the transaction this lock is associated with, if there is one
• last time the holder of this lock sent a heartbeat indicating it was still alive
• the time the lock was acquired, if it has been acquired
• Hive user who requested the lock
• host the user is running on

A new option has been added to ALTER TABLE to request a compaction of a table or partition. In general users do not need to request compactions, as the system will detect the need for them and initiate the compaction. However, if compaction is turned off for a table or a user wants to compact the table at a time the system would not choose to, ALTER TABLE can be used to initiate the compaction. The syntax is:

ALTER TABLE tablename [PARTITION (partition_key = 'partition_value' [, ...])] COMPACT 'compaction_type'

where compaction_type can be MAJOR or MINOR. This will enqueue a request for compaction and return. To watch the progress of the compaction the user can use SHOW COMPACTIONS.

• Assignee: Alan Gates
• Reporter: Alan Gates
• Created: 18/Feb/14 21:52
• Updated: 01/Oct/19 22:06
• Resolved: 25/Mar/14 22:15

Description

With the addition of transactions and compactions for delta files some new "show" commands are required.

• "show transactions" to show currently open or aborted transactions
• "show compactions" to show currently waiting or running compactions
• "show locks" needs to work with the new db style of locks as well.