To whom it may concern ...
For any write access to a line of a database table the database sets a physical exclusive write lock on that line. This lock prevents any other write access to the line until it is released by a database commit or database rollback.
How can we see that in ABAP?
Rather simple, write a program:
DATA(wa) = VALUE scarr( carrid = 'XXX' ).
DELETE scarr FROM wa.
INSERT scarr FROM wa.
DO 100000000 TIMES.
ENDDO.
MESSAGE 'Done' TYPE 'I'.
Run it in one internal session. Open another internal session and run another program in parallel:
DATA(wa) = VALUE scarr( carrid = 'XXX' ).
DELETE scarr FROM wa.
INSERT scarr FROM wa.
MESSAGE 'Done' TYPE 'I'.
The program in session 2 finishes only when the first program has finished.
This is as expected. The second program tries to write to the same line as the first program and therefore is locked.
You must be aware that such locks do not only occur for Open SQL statements but for all write accesses to database tables. Clearly all writing native SQL statements are other candidates. But also other ABAP statements access database tables. Recently, I stumbled over EXPORT TO DATABASE.
Program in internal session 1:
EXPORT dummy = 'Dummy' TO DATABASE demo_indx_table(xx) ID 'XXX'.
DO 100000000 TIMES.
ENDDO.
MESSAGE 'Done' TYPE 'I'.
Program in internal session 2:
EXPORT dummy = 'Dummy' TO DATABASE demo_indx_table(xx) ID 'XXX'.
MESSAGE 'Done' TYPE 'I'.
As before, the program in session 1 locks the parallel execution of the program in session 2 because the same lines in the INDX-type database table are accessed. This can lead to deadlock situations, where you might have not expected it.
To prevent such long lasting locking or even deadlock situations, the write locks must be released as fast as possible. These means, there must be database commits or database rollbacks as soon as possible. In classical ABAP programming a lot of implicit database commits occur. E.g., each call of a dynpro screen leads to a rollout of the work process and a database commit. If there is only a short time between write access and database commit, you don't realize such locks in daily live. But if you have long running programs (as I have simulated above with the DO loop) without a database commit shortly after a write access, you can easily run into unwanted locking situations. In my recent case, I experienced deadlock situations during parallelized module tests with ABAP Unit: no screens -> no implicit database commits.
Therefore, as a rule: If there is the danger of parallel write accesses to one and the same line of a database table, avoid long running processes after a write access without having a database commit in between.
In the examples above, you could prevent the deadlock e.g. as follows:
DATA(wa) = VALUE scarr( carrid = 'XXX' ).
DELETE scarr FROM wa.
INSERT scarr FROM wa.
CALL FUNCTION 'DB_COMMIT'.
DO 100000000 TIMES.
ENDDO.
MESSAGE 'Done' TYPE 'I'.
or
EXPORT dummy = 'Dummy' TO DATABASE demo_indx_table(xx) ID 'XXX'.
CALL FUNCTION 'DB_COMMIT'.
DO 100000000 TIMES.
ENDDO.
MESSAGE 'Done' TYPE 'I'.
By calling function module DB_COMMIT in the programs of session1 an explicit database commit is triggered. The programs in session 2 are not locked any more during the long running remainders of the programs in session 1.
It is not a rule, to place such calls behind each write access. Of course, a good transaction model should prevent deadlocks in application programs anyway. But if you experience deadlocks in special situations, e.g. in helper programs that are not governed by a clean transaction model, such explicit database commits can be helpful.
If deadlocks occur during automated testing only, you can also consider the usage of lock objects during test runs. A test that involves a write access can use the SAP enqueue/dequeue mechanism to lock and release table lines and to react appropriately if a line is already locked.
