Laravel - Calling my Web services Api multiple times bypass the logic of single entry in database and duplication record in database

1.4k views Asked by At

I've a web services Api Api inserts data multiple times in database while it has only to insert only one time and update second time If record exist.

The Problem occurring due to Transaction. I've brief all thing what's happening here.

Laravel 5.4 Database: Maria Db 10.1.21 Host: Localhost

web service Includes

  • call external apis to fetch result
  • store fetch result in database

// code here

   public function getWebsiteDetails(Request $request) {
    Log::info("call ");
    try 
    {
     return DB::transaction(function () use($request)  
    {

    //  Get record from website_master of given business id
    $businessWebsiteRecord = Website::where([
        'business_id' => $businessId
    ])->first();

    // calling external api to get result
    $pageSpeedResult = $this->pageSpeedResult($url);

    Log::info("crossed external api point");

    /**
     * Again check to confirm record is exist or not
     * code write to only for testing purpose
     */
    $recordChecker = Website::where
    (
        'business_id', $businessId
    )->first();

    if(!empty($recordChecker))
    {
    Log::info("if" );
    }
    else
    {
    Log::info("else");
    }

    /**
    * Update record if exist
    * else
    * Create record
    */
    Website::updateOrCreate(
    ['business_id' => $businessId],
    [
    'website' => $url
    ]
    );

    // saving data in another table after some operatins
    $thirdObj->globalIssueGenerator(/* some data*/);

    return $this->helpReturn('Website data saved & issues are generated in system');
    });

    } catch (Exception $e) {
    Log::info(" getWebsiteDetails >> " . $e->getMessage());
    return $this->helpError(1, 'Some Problem happened to run script.');
    }
    }

I've to add only single row entry in database table. But the problem is If I call api multiple times it inserts data multiple times in database while it has only to add first/single time. As Laravel Updateorcreate function is using.

Test Case

If I call APi first time and then again hit that api second time after 3-5 seconds

That api will bypass the check and re-insert data like insert data in multiple times like shown in this screenshot.

enter image description here

That's why I using below code to check.

/**
* Again check to confirm record is exist or not
* code write to only for testing purpose
*/

$recordChecker = Website::where
('business_id', $businessId)->first();

if(!empty($recordChecker))
{
Log::info("if");
}
else
{
Log::info("else");
}

Logs:

so logs generated time same like in database insertion screenshot.

[2018-02-12 06:18:58] local.INFO: call   
[2018-02-12 06:19:03] local.INFO: call   
[2018-02-12 06:19:16] local.INFO: crossed external api point

Going in else because data not available in database

[2018-02-12 06:19:16] local.INFO: else

like in screenshot data has been inserted in database.

Second Call logs

[2018-02-12 06:19:19] local.INFO: else
[2018-02-12 06:19:19] local.INFO: crossed external api point

Again Going in else because data has not been in database yet.

Data has been submitted in database but yet second call response is on the way

Problem Detected:

I'm using Transaction I'm updating two tables by inserting/updating a record.

Without Transaction If I comment transaction code and apply above mentioned test cases data insert only single time. What I require.

Here's the logs of without transaction

[2018-02-12 06:59:56] local.INFO: call   
[2018-02-12 07:00:00] local.INFO: call   
[2018-02-12 07:00:14] local.INFO: crossed external api point  

Going in else because data not available in database so data will be create

[2018-02-12 07:00:14] local.INFO: else  

[2018-02-12 07:00:17] local.INFO: crossed external api point  

Going in if because data available in database

[2018-02-12 07:00:17] local.INFO: if  

But I can't remove transaction because after inserting data in first table most operation done and insert multiple data in table.

I'm thinking to use different action to handle to update flag in file system or in Redis but that's a different technique whichI'll handle this.

I want if any one has fix of this because I've more web services which I'm using same technique

FUll Code

public function getWebsiteDetails(Request $request)
    {
        Log::info("call ");
        try {
            return DB::transaction(function () use($request)
            {
                $thirdObj = new ThirdPartyEntity();

                // user extract
                $checkPoint = $this->setCurrentUser($request->get('token'))->userAllow();

                $user = $checkPoint['records'];
                $businessDetail = $this->businessEntity->userSelectedBusiness($user);

                $businessDetail = $businessDetail['records'];

                $userId = $user['id'];
                $businessId = $businessDetail['business_id'];
                $url = $businessDetail['website'];

                /**
                 * Get record from website_master of given business id
                 */
                $businessWebsiteRecord = Website::where([
                    'business_id' => $businessId
                ])->first();

                // if business_master (url) is exist then go to if block
                if ($url != '') {
                    $data = [];

                        $pageSpeedScore = '';
                        $mobileReadyScore = '';
                        $data['website'] = $url;

                        $url = 'http://'.$url;
                        $pageSpeedResult = $this->pageSpeedResult($url);


                        if( $pageSpeedResult['_metadata']['outcomeCode'] == 200 )
                        {
                            $pageSpeedData = $pageSpeedResult['records'];

                            $speedResult = json_encode($pageSpeedData['formattedResults']['ruleResults']);

                            $data['title_tag'] = $pageSpeedData['title'];
                            $data['page_speed_score'] = $pageSpeedData['score'];
                            $data['page_speed_suggestion'] = $speedResult;

                            $pageSpeedScore = $pageSpeedData['score'];
                        }
                        else
                        {
                            $speedResult = NULL;

                            $data['title_tag'] = NULL;
                            $data['page_speed_score'] = NULL;
                            $data['page_speed_suggestion'] = NULL;
                        }

                    $mobileFriendlyResult = NULL;

                    $data['mobile_ready_score'] = 0;
                    $data['mobile_ready'] = 0;
                    $data['mobile_ready_suggestion'] = NULL;

                    $data['google_analytics'] = 0;

                        Log::info("busnes " . $businessId);
                        Website::updateOrCreate(
                            ['business_id' => $businessId],
                            $data
                        );

                    $issueData = []
                        $thirdObj->globalIssueGenerator($userId, $businessId, '', $issueData, 'website', 'website');


                    return $this->helpReturn('Website data saved & issues are generated in system');
                }
            });

        } catch (Exception $e) {
            Log::info(" getWebsiteDetails >> " . $e->getMessage());
            return $this->helpError(1, 'Some Problem happened to run script.');
        }
    }
2

There are 2 answers

4
AudioBubble On

Use record locks during the transaction to prevent duplicate records.

Locks can be applied at the table level - preventing any record from being modified - or at the record level - preventing any other processes from modifying just that record.

Since you have duplicates you'll likely need to use table level locking

0
Abdul Rehman On

@btl, @Nikola

Current I try with code location changes and returning result correctly and inserting record in database,

I paste out code from transaction which check data exist in database or not. Currently I'm digging what's happening but I'm sharing the current status please see new code and old code. new code is working and also add logs of both code

Newcode after changing

public function getWebsiteDetails(Request $request)
    {
        Log::info("call ");
        try {

            /**
             * out from transction
             */
            // extract user 
            $checkPoint = $this->setCurrentUser($request->get('token'))->userAllow();

            $user = $checkPoint['records'];

            // extract business of user
            $businessDetail = $this->businessEntity->userSelectedBusiness($user);

            $businessDetail = $businessDetail['records'];
            $userId = $user['id'];
            $businessId = $businessDetail['business_id'];
            $url = $businessDetail['website'];

            /**
             * Get record from website_master of given business id
             */
            $businessWebsiteRecord = Website::where([
                'business_id' => $businessId
            ])->first();

            $result = DB::transaction(function () use($request, $url, $businessId, $userId,$businessWebsiteRecord)
            {
                $thirdObj = new ThirdPartyEntity();

                // if business_master (url) is exist then go to if block
                if ($url != '') {
                    $data = [];
                    $pageSpeedScore = '';
                    $mobileReadyScore = '';
                    $data['website'] = $url;
                    $url = 'http://' . $url;

                    $pageSpeedResult = $this->pageSpeedResult($url);

                    if ($pageSpeedResult['_metadata']['outcomeCode'] == 200) {
                        $pageSpeedData = $pageSpeedResult['records'];
                        $speedResult = json_encode($pageSpeedData['formattedResults']['ruleResults']);
                        $data['title_tag'] = $pageSpeedData['title'];
                        $data['page_speed_score'] = $pageSpeedData['score'];
                        $data['page_speed_suggestion'] = $speedResult;
                        $pageSpeedScore = $pageSpeedData['score'];
                    } else {
                        $speedResult = NULL;
                        $data['title_tag'] = NULL;
                        $data['page_speed_score'] = NULL;
                        $data['page_speed_suggestion'] = NULL;
                    }

                    $mobileFriendlyResult = NULL;
                    $data['mobile_ready_score'] = 0;
                    $data['mobile_ready'] = 0;
                    $data['mobile_ready_suggestion'] = NULL;
                    $data['google_analytics'] = 0;
                    Log::info("busnes " . $businessId);

                    $recordChecker = Website::where('business_id', $businessId)->first();

                    if (!empty($recordChecker)) {
                        Log::info("if busnes " . $businessId);
                    } else {
                        Log::info("else busnes " . $businessId);
                    }

                    Website::updateOrCreate(
                        ['business_id' => $businessId],
                        [
                            'website' => $url
                        ]
                    );
                    $issueData = [
                        [
                            'key' => 'title_tags',
                            'value' => $data['title_tag'],
                            'issue' => 36,
                        ],
                        [
                            'key' => 'page_speed',
                            'value' => $pageSpeedScore,
                            'issue' => 38,
                        ],
                        [
                            'key' => 'mobile_speed',
                            'value' => $mobileReadyScore,
                            'issue' => 37,
                        ],
                        [
                            'key' => 'google_analytics',
                            'value' => $data['google_analytics'],
                            'issue' => 39,
                        ]
                    ];
                    $thirdObj->globalIssueGenerator($userId, $businessId, '', $issueData, 'website', 'website');
                    return $this->helpReturn('Website data saved & issues are generated in system');
                }
            });

            Log::info("finish");
            return $result;

        } catch (Exception $e) {
            Log::info(" getWebsiteDetails >> " . $e->getMessage());
            return $this->helpError(1, 'Some Problem happened to run script.');
        }
    }

Old code

public function getWebsiteDetails(Request $request)
    {
        Log::info("call ");
        try {
            return DB::transaction(function () use($request)
            {
                $thirdObj = new ThirdPartyEntity();

                // user extract
                $checkPoint = $this->setCurrentUser($request->get('token'))->userAllow();

                $user = $checkPoint['records'];
                $businessDetail = $this->businessEntity->userSelectedBusiness($user);

                $businessDetail = $businessDetail['records'];

                $userId = $user['id'];
                $businessId = $businessDetail['business_id'];
                $url = $businessDetail['website'];

                /**
                 * Get record from website_master of given business id
                 */
                $businessWebsiteRecord = Website::where([
                    'business_id' => $businessId
                ])->first();

                // if business_master (url) is exist then go to if block
                if ($url != '') {
                    $data = [];

                        $pageSpeedScore = '';
                        $mobileReadyScore = '';
                        $data['website'] = $url;

                        $url = 'http://'.$url;
                        $pageSpeedResult = $this->pageSpeedResult($url);


                        if( $pageSpeedResult['_metadata']['outcomeCode'] == 200 )
                        {
                            $pageSpeedData = $pageSpeedResult['records'];

                            $speedResult = json_encode($pageSpeedData['formattedResults']['ruleResults']);

                            $data['title_tag'] = $pageSpeedData['title'];
                            $data['page_speed_score'] = $pageSpeedData['score'];
                            $data['page_speed_suggestion'] = $speedResult;

                            $pageSpeedScore = $pageSpeedData['score'];
                        }
                        else
                        {
                            $speedResult = NULL;

                            $data['title_tag'] = NULL;
                            $data['page_speed_score'] = NULL;
                            $data['page_speed_suggestion'] = NULL;
                        }

                    $mobileFriendlyResult = NULL;

                    $data['mobile_ready_score'] = 0;
                    $data['mobile_ready'] = 0;
                    $data['mobile_ready_suggestion'] = NULL;

                    $data['google_analytics'] = 0;

                        Log::info("busnes " . $businessId);
                        Website::updateOrCreate(
                            ['business_id' => $businessId],
                            $data
                        );

                    $issueData = []
                        $thirdObj->globalIssueGenerator($userId, $businessId, '', $issueData, 'website', 'website');


                    return $this->helpReturn('Website data saved & issues are generated in system');
                }
            });

        } catch (Exception $e) {
            Log::info(" getWebsiteDetails >> " . $e->getMessage());
            return $this->helpError(1, 'Some Problem happened to run script.');
        }
    }

Note: Going in else because data not available in database so data will be create Going in If because data has already existed. (That' what I acquire)

Ne code Logs

[2018-02-12 14:18:49] local.INFO: call   
[2018-02-12 14:18:53] local.INFO: call
[2018-02-12 14:19:08] local.INFO: crossed external api point
[2018-02-12 14:19:08] local.INFO: else
[2018-02-12 14:19:08] local.INFO: finish
[2018-02-12 14:19:09] local.INFO: crossed external api point
[2018-02-12 14:19:09] local.INFO: if
[2018-02-12 14:19:09] local.INFO: finish

Old Code Logs

[2018-02-12 14:17:16] local.INFO: call   
[2018-02-12 14:17:19] local.INFO: call
[2018-02-12 14:17:34] local.INFO: crossed external api point
[2018-02-12 14:17:34] local.INFO: else
[2018-02-12 14:17:34] local.INFO: finish
[2018-02-12 14:17:35] local.INFO: crossed external api point
[2018-02-12 14:17:36] local.INFO: else
[2018-02-12 14:17:36] local.INFO: finish  

Currently as per old logs which run after first transaction that old code did not found record yet in database in second transaction but in new code logs shows data before start second transaction found data in database,

Yeah this has the chance of failure but if the response is same and both call saving data at same time into database but I'll use another technique to handle this but New code has the better chances to track record if record already inserted 3-5 seconds ago which was failing on old code.

Thanks @btl and @Nikola. But Please share if you have anything on this behavior.