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.
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.');
}
}
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