Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Allow only one batch j...
Finance | Project Operations, Human Resources, ...
Unanswered

Allow only one batch job to insert into a table at one time in multiple batch job scenario.

(0) ShareShare
ReportReport
Posted on by pam9712 10

Hi,

I have created a batch job class based on RunBaseBatch. When I run this class, it populates new report data into a regular table. I can't use other table types and it has to be a regular table. Also, this table will only be used by this class only. I have the logic in the class where it deletes everything from the table and recreates it on each run. Obviously, It shows inaccurate data when there are multiple reoccurring batch jobs runs at the same time. How can I solve this issue that only one batch can go into this table and others will have to wait or one can go in and others jobs will fail. 

Thank you.

  • pam9712 Profile Picture
    pam9712 10 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    Thank you.

    I will try and post it.

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User 2024 Season 1 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    How long is the time between the two transactions? Seems very unlikely to me.

    Note: In the second operation, it will be faster if you simply insert instead of using insert_recordset. (join may be slowing you down)

    Alternatively, you can open a flag record in one of the tables and lock it with pessimisticLock throughout the process.

  • pam9712 Profile Picture
    pam9712 10 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    That what I am doing,  please look into it:

    select * from Batch where Batch.ClassNumber == classNum(myBatchClass) && Batch.Status == BatchStatus::Executing;

            //select count (RecId) from Batch where Batch.ClassNumber == classNum(myBatchClass) && Batch.Status == BatchStatus::Executing;
            //countRec = Batch.RecId;
            select firstonly batchStatusLogTable where batchStatusLogTable.Status == BatchStatus::Executing;            
            try
            {
            
            if (!batchStatusLogTable.RecId)
            {

                insert_recordset batchStatusLogTable (BatchJobId,Caption,Status)
                    select BatchJobId, Caption, Status from Batch 
                    where Batch.ClassNumber == classNum(myBatchClass) 
                        && Batch.Status == BatchStatus::Executing
                        && Batch.BatchJobId == batchId;


                //insert data into report table
                this.selectFromQuery(batchDate);
                    
                //update the batch status table to ended
                ttsbegin;
                While select forupdate * from batchStatusLogTable where batchStatusLogTable.Status == BatchStatus::Executing
                {
                    batchStatusLogTable.Status = BatchStatus::Finished;
                    batchStatusLogTable.update();
                }
                ttscommit;              
            }
            else
            {
                Error('another batch running');
            }

    Before the first batch inserts the record into batchStatusLogTable, the other batch also enters the if block. Please help!

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User 2024 Season 1 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    You don't need to update line by line. Update all records at the end of batch.

    Check if there any empty(isUpdated=No) records  at the beginning of each batch.

    If there is such a record, it means there is a working batch.

    If you want to solve it with a second table (and maybe you want to keep a log). Open start time and end time fields. If there is a start full and end empty record, it means there is a working batch.

  • pam9712 Profile Picture
    pam9712 10 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    The table needs to hold data until the next batch run. But Ergün, your logic will not work because if one or more batches run at the same time and line by line, the second batch will overlap and the result in the table will be inaccurate.

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User 2024 Season 1 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    It can be managed with batch information(from standart batch tables), but I think it is necessary to think more simply and avoid surprises.

    If the table holds temporary data, delete it at the end of the batch. If there is a record in the table, dont let run the second batch.

    If the records are needed after the batch is over. Create a field, fill the field at the end of the batch. If there is a record and the field is not full, dont let run the second batch.

  • pam9712 Profile Picture
    pam9712 10 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    That is what I am trying to do, I am using the batch table with the following statement:

    select from batch where batch.ClassNumber == classNum(myBatchClass) && batch.Status == BatchStatus::Executing;

            NewBatchRunStatus.BatchJobId = batch.batchjobid;
            NewBatchRunStatus.Caption = batch.Caption;
            NewBatchRunStatus.Status = BatchStatus::Executing;
            NewBatchRunStatus.insert();
            try
            {
                if (NewBatchRunStatus.Status != BatchStatus::Executing)
                {
                    error("Another batch running the same batch class");
                }
                else
                {
                    //run logic and insert data into my table
                    
                    While select * from NewBatchRunStatus where NewBatchRunStatus.Status = BatchStatus::Executing
                    {
                        NewBatchRunStatus.Status = BatchStatus::Ended
                        NewBatchRunStatus.update();
                    }
                }
            }
            catch (Exception::Error)                                
            {
            }

    I am using another table to write the batch info such as batchjobid, status, etc. But when another batch job runs at the same time and executes the above code, it fails. 

  • nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    You can either try to use BatchJob and Batch tables to figure out if the batch is already running, or let your batch write it's status (executing /done) in some other table. This way other batches know whether to fail or not. Should be pretty straight forward. And the moment when the batch is done, another batch is free to delete the data in your table and re-populate it. 

  • pam9712 Profile Picture
    pam9712 10 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    I want to keep the data in the table until the next batch job comes and overwrites it. Because the batch job can be set using different parameters. I don't want to keep the different versions of data in that table, only the latest run will be kept. Everything works fine but it only fails if the user setup multiple batch jobs which run at the same date&time. In this scenario, I only want one of the batch to execute and the rest to show an error.

  • nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Allow only one batch job to insert into a table at one time in multiple batch job scenario.

    Why doesn't it work? If a batch notices that this table has data with other session if, you can make it fail. Or am I missing something?

    But someone / something must eventually clear data from the table or all future batches would fail. But I guess you already must have thought of this.

    I personally think that your approach is quite different than the overall logic of the system, but it's definetely doable if your business requirement truly needs it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans