Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

HANA Deadlock and Out Of Memory

Hi all,

While monitoring our HANA One server, we noticed that memory consumption went uncontrollably upward. We stopped all of our applications that were pushing data to HANA database, but that didn't change anything. The memory consumption still kept going up.

Having had problems with indexserver before, we opened the indexserver's log and checked what was going on, and we saw this:

[15548]{207448}[406931110] 2013-04-25 07:23:37.818999 e Lock         WaitGraph.cc(00504) : Deadlock detected: Deadlock detected while executing transaction (TRANSACTION_ID=173, UPDATE_TRANSACTION_ID=406931110):

This is not an HDB error. User or application may cause a deadlock due to incorrect access sequences on shared objects.

current tx CONNECTION_ID=7448, CLIENT_HOST=ip-10-101-27-234.ec2.internal, CLIENT_IP=10.101.27.234, CLIENT_PIP=18844, TRANSACTION_ID=173, TRANSACTION_ADDRESS=0x7f5354df3000, TRANSACTION_TYPE=USER, TRANSACTION_STATE=ACTIVE, TRANSACTION_CACHING_FLAG=0, TRANSACTION_ISOLATION_LEVEL=RC, THREAD_ID=15548, UPDATE_TRANSACTION_ID=406931110, QUERY_BUFFER_SIZE=35432, NUM_OPENED_CURSORS=0, SQL_STATEMENT="UPSERT "SOGAMO"."PLAYER_GAME" SELECT "ID_1", "GAME_ID", MIN("LOGIN_DATETIME"), MAX("LAST_ACTIVE_DATETIME") FROM "_SYS_BIC"."sogamo-web/AT_SESSION_DETAILED" GROUP BY "ID_1", "GAME_ID""

  LOCK_TYPE=RECORD_LOCK, LOCK_MODE=EXCLUSIVE CONTAINER_ID=0, TABLE_ID=162815, TABLE_NAME=SOGAMO:PLAYER_GAME, RECORD_ID=[CS:OID=0x0000013b, PARTID=0x0, OFFSET=0x8001d728]

blocked by CONNECTION_ID=7151, CLIENT_HOST=ip-10-204-101-225.ec2.internal, CLIENT_IP=10.204.101.225, CLIENT_PIP=7767, TRANSACTION_ID=123, TRANSACTION_ADDRESS=0x7f53aad58000, TRANSACTION_TYPE=USER, TRANSACTION_STATE=ACTIVE, TRANSACTION_CACHING_FLAG=0, TRANSACTION_ISOLATION_LEVEL=RC, THREAD_ID=15583, UPDATE_TRANSACTION_ID=406931117, QUERY_BUFFER_SIZE=35432, NUM_OPENED_CURSORS=0, SQL_STATEMENT="UPSERT "SOGAMO"."PLAYER_GAME" SELECT "ID_1", "GAME_ID", MIN("LOGIN_DATETIME"), MAX("LAST_ACTIVE_DATETIME") FROM "_SYS_BIC"."sogamo-web/AT_SESSION_DETAILED" GROUP BY "ID_1", "GAME_ID""

  LOCK_TYPE=RECORD_LOCK, LOCK_MODE=EXCLUSIVE CONTAINER_ID=0, TABLE_ID=162815, TABLE_NAME=SOGAMO:PLAYER_GAME, RECORD_ID=[CS:OID=0x0000013b, PARTID=0x0, OFFSET=0x80001a33]

blocked by current transaction (TRANSACTION_ID=173, see above for the details)

HANA was in a deadlock, because two threads were trying to hit the same table, PLAYER_GAME, at the same time. The SQL statement in question was an UPSERT with SELECT, which can fetch and upsert quite a considerable number of rows.

While we will probably break this UPSERT down into smaller transactions moving forward, what troubled us was that after we stopped all processes hitting that particular table, HANA's memory consumption continued going up. Eventually, other processes were cut off from HANA as well with the following errors:

SAP DBTech JDBC: [2048]: column store error: [29020] exception 29020: PersistenceLayer/DeltaLog.cpp:1214 ltt::exception caught while operating on I SOGAMO WORKER_INDIVIDUAL_ERROR D $delta$ exception 1000002: ltt/impl/memory.cpp:91 Out of memory ; $size$=4194304; $name$=Page; $type$=pool; $inuse_count$=578574; $allocated_size$=37980340224


[129]: transaction rolled back by an internal error: Memory allocation failed


Finally, we couldn't connect to HANA at all and had to restart HANA. Thankfully, HANA started up just fine this time. We have had worse experience where HANA crashed (due to out of memory too, but due to other reasons) and we actually couldn't start it back up and had to recover from a backup.


When we encounter table deadlock problem, is there a way to recover from a deadlock gracefully rather than go out of memory, crash and restart the database? We have no problem avoiding a long UPSERT, we can learn from errors, but we fear the risk of losing data while in the process of doing so.


I have attached the log file of our HANA One's index server, as well as the OOM (out of memory) crash dump.


Thank you and look forward to your reply.

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question