An example of Database deadlock in SAP table

[JerryWang]

• Test1 - program instance 2 waits program instance 1 to release resource


• Test2 - deadlock
  
  
  
  • step1 - execute zlock1 under debug mode
  
  
  • step2 - execute zlock2 under debug mode
  
  
  • step3 - ZLOCK1 tries to lock Z02
  
  
  • step4 - go back to ZLOCK2, execute line 12
  
  
  
  


• Further reading

This blog is inspired by an interview today. I would just like to refresh my knowledge gained in my university :smile:

We can get the concept of deadlock in wikipedia.

The picture below gives a common scenario which leads to deadlock.

So how can we write an ABAP program which will lead to deadlock? If we search for ABAP help, we can know it is possible that SELECT SINGLE FOR UPDATE can generate a deadlock.

So my test program is written based on SELECT FOR UPDATE.

The underlying database table has the following two entries:

Test1 - program instance 2 waits program instance 1 to release resource

I have written the following simple program:

Execute it as program instance 1:

then type /nSE38, execute this program once again as program instance 2. This time the program instance 2 hangs in line 10, waiting for the program instance 1 to release the lock.

Switch back to program instance1, click F8 to continue. Then the program instance 2 now is able to continue:

Test2 - deadlock

Write two different programs:

REPORT zlock1.

DATA: ls_prod TYPE zorder_header.

SELECT SINGLE FOR UPDATE * FROM zorder_header INTO ls_prod WHERE object_id = 'Z01'.

SELECT SINGLE FOR UPDATE * FROM zorder_header INTO ls_prod WHERE object_id = 'Z02'.

REPORT zlock2.

DATA: ls_prod TYPE zorder_header.

SELECT SINGLE FOR UPDATE * FROM zorder_header INTO ls_prod WHERE object_id = 'Z02'.

SELECT SINGLE FOR UPDATE * FROM zorder_header INTO ls_prod WHERE object_id = 'Z01'.

step1 - execute zlock1 under debug mode

step2 - execute zlock2 under debug mode

step3 - ZLOCK1 tries to lock Z02

step4 - go back to ZLOCK2, execute line 12

Result: runtime error in ZLOCK2, and ZLOCK1 now successfully locked both Z01 and Z02:

ST22 has given detailed information:

Further reading

You can find how to write a Java program to generate deadlock and how to detect deadlock in Java program via standard JDK tool jstack from this blog.