question about dead lock

[Former Member]

I have two programs : zpztest03,zpztest04.

if we run them at the same time, they can run successfully.

but i just think it meets the deadlock condition.

if we change zpztest04 to zpztest05, we just add a line 'commit work' . the program zpztest05 will be dead. why ????

*********zpztest03**************
Report  ZPZTEST03.
tables: SFLIGHT.
DATA: SFLIGHT_WA TYPE SFLIGHT, SBOOK_WA TYPE SBOOK.
do 100000 times.

  UPDATE SFLIGHT
    SET
      SEATSOCC = SEATSOCC + 1
    WHERE
      CARRID   = 'LH '      AND
      CONNID   = '0400'     AND
      FLDATE   = '19950228'.

    UPDATE SBOOK
    SET
      smoker = ''
    WHERE
      CARRID   = 'LH '      AND
      CONNID   = '0400'     AND
      FLDATE   = '19950228'.
enddo.

******zpztest04****************
Report  ZPZTEST04.
tables: SFLIGHT.
DATA: SFLIGHT_WA TYPE SFLIGHT, SBOOK_WA TYPE SBOOK.
do 100000 times.
*commit work.
    UPDATE SBOOK
    SET
      smoker = ''
    WHERE
      CARRID   = 'LH '      AND
      CONNID   = '0400'     AND
      FLDATE   = '19950228'.
  UPDATE SFLIGHT
    SET
      SEATSOCC = SEATSOCC + 1
    WHERE
      CARRID   = 'LH '      AND
      CONNID   = '0400'     AND
      FLDATE   = '19950228'.

enddo.

**zpztest05****
Report  ZPZTEST06.
tables: SFLIGHT.
DATA: SFLIGHT_WA TYPE SFLIGHT, SBOOK_WA TYPE SBOOK.
do 100000 times.
commit work.
    UPDATE SBOOK
    SET
      smoker = ''
    WHERE
      CARRID   = 'LH '      AND
      CONNID   = '0400'     AND
      FLDATE   = '19950228'.
  UPDATE SFLIGHT
    SET
      SEATSOCC = SEATSOCC + 1
    WHERE
      CARRID   = 'LH '      AND
      CONNID   = '0400'     AND
      FLDATE   = '19950228'.

enddo.

[Former Member]

All three programs are running?

Its because data is also used by another program

[Former Member]

the program zpztest03 and zpztest04 run at the same time. it will be ok. but if we run zpztest03 and zpztest05. one of them will be dead. the difference between zpztest04 and zpztest05 is only one line : commit work.

I just want to know why the both program will be ok when zpztest03 and zpztest04 run at the same time and why the program will be dead when zpztest03 and zpztest05 run at the same time.

[Former Member]

Hi,

You have used commit work inside loop, which for every iteration of

loop concludes sap luw and updates database for every iteration.

Where as if u exclude commit work statement then implicite commit work

triggers at the end of dialog step and all your changes in database are updated

in a single database luw.

I think if you write commit work outside loop explicitly, then also your program

will run fine.

For detailed explanation of Database Update techniques in SAP refere

SAP LUW (Logical Unit of Work),

CALL FUNCTION IN UPDATE TASK,

PERFORM ON COMMIT

Regards,

Vishal

[Former Member]

Hi Zhen,

The programs use UPDATE keyword which means they ae using database locks not transactional locks. Database locks are the locks that are to be handled(implemented by database) and they are not recognized by SAP in the programs are running in separate sessions. Hence SAP is submitting queries to database which is will take care of priority hence no problem when running programs 03 and 04.

Incase of program 05 comit work is been used. Whenever update request is sent to database they keep filling stacks(normally 8-13 per instance) which will be written to database as scheduled(normally every 5 min.

Every time comit work is used the stack is changed irrespective of it is filled the stack or not. If you have used more than 13 commit work with in 5 min then there is no stack available to push data as stacks are not released till 5 min, this will cause program to block all processors and end up in a dead lock.

If you run the program 05 alone even then it blocks all SAP sessions and goes in a dead lock.

Regards

Hussain

[Former Member]

your answer is very helpful.

well, after I excute the code:

UPDATE SBOOK

SET

smoker = ''

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'.

UPDATE SFLIGHT

SET

SEATSOCC = SEATSOCC + 1

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'.

are these two table locked ? in fact only sflight is locked,and any one can update sbook. why ? and at the same time, i just want to know how to lock more than one table at the same time.