Delete user form table - need good advice

[Former Member]

Hi ,

I have tables 6 Z tables with user as foreign key ,for every table i create lock object

My question what is the best way to handle the delete scenario with the locking concept.

1. in case i want to delete all user data from all the table but just 1 and 2 are with data on the user do i need to lock all the tables ?(sounds not good idea)

2. Do read before delete and then use enque and dequeue in case sy-subrc = 0 after selection .

I am not sure with the performance aspects since the tables can have more then 100000 users

Other idea ?

Regards

Chris

Edited by: Chris Teb on Aug 9, 2009 2:46 PM

[Sandra_Rossi]

1. SAP lock concept is at the transaction level, not the table level. I mean, for example: if you only have one transaction to update all 6 tables, and one table is the superior table of the 5 others, then you just need to enqueue the superior table.

2.You can lock generically (for example all records) in one enqueue command.

[Former Member]

HI Sandra ,

What does it mean ,_SAP lock concept is at the transaction level, not the table level_

the tables are update on runtime with some ws ,

in case one of my method (the delete user are invoked ) i don't need to lock all the table just one ?

Can u give some example ?

Regards

Chris

[Sandra_Rossi]

It's difficult to answer more precisely without information on your tables, the various ways how they can be updated, etc.

With an example, say, a transaction for updating materials. There are many database tables for materials, but you may only use one lock object with the material number (another user can't change the material at the same time).

If you create a new transaction for updating one table of the material, you must not use another lock object than for the 1st transaction, otherwise 2 users would be able to access a same data of a material through the 2 transactions.

Now please give us more information on your tables, the various ways how they can be updated, etc.

[Former Member]

HI Sandra,

this is a part from the table i had.

X = key

Table 1

mandt
user     X
cdate
ctime

table 2

mandt
user X
application id  X
application type 
cdate
...

table 3

mandt
user  X
application id  X
application type X 
application name

cdate

....

table 4

mandt
user  X
application id  X
application type X 
application name  X
application version
....

and so on ...

table are relate with foreign key

for all of this table i create lock object and since i new to this topic (just read on the help and F1)

i want to consult with u on the solution .

for instance i want to delete user application version from table 4 ,i lock table 4 and delete the value.

but what i should do if i want to delete the specific user from all of the tables ?

how to handle this situation ? the simple way is to lock one table at a time but i am not sure that this is the best thing to do.

Regards

Chris

[Sandra_Rossi]

Using a lock object makes sense only if a user can be deleted from the 6 tables at the same time it is deleted from 1 table.

Even if this situation may happen, your database (RDBMS) should handle this situation because it locks the deleted record(s) so there would be a conflict. A SAP lock object is at a higher level: it keeps users from starting to maintain an object if another user has already started to maintain it.

[Former Member]

HI Sandra,

So what is the bottom line ,

if i want to delete user from all of the table i need to lock each table ?

and if i want to delete user entry from one table i use just one lock

Regards

Chris

[Sandra_Rossi]

sorry to not be clear enough. I hope this message is more helpful :

As you use web services, it is probably better that you work in stateless mode (*) (in short, it means that the context is deleted after the execution of a web service), it means that using lock objects here makes no sense. Perhaps locks should be handled by the external application. Moreover, your RDBMS will lock the records ensuring one of two concurrent deletions would fail.

(*) maybe web services always work in stateless mode, I don't know <-- added by: Sandra Rossi on Aug 10, 2009 11:19 AM

[Former Member]

HI Sandra

one thing that i forget to mention (important one ,sorry ) is that i have maintance view

for all the table so the admin can update some records manually .

Thanks !

Chris

[Sandra_Rossi]

In your web service, you may check E_TABLEE lock object (the one used by SM30), repeat for each table.

[Former Member]

HI Sandra,

Can u please provide some high level pseudo code ( or process ) how to handle this situation

i want to verify that understand your concept before i going the implementation .

Thank you !!!!

Chris

p.s.

Actually i think that i need to see if i can lock in the maintenance view every time one record

one line and not all the table .

Edited by: Chris Teb on Aug 10, 2009 12:42 PM

[Sandra_Rossi]

But SM30 locks the whole table! How could you know which record he maintains? Anyway, the admin will maintain this table rarely, won't he?

If you want to lock one record, you must create your own "sm30" (simple is to copy the dedicated function group and adapt lock algorithm).

But maybe it is simpler to not manage locks at all, don't you think? (as the admin should rarely update these tables... and in your application, is it important to prevent 2 updates at the same moment? the last will win that's all)

If you want to see a snippet, check sap library example : http://help.sap.com/saphelp_nw2004s/helpdata/en/af/22ab01dd0b11d1952000a0c929b3c3/frameset.htm