Record lock with editable ALV

[Jelena_Perfiljeva]

Our users want to maintain a Z table with some additional information for the order lines. Their vision is: some selection criteria (Sales Org, Delivery Date, etc.) will be entered and the whole list of order lines will appear. On that list they will enter additional data where needed (i.e. possibly not on all the lines) and then just click Save. The program will have to figure out what they added/changed/deleted and save the records in Z table accordingly.

This is not difficult to achieve with an editable ALV, but I got stuck at figuring out how to apply the database lock (ENQUEUE). Locking the whole table is not an option, so individual records will have to be locked. Also lock should be applied when the data is read. Unfortunately, the users don't want to limit selection criteria much, which potentially could lead to thousands of records being selected.

What would be the best approach to ensure the correct record lock without risking a lock table overflow? Should the number of records be limited? If so, what's the reasonable maximum number to allow - 100, 500?

I thought about other options (e.g. having users select the records explicitly and click Edit button to edit), but they would add more inconvenience to the users and at the same time are more difficult to program. Maybe there is a completely different solution?

[Jelena_Perfiljeva]

Our users want to maintain a Z table with some additional information for the order lines. Their vision is: some selection criteria (Sales Org, Delivery Date, etc.) will be entered and the whole list of order lines will appear. On that list they will enter additional data where needed (i.e. possibly not on all the lines) and then just click Save. The program will have to figure out what they added/changed/deleted and save the records in Z table accordingly.

This is not difficult to achieve with an editable ALV, but I got stuck at figuring out how to apply the database lock (ENQUEUE). Locking the whole table is not an option, so individual records will have to be locked. Also lock should be applied when the data is read. Unfortunately, the users don't want to limit selection criteria much, which potentially could lead to thousands of records being selected.

What would be the best approach to ensure the correct record lock without risking a lock table overflow? Should the number of records be limited? If so, what's the reasonable maximum number to allow - 100, 500?

I thought about other options (e.g. having users select the records explicitly and click Edit button to edit), but they would add more inconvenience to the users and at the same time are more difficult to program. Maybe there is a completely different solution?

[schneidertho]

Hi Jelena,

hhhmmm. Some thoughts... Assumption: you only update a Z-table

a) logically the lock should be the key of your table, so probably something like order + line item.

b) there are generic locks

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/cf/21eebf446011d189700000e8322d00/content.htm

but I somehow doubt that they will be of much help here.

c) maybe you have something like a planner who is responsible for a group of orders. then you might think of creating a lock object for planner and work with that. If you ensure that only your transaction writes to the Z-table, that might be an option

d) think or something like:

1. read w/o any locks

2. when the user makes a change in the table, set the lock and re-read the specific row from the DB 3. if there was a change to the data in between (you compare your local workarea / itab with what is coming from the DB), raise an error message

4. if you don't get the lock, you also raise an error message

5. otherwise proceed with the change. Disadvantage: if the user does many changes in the frontend before pressing e.g. ENTER, you can get a huge number of messages (so it makes only sense if parallel changes are unlikely)

Best regards

Thorsten

[Jelena_Perfiljeva]

Thorsten, thanks for a reply. Generic lock just locks the whole table, I believe, so this option didn't make it to semifinals.

The other suggestion is similar to where I was going with the Edit button (great minds think alike! ). But I also had the same concern about re-reading data and throwing dozens of error messages.

This is a possibility though. Maybe instead of throwing a bunch of messages I can add a 'traffic signal' to ALV to show the entries that were not updated. It's not perfect though - the users might not pay attention to any "red lights". Everyone is just expecting the program to work magically, sigh...

[Clemenss]

Hi Jelena,

lock table overflow will occur after more than 10 000 records - no danger. Just limit the # of records displayed to say 1000 and give a message "not all records displayed - use better selection".

Create a handler for event data_changed.

handle_data_changed

  FOR EVENT data_changed OF cl_gui_alv_grid

  IMPORTING er_data_changed

The handler method gets an object er_data_changed that has attribute table ->mt_mod_cells. This has the information about changed data - use this for individual locking.

Why lock should be applied when the data is read?

Anyway, this could be an interesting case for the use of 'optimistic' locks - introduces just recently by SAP.

Regards

Clemens

[Jelena_Perfiljeva]

Clemens, thanks for a reply. Normally if a record is opened for editing, it is locked at once, but the problem here is that potentially the users can change a lot of records. When reading the data, the program can't know which records and how many the users will update.

I think lock table size is driven by some parameter Basis maintains. Also the table is shared by many users, so I'm trying to be very cautious. But from the programming standpoint limiting the number of records is the easiest option (and I'm so lazy! ).

If lock is not applied when data is read, then someone else might be modifying the same data at the same time. So the users would end up with "what you see is NOT what you get". In this case, as Thorsten describes, the program would have to double-check the record against the database before saving to make sure that it hasn't changed in between.

I looked at the optimistic lock, but am not sure how it could be helpfull in this case. It really needs to be an exclusive lock (no one else should be able to modify the same records), but the problem here is with the record volume.

[Clemenss]

Hi Jelena,

the optimistic lock is not too easy to understand but I think it could help:

First when showing data in editable ALV, put optimistic locks on all records displayed (you can have any number of concurrent users create the same optimistic lock).

If optimistic lock can not be set because of existing exclusive lock, show the record in display mode with remark "currently locked by...").

After user changed any data and triggers SAVE, change all optimistic to exclusive locks. Where not possible, put remark "already changed by...". Directly after SAVE, release all locks.

User should have the option to refresh, that means load all data again (in current state) putting optimistic locks again...

If an other user changes the data (converting his or her optimistic lock) all other existing optimistic locks are removed. So you can not change the data because you can no longer change your optimistic to exclusive lock - in this case you can give information "already changed by other user" and ask to reload.

Check the help on How Optimistic Locks Work This concept should be really promising in your case.

Have a look at the help with Programming with Optimistic Locks The comments explain the concept quite well - better than I could.

Regards

Clemens

Message was edited by: Clemens Li

[Jelena_Perfiljeva]

Closing my old thread. I ended up adding 'Edit' button and the users have to select the records to update. The selected records are locked and also checked against the database if they were updated since reading (the users could have the screen open for long time and records could be changed by someone else meanwhile). Also I've added a "status" column that shows red light if there was an error in locking the record or if the data has already been updated (in which case the users just need to refresh the screen). This is to avoid throwing potentialy dozens of error messages and users clicking through them.

It's not perfect, but so far I've heard no complaints. Note that this transaction is accessed only by the few users.

Thanks again, everyone, for the replies!