Issue with Database Commit

[sachin_yadav3]

Hello All,

I have two include in my user exit of the sales order.

In one include I am doing some validation on the Order quantity of the sales order depending upon the order quantity in one of my Z table. If every validation is OK then I am exporting this order quantity to memory ID.

In the second include I am importing this Order Quantity and updating this Quantity in my database in my same Z table.

If I process one order at a time then everything is working fine but when I try to create two sales order at the same time then only second order is getting validated and order quantity is getting updated whereas for the first order no validation is performed and also order quantity is not getting updated in the Z table.

I really do not know is this issue related to database commit or something else.

Kindly help.

Regards

Sachin yadav

[former_member404244]

Hi,

I don't understand why you are importing the quantity in other other include and update the ztable.I believe you can do it in the same include..I might be wrong.

I think for two sales orders processing at the same time, you might be getting two different records in any temporary SAP internal table like XVBAK .. So instead of sending one variable, you can send internal table to memory-id and read the internal table from memory id, this way you can get multiple records..Please give a try and see.

Regards,

Nagaraj

[sachin_yadav3]

Hello Nagaraj,

Thanks for your reply.

I am sending the internal table to memory id not the variable as it contains the order Quantity of the line itmes.

Also even if I use to same include then while fetching the data from this Ztable to perform the validation on the Quantity I will not get the correct data because I am processing the two orders at the same time and both the code are executed at the same time so the Quantities are not updated in the Ztable.

So even if I write the code in same include then also only one order quantity (i.e second order Quantity) will be updated in the Ztable while processing the two sales orders at same time.

Kindly help.

Regards

Sachin Yadav

[former_member404244]

Hi,

Can you please share your code, to have a look..Also which userexit you are using "USEREXIT_SAVE_DOCUMENT".For each oder the exit will be called right.what ever data is there it takes that and updates the ztable right.Both the orders should be updated to ztable.

Regards,

Nagaraj

[Former Member]

Hi Sachin Yadav,

Along with the code it might be of more help if you provide the Z Table structure too with details like the key fields in the table.

I am processing the two orders at the same time and both the code are executed at the same time so the Quantities are not updated in the Ztable.

Also, didn't quite get what you meant by the above sentence, what/how do you mean by "Processing two orders at the same time and both code are updated at the same time"...provide more information on the actual scenario.

Regards,

Chen

[sachin_yadav3]

Hello,

My Include are present in the USEREXIT_SAVE_DOCUMENT.

The Table which I am updating is ZDSQTA.

The fields of the tables are as below.

ZMANDT Client (Key)

ZVKORG Sales Organization (Key)

ZSCYC1 Sales Cycle 1 (Start Period) (Key)

ZSCYC2 Sales Cycle 2 (End Period) (Key)

ZBZIRK Sales district (Key)

ZVKBUR Sales Office (Key)

ZMATNR Material Number (Key)

ZQTANO Quota Number (Key)

ZGRQTY Allocated Quantity. (Non Key)

ZALQTY Allocated Quantity. (Non Key)

ZSHQTY Shipped Quantity. (Non Key)

ZORQTY Ordered Quantity. (Non Key)

Please find the code as below :-

Moderator message - Please respect the 5,000 character maximum when posting. Post only the relevant portions of code

Plz revert back in case you need further details.

Regards

Sachin

Edited by: Rob Burbank on Jun 8, 2011 3:22 PM

[sachin_yadav3]

Nagaraj,

If you want i can send you code plz share your id.

[sachin_yadav3]

Hello Chen,

By processing two order at same time mean I am saving two sales order at the almost same time with same details.

When I debug these two order by putting breakpoint and saving at the same time I realizes that the data is selected from the Z table in both the orders at same time.

Where as as per my requirement data should be selected for the second order after processing is done for the first order.

Regards

Sachin

[former_member404244]

Hi,

Please don't paste entire code only paste the code which is needed (example ztable update, ztable data retrieval). Also paste in read able format.

Regards,

Nagaraj

[matt]

Nagaraj,
> 
> If you want i can send you code  plz share your id.

Do not exchange email addresses on these forums

[sachin_yadav3]

Nagaraj,

any suggestion?

[Former Member]

Hi Sachin,

Still not sure on what you are trying to achieve,

My Include are present in the USEREXIT_SAVE_DOCUMENT

Do you mean both of your includes are present in the above exit? If yes, then why do you have your code in two separate includes, why not have it in one, also why do you want to export and import the values?

Or if you mean only one of the include is called in save_doc where is the other include?

Where as as per my requirement data should be selected for the second order after processing is done for the first order.

How do you know that the data is not processes for the first order, i ask this because the key fields in your Z table is not order specific?

ZMANDT Client (Key) 
ZVKORG Sales Organization (Key) 
ZSCYC1 Sales Cycle 1 (Start Period) (Key) 
ZSCYC2 Sales Cycle 2 (End Period) (Key) 
ZBZIRK Sales district (Key) 
ZVKBUR Sales Office (Key) 
ZMATNR Material Number (Key) 
ZQTANO Quota Number (Key) 
ZGRQTY Allocated Quantity. (Non Key) 
ZALQTY Allocated Quantity. (Non Key) 
ZSHQTY Shipped Quantity. (Non Key) 
ZORQTY Ordered Quantity. (Non Key)

Provide a brief description of your requirement for getting helpful answers.

Regards,

Chen

[former_member404244]

Hi,

Please refer the below link, how to paste your code in SCN.

I would suggest create a lock object for the entire table when the update is going on and release it once the update is complete.

Call the lock object in a loop, so that every time you keep on checking if the lock is there or not, if yes you will not get sy-subrc eq 0. if no then sy-subrc eq 0. based on this you can update the ztable at one for one document.

Chen is correct with his question there is no order number, item number in your ztable and also you can put your code in one include as he and me suggested.

Regards,

Nagaraj

[sachin_yadav3]

Hello Chen,

Do you mean both of your includes are present in the above exit?

Yes

If yes, then why do you have your code in two separate includes, why not have it in one, also why do you want to export and import the values?

Actually I am working on the maintenance issue so the codes are already in place.

Or if you mean only one of the include is called in save_doc where is the other include?

USEREXIT_SAVE_DOCUMENT_PREPARE

Include for the validation and preparing the data for Z table and then exporting data.

userexit_save_document

Include to update the Z table in the Data base table by importing.

How do you know that the data is not processes for the first order, i ask this because the key fields in your Z table is not order specific?

I put the breakpoint in the user exit and saved both the order at same time and in debug found that the data from the Z table is selected at same time into the internal table for both the orders. So before updating the data for first order in the Z table the data for second order is getting selected as i am saving both the order at almost same time.

Regards

Sachin

[former_member404244]

Hi,

Please use lock object concept as i suggested above. Also if the data is same then you will get the same data for the two sales orders, if different then you will get different. Please share you data base select query.

Regards,

Nagaraj

[sachin_yadav3]

Hello Nagaraj,

I have to perform my validation depending upon the Quantity in the order and Quantity in the Z table.

When I am saving two orders at almost same time only second order is considered for the validation.

When I checked in the debug by saving these two order at same time I found that the data from the Z table is selected before the processing is done for the first order and quantity in the Ztable is updated for the first order.

My requirement is to perform the validation for all the order as these two order are created at same time the validation is done for only one order as both the order are selecting the data from the Z table at same time.

Regards

Sachin

[former_member404244]

hI,

This is fine you want to validate for both the records. At run time you will have the sap data stored in temp tables. You can very well use them to select the data. This will be different for both the orders .the ztable should have both the reocrds that you are validating on screen. While updating the data please use lock object and save once at one time , once it is complete then save the other one. if there is one record in the data base table for two orders then you will get one record only.

open two sessions go to VA01 for both, put a break-point in the program. First session input the values and press save button, then do the same thing for other order and press save button. the data available on screen should be different . I hope this is what you are doing.

Regards,

Nagaraj

[sachin_yadav3]

  SELECT * FROM ZDSQTA INTO TABLE XZDSQTA FOR ALL ENTRIES IN TABLE2
  WHERE ZVKORG EQ TABLE2-VKORG
  AND   ZSCYC1 LE TABLE2-ERDAT
  AND   ZSCYC2 GE TABLE2-ERDAT
  AND   ZBZIRK EQ BZIRK
  AND   ZVKBUR EQ VBAK-VKBUR
  AND   ZMATNR EQ TABLE2-MATNR.

Please note that I do not have the order number or the line item number in my z table.

In validation I am modify the records in the Ztable as well I mean to say for each order created I am updating the Quantity in my Z table.

So for the next order the validation should be done depending upon the new order quantity not the old order quantity.

I mean to say for the first order the validation should be done depending upon the old Qty and for the second order validation should be done by new order Qty.

When I am saving the two order at same time validation on both the order is done by old qyt bcoz new oder Qty from the first order is not yet upadated in the database when second order try to fetch the data from the Z table as i am saving both the order at same time.

Hope now i am bit clear now.

[former_member404244]

Hi,

Correct me if i understand your requirement correctly. You are saying for each order you are saving the quantity in the ztable.Now next time you are passing new value to the existing quantity in the ztable to update the new quantity for that order.So for each order you may be changing the qunatity or no change to the quantity.

I didnot get the below points.

I mean to say for the first order the validation should be done depending upon the old Qty and for the second order validation should be done by new order Qty.

When I am saving the two order at same time validation on both the order is done by old qyt bcoz new oder Qty from the first order is not yet upadated in the database when second order try to fetch the data from the Z table as i am saving both the order at same time.

Did you give a try with lock object??

Regards,

Nagaraj

[sachin_yadav3]

Hi,

I am validating my order qty with the qty stored in the table and after validation i am modifying the Qty in Z table with order Qty + Qty in Z table.

I am trying to use the "Exclusive but not cumulative lock" on my table.

As it will lock the entire table and there will be many order created at same time so there will be bit delay.

We are confirming with the user are they Ok with that bit delay if many oders are created at same time.

Regards

Sachin

[Former Member]

Sachin,

I guess i am getting to understand your requirement a little bit,

In validation I am modify the records in the Ztable as well I mean to say for each order created I am updating the Quantity in my Z table.

So you are just updating the quantity fields in the Z table, and this doesn't have any affect on the actual order quantity what soever, is that right or is the order quantity changed or modified based on the entry in the Z table?

So for the next order the validation should be done depending upon the new order quantity not the old order quantity.

Now this is a little tricky, now by next order do you mean the next order with respect to the number range or the next order in general?

For ex: Orders 1234, 1235, 1236 in order, or let us say there are 10 people around the world creating order simultaneously depending on the sequence in which their orders are saved.

I mean to say for the first order the validation should be done depending upon the old Qty and for the second order validation should be done by new order Qty.

When I am saving the two order at same time validation on both the order is done by old qyt bcoz new oder Qty from the first order is not yet upadated in the database when second order try to fetch the data from the Z table as i am saving both the order at same time.

This is what will happen most probably, even though you use the lock concept, let me try and explain...once you save the order in the user exit you are locking the object with enqueue and dequeue...something like below.

Order 1

userexit_save_doc.

enqueue ztable

select from Z table

validate based on the entries from z table

update/modify the z table with udpated values

dequeue ztable

end of userexit.

Now by the above code it doesn't mean that your changes are available in the Z table immediately, the table will actually be updated by the update modules along with other standard tables. Now in the mean time if an other order that hits userexit_save_doc before the update module of the previous order is complete it will still be looking at the old values.

Now in order to overcome the above sitauation you might need to add a COMMIT WORK AND WAIT in the userexit. But this will compromise the data integrity of the order and IS NOT SUGGESTED.

So you see, by locking the table you still haven't achieved your results.

Now, you need to confirm if the entries in the Z table are used only for validation and updating of data in the Z table or does it have any kind of affect on the Order quantity or any other Order specific values in general.

If it is only to track the quantity in the Z Table, and there is no affect on the order data in general, then i suggest that you create a Z program and get all the orders that have been created/changed in a given period and then update the Z Table quantity accordingly, you can have this scheduled as a background job in the non business hours.

Let me know if i was completely off track in getting your requirement

Regards,

Chen

[sachin_yadav3]

Hello Chen,

I appreciate your effort. I can say that you are on right track to understand my actual requirement.

I am facing issue only when multiple user try to create sales order at same time like 103, 104, 105, 106.

If all the above order are created at same time then there is issue with updating of Quantity in the Z table.

Please find my comments as below.

So you are just updating the quantity fields in the Z table, and this doesn't have any affect on the actual order quantity what soever, is that right or is the order quantity changed or modified based on the entry in the Z table?

Sachin Yadav :- I am just modifying the Quantity in the Z table. I am not changing the Quantity in the Order.

Now, you need to confirm if the entries in the Z table are used only for validation and updating of data in the Z table or does it have any kind of affect on the Order quantity or any other Order specific values in general.

Sachin yadav :- I am not changing the order quantity in the order.

By validation I mean to say if there is not sufficient Quantity in the Z table the it will put the header block in the sales order it doesnu2019t change the Quantity in the actual sales order.

If it is only to track the quantity in the Z Table, and there is no affect on the order data in general, then i suggest that you create a Z program and get all the orders that have been created/changed in a given period and then update the Z Table quantity accordingly, you can have this scheduled as a background job in the non business hours.

Sachin Yadav :- I have created correction program to correct the Quantity in Z table but the user saying they donu2019t need correction program. Currently they donu2019t have any option so for time being they are using it. But at same time they are insisting us to find the permanent solution for this issue.

Also orders are blocked depending upon the quantity in the Z table so as Z table is not updated properly so sales order doesnu2019t get blocked thatu2019s why business is insisting us to solve the issue without correction program.

[Former Member]

Hi Sachin,

Sachin yadav :- I am not changing the order quantity in the order. By validation I mean to say if there is not sufficient Quantity in the Z table the it will put the header block in the sales order it doesnu2019t change the Quantity in the actual sales order.

I was kind of hoping that this wouldn't be the case and you just wanted to update the Z table

Anyways, below are my thoughts on a possible solution, now the issue we are trying to address is the case where multiple orders are being created in parallel, and the values from the previous order(which is used for some validation in the current order) not getting updated into the database fast enough.

The solution is based upon using "Data Clusters", as the response time in fetching the data to/from a data cluster is considerably less and the data is stored persistently. Go through the link below and play around with the test program provided to understand the intricacies of this solution.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3bf8358411d1829f0000e829fbfe/frameset.htm

I would suggest you to code something on the below lines in the user exit.

Below is just a pseudo code and you can modify this as per your requirement,

Import itab to li_itab from database indx(AB) ID 'SOME_ID'.
if sy-subrc eq 0.
   read li_itab using the key values.
   validate
   modify the li_itab with the updated values
   export li_itab to database indx(AB) ID 'SOME_ID'.
else.
   select the entry from the Z Table for the key fields in concern.
   read li_itab using the key values.
   validate
   append li_itab with the updated values for this key
   export li_itab to database indx(AB) ID 'SOME_ID'.   
endif.

You can have your correction program adjust the values and update the data clusters in a background job at the EOD.

Also, once this is done you can have your Z Table as a buffered table, as you will be updating that only once in a day.

Now the only concern i see here is the growing size of the data clusters, as the key fields in your Z table are as below the total no of records could possible be running into several thousands(depending on your material master) an approx would be your the "no of sales areas" X "no of materials".

ZMANDT Client (Key) 
ZVKORG Sales Organization (Key) 
ZSCYC1 Sales Cycle 1 (Start Period) (Key) 
ZSCYC2 Sales Cycle 2 (End Period) (Key) 
ZBZIRK Sales district (Key) 
ZVKBUR Sales Office (Key) 
ZMATNR Material Number (Key) 
ZQTANO Quota Number (Key) 

I can think of an approach to overcome this too, all that you will have to do is come up with a logic to create a user defined key(upto 22 characters) using a proper combination of the above key fields. This should limit the records to a few thousand.

i would say wait for the experts on the forum to comment on this solution, if you don't get any response back on this post, you can post the below solution as a question to get their feedback

Regards,

Chen

Edited by: Chen K V on Jun 8, 2011 10:46 AM

[former_member404244]

Hi Chen,

Your solution is pretty good.

Using the lock objects when we are updating ztable it will be update immediately in the ztable.. I don't see it needs to wait for other updates.I have done this but never came across such scenario where the ztable needs to wait for other updates.As the orders are processed parallelly so we can lock the ztable then even if the second order comes when we go for enqueue function module it will throw an error and sy-subrc value not eq 0. we can call this in a loop and exit when the sy-subrc eq 0.

Let me know your thoughts on this.

Regards,

Nagaraj

[Former Member]

Hi Nagaraj,

The changes to the Z table are not committed to the DB, until a "COMMIT WORK" is issued by the program. Now till the changes hit the database, there might be another program which could be in the user exit and the table is open to enqueue and dequeue but are the changes available from the previous order? and i guess this exactly is the issue Sachin is facing as of now ...to put it in a more pictorial way

Order 1

User exit

en queue table

select data from table

update/modify table with updated values

dequeue table

continue with the standard code and commit will be issued by standard at a later point

Now if another order is being processed while the standard code is still executing other pieces of code in user_exit of Order1, then the program will still be able to en queue(as you have already done the dequeue) but the select after that will still be looking at the previous entry.

Order 2

User exit

enqueue table

select data from table

update/modify table with updated values

dequeue table

continue with the standard code and do a commit later

Hope that explains my thoughts on suggesting a different approach.

Regards,

Chen

[sachin_yadav3]

Hi Nagaraj,

I completely agree with Chen, the correct details will not available in the Z table if we donu2019t use the commit and wait but there are risk using commit and wait as suggested by Chen.

Is there any other way by which I can handle my requirement?

[Former Member]

Hello Sachin,

In continuation with the above discussion, what you can do is just before using DEQUE for your Z table do a COMMIT WORK and then do the DEQUE table.

So updated entries will be available in your Z table, when the second order which is being processed simultaneously reaches the point, where the quantity in the Z table is getting updated.

Also what you can do is, the select from the Z table can be done only when the your are able to lock the Z table using ENQUEUE.

So here is what I am suggesting : -

1) First ENQUEUE your Z table using ENQUEUE_LOCKOBJECTNAME.

2) Now select the quantity from the Z table using your select query for the validation purpose.

3) Do the validations.

4) If the validations are successful update the Z table using MODIFY statement.

5) USE COMMIT WORK, after modify statement.

6) Use DEQUEUE_LOCKOBJECTNAME to unlock your Z table.

Hope this should help.

Regards,

RInkesh Doshi

[sachin_yadav3]

Hello Rinkesh,

Please note that I cannot use the commit work as it will cause the database inconsistent. It will commit the entire table in the database.

Hello Chen and Nagaraj,

One possible solution on which I am working is as below.

1:- Read if there is lock on the Ztable.

2:- If there is lock on Ztable then wait for 1 sec and then again check if there is lock on the Ztable or not.

This process is happening in while and end while loop.

3:- If there is no lock on the Ztable then lock the Ztable.

Process the order.

Please note that I am not use commit and I am not unlocking the Ztable.

The Ztable will automatically unlock once the order is saved/created. (In short I am making my second order to wait until my first order is saved/created)

The only thing is I using is Wait upto 1 Sec while checking the lock on the table.

I am testing my changes if I am successfully then I will be back to you with good news.

Please feel free to suggest if I am wrong any where in my logic.

[Former Member]

Hi Sachin,

Sorry to be the bearer of bad news, WAIT implicitly invokes a "COMMIT WORK".

Go through the below link.

http://help.sap.com/saphelp_nwpi71/helpdata/en/c6/13a228e9c5ca469fe7065387dc87be/content.htm

Regards,

Chen

[SuhaSaha]

Hello Chen,

Each time the statement WAIT is used, a database commit is performed. 

It triggers a "database commit" & not a "COMMIT WORK"! I'm sure you must be knowing the difference between the two.

BR,

Suhas

[Former Member]

Hello Sachin,

Please understand that the usage of COMMIT WORK explicitly does not create any database inconsistencies.

If we do not explicitly use the COMMIT WORK in your case, COMMIT WORK triggers automatically once the processing in the particular LUW is over.

As per your reply,

"COMMIT WORK will commit all the entries in the table to the database".

But at any given point of time you will have ONLY one entry of the sales order in your internal table, the one which you are currently processing.

So even if you are using the COMMIT WORK after modifying Z table from the internal table, only values of that SINGLE record will be updated in the database, since your internal table will contain only one record at that point of time.

If the COMMIT work is sucessful , then you refresh your internal table, just before doing the DEQUEUE for Z table. So for the next order now, the internal table will be blank and you can append the order details of the second order in that internal table and continue the cycle.

Also just a suggestion, instead of doing WHILE..ENDWHILE. you can use DO..ENDO with maximum attempts to be done for locking the Z table.

DO N times..

.....code for enqueque, select , update/commit, dequeque.

{If all the above 4 activities are sucessful, do an EXIT from DO ENDO loop using EXIT keyword}.

ENDDO.

Because the only risk that you stand over here is, if you use WHILE...ENDWHILE, then if due to some reason, the program is not able to lock the Z table, then it might go into INFINITE loop, and the user who is trying to create the sales order will never have their sales order created/ there might be a big DELAY which might not be acceptable.

Hope you are getting my point.

BR,

Rinkesh Doshi.

[Former Member]

Thanks Suhas,

I stand corrected, "WAIT" invokes a DB COMMIT, Do you think it is a good idea to add another DB COMMIT hence a DB LUW, specifically in a user exit....your thoughts.

@Sachin - I was thinking over the part of using "EXPORT TO DATABASE" after a mulling over it for sometime, i think a better solution would be to use SHARED BUFFER (provided you have only one app sever in your production instance-this is very unlikely, could you confirm).

@Rinkesh

Please understand that the usage of COMMIT WORK explicitly does not create any database inconsistencies.

It definitely can, think of a situation where you invoke a commit work in between the transaction, and in the latter part of the transaction some validation fails?

Try the below code in a test program

DATA: li_data TYPE STANDARD TABLE OF ztest_table,
      lw_data TYPE ztest_table.

lw_data-vbeln = '1234567890'.
lw_data-posnr = '123456'.
lw_data-matnr = '123123123123'.

MODIFY ztest_table FROM lw_data.

COMMIT WORK.

lw_data = 1 / 0.

If we do not explicitly use the COMMIT WORK in your case, COMMIT WORK triggers automatically once the processing in the particular LUW is over.

Exactly my point COMMIT WORK triggers automatically once the processing in the particular LUW is over.

Because the only risk that you stand over here is, if you use WHILE...ENDWHILE, then if due to some reason, the program is not able to lock the Z table, then it might go into INFINITE loop

I guess you can have a counter within the while and exit the loop, based on this.

Regards,

Chen

Devil lies in the details

[SuhaSaha]

Do you think it is a good idea to add another DB COMMIT hence a DB LUW, specifically in a user exit....your thoughts.

As we know DB LUW & SAP LUW are different, so (read: theoritically) i can safely say that the OP can trigger the DB Commit w/o worrying about the SAP LUW.

To be on the safe side i'll prefer to test the solution before implementing it

I believe that the

WHILE sy-subrc <> 0.
*  Check the Lock Object on the Z-table
ENDWHILE.

might be a possible solution, but there are many design-related questions which need to be answered. For e.g., will the 2nd user "wait" till the 1st user releases the lock etc.

BR,

Suhas

Edited by: Suhas Saha on Jun 9, 2011 2:00 PM

[sachin_yadav3]

Hello All,

Sorry for delay in the reply, I was working on the logic mentioned by me in my previous post and suggestion by Rinkesh and Chen.

Good news is that itu2019s working in my Development system when I tested by creating 5 order at same time.

As of now we have not encountered any issue, we are doing regression testing.

Thanks all for your support and suggestion.

I really appreciate your effort.

I everything is ok then I will close this thread and in case of any issue I will get back to you guys.

Regards

Sachin yadav