[HANA] Trouble shooting - MVCC Issue

MVCC Problem Identification

• Transactions blocking garbage collection은 다음으로부터 발생 가능
  • Long-running or unclosed cursor
  • Long-running trancation with isolation mode 'serializable' or 'repeatable read'
  • Hanging threads
• MVCC 관련 문제가 있는지 확인 위해 active version 수를 확인 해야 함

select *
from   m_mvcc_tables 
where  name in ('NUM_VERSIONS','MAX_VERSIONS_PER_RECORD','TABLE_ID_OF_MAX_NUM_VERSIONS');

     -> NUM_VERSIONS and MAX_VERSIONS_PER_RECORD > 8,000,000 : 문제로 인식, 시스템 전체에 대한 slowdown 발생

     -> TABLE_ID_OF_MAX_NUM_VERSIONS 는 SYS.TABLES와 join을 통해서 추가 확인 필요

 

 

 

Analysis of MVCC Issue

• garbage collection을 blocking하는 transaction 찾는 SQL문 

select top1
       connection_id
     , transaction_id
     , update_transaction_id
     , primary_transaction_id
     , transaction_type
     , isolation_level
from   m_transactions 
where  min_mvcc_snapshot_timestamp > 0
order  by min_mvcc_snapshot_timestamp desc

select top 1
     , transaction_id
     , update_transaction_id
     , primary_transaction_id
     , transaction_type
     , isolation_level
from   m_transactions
where  mvcc_snapshot_timestamp = (select min(value) 
                                  from   m_mvcc_tables 
                                  where  name = 'MIN_SHAPSHOT_TS'
                                  order  by min_mvcc_snapshot_timestamp desc) 

  -> TRANSACTION_TYPE = 'USER TRANSACTION' 인 경우 connection ID 확인 가능

                                              = 'EXTERNAL TRANSACTION' 인 경우 다음 Query를 이용해서 connection ID 찾아야 함

SELECT t.connection_id AS "Kill this connection id"
     , t.transaction_id AS "Belonging to user transaction id"
     , e.transaction_id AS "To get rid of external transaction id" 
FROM   m_transactions t JOIN m_transactions e 
       ON e.primary_transaction_id = t.transaction_id AND e.volume_id = t.volume_id
WHERE  e.transaction_type = 'EXTERNAL TRANSACTION' 
   and e.transaction_id = <GC blocker transaction id>;

 

 

 

Solution of MVCC Issue

• Cantact the user to stop his activity
• Cancel the statment/cancel the internal transaction
• Cancel the connection
• Kill the client application