Friday, 8 September 2023

Deadlock issue Analysis

Introduction:


The problem is the Database dead lock that occurred on every week at particular day and time in the production environment. Due to this database deadlock issue, the system would halt the background process, the users would not able to login into the system and would not able to process any document creation or change in the production system. The database table lock entries are being created spontaneously without releasing lock entries. For instance, within the short span of time more than 600k entries are created. So, there is no system resources free to process the rest of the operation/process.

Analysis:


What is Deadlock?

Deadlock is a database system having two or more transactions running same time, when each processing is waiting for the data that is being locked by some other transaction. A deadlock can be indicated by a cyclic waiting.

Example:

Process 1 locks resource A.

Process 2 locks resource B.

Process 1 requests resource A exclusively (-> lock) and waits for process 2 to end its transaction.

Process 2 requests resource B exclusively (-> lock) and waits for process 1 to end its transaction.

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification
Deadlock picture

For example, resources are table records, which are locked by a modification or a Select-for-Update operation.

Process to check the deadlock issue:

Firstly, check the deadlock logs then check SM12 where you can find the lock entries. In our case, the EINA, MARA tables got locked and were not released. Due to the deadlock that occurred in the lock entries were being created but not released within short span of time the lock entries are created 600k.

Where to check deadlock in sap:

The deadlock issues can be checked and analysed in the transaction SM21 system log and analysis. SM21 provides monitoring the event log, identifying the issue and troubleshooting the issue. Based on our analysis, we can take appropriate actions on the findings, like resolving errors, investigating security breaches or optimizing system performance.

We can filter the problems based on priority- Red (error), Yellow (Warning).

For example:

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification
SM21 initial screen

Once you enter the date and other relevant data in this screen, execute to see the details.

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification
SM21 system log

Then you select the deadlock row to get the more details.

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification
SM21 details

Due to this update request failure, there are some other issues that happened in the system like number range skip in the VBRK table. And thus, the update termination happened, and mail was sent to SAP inbox. These details can be seen in transaction SW01

From the SM21 analysis, update request failure happened. To know the further details about the update error we need to go to transaction SM13.

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification
SM12 tcode

Now execute to get the details screen. Then select error records and press update header option from header to get more details.

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification
SM13 detail

Check further to identify the place of issue occurrence from the above picture.

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification

From this above picture, we can identify the issue started from function module => ME_UPDATE_DOCUMENT, inside this function module which in turn calls another function module which updates the EINE table. 

CALL FUNCTION ‘ME_DB_UPDATE_INFORECORDS’ with table EINE_UB.

Deadlock issue Analysis, SAP HANA, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Prep, SAP HANA Preparation, SAP HANA Certification
FM

So the problem is FM “‘ME_DB_UPDATE_INFORECORDS”. This ME_UPDATE_DOCUMENT used 3 Z program which are scheduled to run in small time gap. By further analysis, program 1 which started at 16:30 pm and ran around 2 hours and in the mean time, another program 2 which was scheduled at 17:00 pm ran parallelly. In the middle of a program, say program 3 was scheduled. All these three programs call ME_UPDATE_DOCUMENT.

No comments:

Post a Comment