CODEWITHSUNDEEP
phpmysql
Teerath Kumar
Teerath Kumar

Reputation: 488

web api inserting duplicate rows

My Code:

$con = new PDO("mysql:host=localhost;dbname=mis","root","");

$name = $_POST['name'];
$transaction_status = $_POST['transaction_status'];
$client_id = $_POST['client_id'];
$group_id = $_POST['group_id'];

$statement = $con->prepare("select transaction_id from transaction_process where client_id='$client_id' and group_id='$group_id'"); 
$statement->execute();

$data = $statement->fetch(PDO::FETCH_ASSOC);
if($data){
..update query
} else {
..insert query
}

Above mentioned is my code sample.

This code executes when web api get called by clients and thousands of transactions collected per minute. So the issue is that, many times i face duplicate records with same group_id and client_id, i tried many more amendments in above select query but still facing same problem.

I can provide more details if required, but want to get out of it. Please support.

Upvotes: 0

Views: 113

Answers (1)

Simon R
Simon R

Reputation: 3772

Given the number of connections you have going to the api, you probably need to write lock your transactions. Please note that during the lock process, other sessions will not be able to read and write data (but this should be relatively quick). 

$con = new PDO("mysql:host=localhost;dbname=mis", "root", "");

$name = $_POST['name'];
$transaction_status = $_POST['transaction_status'];
$client_id = $_POST['client_id'];
$group_id = $_POST['group_id'];

try {
    $con->exec('LOCK TABLE transaction_process WRITE');

    $statement = $con->prepare("SELECT transaction_id 
                                      FROM transaction_process 
                                      WHERE client_id = :clientId and group_id = :groupId");
    $statement->bindParam(':clientId',$client_id);
    $statement->bindParam(':groupId',$group_id);
    $statement->execute();

    $data = $statement->fetch(PDO::FETCH_ASSOC);
    if ($data) {
        do_something();
    } else {
         do_something_else();
    }

    $con->exec('UNLOCK TABLES');
} catch (Exception $e) {
    // catch any exceptions ..
}

For a bit more information on table locking check out this table locking tutorial or the MySQL documentation

Upvotes: 1

Related Questions