Is there an advantage of using vanilla sql code vs an upsert

Question

I am not a db expert so I hope someone can give me a pointer. Is there an advantage of using a vanilla approach vs an upsert appoach. I find the vanilla approach clearer with no obvious side effects.

This table is a summary table created from time consuming reads and calculations (average 3-4 seconds to complete). This table is disposable (it can be truncated at any time). This table receives a lot of updates and reads with minimal inserts.

Initially each read access would update and then read the data. To improve performance, I added a 5 minute delay between updates

after an update occurs for a group, for the next 5 minutes users in that group will only read the data (saving 3-4 seconds)
the first access after the 5 minutes has elapsed will trigger a full update for the group data

Initially I implemented an upsert algorithm. However a side effect of the upsert-insert is that the auto-increment pk field is updated with every execution. This resulted in a jump of 100000 for that key in a few hours. My concern is not the gaps, but hitting the max value for an int. I can manage this by either removing the auto-increment field or issuing a truncate command everynight. I also saw a few other creative solutions to prevent the auto increment.

However I prefer not to manage a side effect. I decided to try resolve this by using a different approach. Especially since the is a read and updatecentric table.

Revised approach

    $lastInsertId=0;
    $q->beginTransaction(); 
    
    $sql = 'UPDATE data_snapshots SET field1=:field1,field2=:field2,field3=:field3,id = LAST_INSERT_ID(id) WHERE groupId=:groupId';
    $args=[':groupId'=>$groupId,':field1'=>$field1,':field2'=>$field2,':field3'=>$field3];
    $q->prepare ( $sql );
    $result = $q->execute ( $args );
    $lastInsertId = $q->lastInsertId();
    
    if($lastInsertId == 0){
        $sql='INSERT INTO data_snapshots (groupId,field1,field2,field3)';
        $q->prepare ( $sql );
        $result = $q->execute ( $args );
        $lastInsertId = $q->lastInsertId();
    }
    
    if($result == true && $lastInsertId  > 0){
        $q->commit();
        $modified=true;
    }
    else{
        $q->rollBack();
    }

Upsert approach

    $sql='INSERT INTO data_snapshots
    (groupId,field1,field2,field3)
    VALUES
    (:groupId,:field1,:field2,:field3)
    ON DUPLICATE KEY UPDATE
    groupId=:groupId_dup,field1=:field1_dup,field2=:field2_dup,field3=:field3_dup'];

    $args=[':groupId'=>$groupId,':field1'=>$field1,':field2'=>$field2,':field3'=>$field3,
    ':groupId_dup'=>$groupId,':field1_dup'=>$field1,':field2_dup'=>$field2,':field3_dup'=>$field3,]
    
    $q->prepare ( $sql );
    $result = $q->execute ( $args );

Bill_Karwin_20860 · Answer

INSERT...ON DUPLICATE KEY UPDATE does not increment the auto-increment counter.

Your "vanilla approach" has a race condition problem. You try to UPDATE but if it affects no rows, then you INSERT. But what if some other client inserts the row in the brief amount of time between your UPDATE and your INSERT?

You might think this is unlikely to the point where you don't have to worry about it, but we frequently develop code that runs many transactions per second, and from many concurrent clients. There's an old cautionary saying, "one in a million is next Tuesday," meaning even though you assume the race condition is very unlikely to happen, it's sure to happen sooner than you think, given a large number of samples.

By the way, you can eliminate all the *_dup parameters from your prepared statement. In your ON DUPLICATE KEY clause, use VALUES() like this:

INSERT INTO data_snapshots (groupId,field1,field2,field3)
VALUES (:groupId,:field1,:field2,:field3)
ON DUPLICATE KEY UPDATE
groupId=VALUES(groupId), field1=VALUES(field1), field2=VALUES(field2), field3=VALUES(field3);

This syntax means if the ON DUPLICATE KEY clause is executed, the values to update will be the same values that you provided in the parameters in the VALUES() clause above. If this is the typical case, then you don't have to pass each value twice.

Is there an advantage of using vanilla sql code vs an upsert

1 Answers