topic Re: Enqueue with multiple secondary tables in Application Development and Automation Discussions

Enqueue with multiple secondary tables

kurt_slater3 — Wed, 21 Oct 2009 15:14:59 GMT

I have an enqueue object that locks one primary table and several related secondary tables. When I call the FM using the key of the primary table I notice that I cannot open any secondary table record in change mode in an alternative transaction. It's as if each secondary table has been entirely locked.

I was expecting that only those records in any secondary table whose foreign key matches the key of the primary table would be locked.

How do I lock only the set of related records based on the key of the primary table without having to loop at each related secondary table record and locking these in turn? I mean, I know I could do this, but is that really the only way?

How does SAP manage this problem?

Re: Enqueue with multiple secondary tables

former_member191735 — Wed, 21 Oct 2009 20:44:46 GMT

If the tables are configured in a way that they are key each other - It should lock only those records that you passed as key.

Re: Enqueue with multiple secondary tables

kurt_slater3 — Wed, 21 Oct 2009 21:16:11 GMT

If by "configured" you mean I am using foreign key relationships, then yes. This is why I'm a little confused as to why the FM appears to be locking the entire table for all secondary tables.

Here's what I'm trying to do. Suppose I have 3 tables. There is a 1:n relationship between Table 1 -> Table 2 and Table 1 -> Table 3. Table 1 has 2 records; Table 2 and 3 have 3 records each.

Table 1

Table 2

Table 3

Tables 2 and 3 are defined as secondary tables in the lock object. So if I request a table lock for record A using the first lock object, then I should be able to request a record lock for ZB in Table 2 using another lock object, but this is not happening.

Re: Enqueue with multiple secondary tables

kurt_slater3 — Thu, 22 Oct 2009 21:25:04 GMT

Ok, so I think I solved this myself. There are three lock objects in this example. The first lock object is defined with Table 1 as the primary table and Table 2 & Table 3 as secondary tables. The second and third lock objects are defined to lock entries in Table 2 and Table 3 respectively.

If I want to use one FM call to lock all associated A records in Table 1, Table 2 and Table 3 then I pass an '*' for the remaining key field parameters.

Now another user can use a separate transaction to lock, say, GB in Table 3 without creating a lock conflict.

Re: Enqueue with multiple secondary tables

kurt_slater3 — Fri, 23 Oct 2009 18:57:35 GMT

After more testing, it appears the asterisk doesn't work after all! So back to square one.

Here's the problem again. I have a lock object with one primary table and several secondary tables. When I call the FM it appears to lock the entire contents of each secondary table - not just those records that are related by the foreign key which is what I want.

Do I have to loop at each secondary table where the foreign key = primary key of the primary table and lock each record individually??

Is there a better way?

Re: Enqueue with multiple secondary tables

Sandra_Rossi — Sat, 24 Oct 2009 21:31:52 GMT

Check the conditions -> [sap library - Lock objects - Conditions Required of Foreign Keys|http://help.sap.com/saphelp_nw2004s/helpdata/en/cf/21eecc446011d189700000e8322d00/frameset.htm]

Re: Enqueue with multiple secondary tables

kurt_slater3 — Mon, 26 Oct 2009 17:25:35 GMT

Hi Sandra

Thanks for the link. To the best of my knowledge, my lock object satisfies all 4 conditions.

Re: Enqueue with multiple secondary tables

Sandra_Rossi — Mon, 26 Oct 2009 18:01:24 GMT

I have made a little test on my 7.0 SP13 system, it works well. You should do the same, create objects from scratch:

- 2 tables (table 1 with mandt + key1 and table 2 with mandt + key2 + key1 + foreign key on key1 which points to table 1 key1 field),

- 2 lock objects from scratch (lock1 on table 1 and table 2, and lock2 on table 2 only; both lock objects have exclusive lock),

- 2 programs (prog1 which locks lock1, and prog2 which locks lock2)

Run them parallely, prog1 first (with wait) then prog2. This last should return an error unable to lock table 2.

If the locks WORK correctly, then try to complexify the test up to the original complexity, until you understand what is different between the 2 cases.

Edited by: Sandra Rossi on Oct 26, 2009 7:02 PM

Re: Enqueue with multiple secondary tables

kurt_slater3 — Mon, 26 Oct 2009 19:31:02 GMT

Sandra

Ok, so I created the tables and locks from scratch. The only thing I did differently was to run the enqueue FM's via SM37 so I don't have to create 2 new test programs. I am still getting the same results as before.

This is my dummy Table T1 (key = ZF1)

ZF1 | ZF2 | ZF3

0001 | A | AA

0002 | B | BB

0003 | C | CC

0004 | D | DD

0005 | E | EE

This is my dummy Table T2 (key = ZF4 + ZF1; FK = ZF1)

ZF4 | ZF1 | ZF5

2001 | 0001 | AA

2002 | 0001 | AB

2003 | 0002 | AC

2004 | 0001 | AD

2005 | 0003 | AE

2006 | 0004 | AF

2006 | 0005 | AG

2007 | 0005 | AH

There are also 2 enqueue FM's. E1 locks T1 & T2; E2 locks only T2. When I execute E1 where T1-ZF1 = 0001 I expect that the foreign key relationship in T2 would mean I also lock T2 where ZF4 = 2001, 2002 and 2004. Yet for example, when I execute E2 with T2-ZF4 = 2007 in another session this triggers a lock conflict.

The only way I can request a successful lock on T2 when I have already called E1 is to fully qualify all of the E2 FM parameters. For example, if I call the E2 where T2-ZF4 = 2007 and T2-ZF1 = 0005 then the request is Ok.

Re: Enqueue with multiple secondary tables

Sandra_Rossi — Tue, 27 Oct 2009 09:35:42 GMT


> There are also 2 enqueue FM's.  E1 locks T1 & T2; E2 locks only T2.  When I execute E1 where T1-ZF1 = 0001 I expect that the foreign key relationship in T2 would mean I also lock T2 where ZF4 = 2001, 2002 and 2004.  Yet for example, when I execute E2 with T2-ZF4 = 2007 in another session this triggers a lock conflict.
> 
> The only way I can request a successful lock on T2 when I have already called E1 is to fully qualify all of the E2 FM parameters.  For example, if I call the E2 where T2-ZF4 = 2007 and T2-ZF1 = 0005 then the request is Ok.

I understand what you mean, but SAP doesn't work like that. When you lock E1-ZF1 = 0001, it locks T1-ZF1 = 0001 and T2-ZF1 = 0001. If you try to lock any value in any other field then it will always succeed. My test was only about locking E1-ZF1 = 0001 then E2-ZF1 = 0001, the last fails because T2-ZF1 is already locked because of E1.

Re: Enqueue with multiple secondary tables

kurt_slater3 — Tue, 27 Oct 2009 13:11:30 GMT

Sandra

Thanks for your help. Happy ABAP-ing!