预订系统点击生成唯一ID按钮出现重复问题技术求助

Hey Nina, the duplicate ID issue you're facing comes down to two key problems: concurrent database requests causing race conditions and async AJAX logic not being handled properly in your frontend. Let's break this down and fix it step by step.

First, Let's Diagnose the Root Causes #

1. Race Condition in the Database #

Right now, your getid.php and insert-id.php both calculate the next ID by fetching the current max value and adding 1. When multiple users click "Add New Booking" at the same time:

• Both requests run SELECT max(mx_val) and get the same value (e.g., 100)
• Both then insert 101 into the table
• Result: duplicate IDs

This happens because the "get max + insert" process isn't atomic—there's a gap between reading the max value and writing the new one where another request can sneak in.

2. Frontend Async Logic Error #

Looking at your jQuery code:

$('#openModal').on('click', function () {
    showId(); // This is an async AJAX call
    var mx_val=$("#show-idi").val(); // This runs BEFORE showId() finishes!
    // ... then you send mx_val via AJAX
});

The showId() function uses XMLHttpRequest to fetch the ID, but you immediately try to read #show-idi's value before the AJAX request completes. So you're probably sending empty or stale values to insert-id.php, which adds to the confusion.

Solutions to Fix the Duplicates #

Option 1: Use Database Auto-Increment Column (Simplest & Most Reliable) #

The easiest way to avoid duplicate IDs is to let the database handle generating unique values for you. Here's how:

1. Alter your mxvalue table to make mx_val an auto-increment primary key:

ALTER TABLE mxvalue MODIFY COLUMN mx_val INT AUTO_INCREMENT PRIMARY KEY;

2. Update your backend code to let MySQL generate the ID automatically:

Modified insert-id.php

<?php
include('db.php');
require_once("dbcontroller.php");
$db_handle = new DBController();

// Insert a new row without specifying mx_val—MySQL will auto-generate it
$stmt = $DBcon->prepare("INSERT INTO mxvalue DEFAULT VALUES");
if($stmt->execute()) {
    // Get the auto-generated ID
    $newId = $DBcon->insert_id;
    $res = "Data Inserted Successfully: ID = $newId";
    echo json_encode($res);
} else {
    $error = "Not Inserted, Some Problem Occurred.";
    echo json_encode($error);
}
?>

3. Update your frontend to fetch the new ID after insertion (instead of before):

Modified Frontend Code

$(function () {
    $('#openModal').on('click', function () {
        $.ajax({
            type: "POST",
            url: "insert-id.php",
            dataType: "JSON",
            success: function(data) {
                // Set the generated ID in the modal form
                document.getElementById("show-id").defaultValue = data.split('ID = ')[1];
                $("#message").html(data);
                $("p").addClass("alert alert-success");
                getDu();
            },
            error: function(err) {
                $("#message").html("Error saving booking!");
                $("p").addClass("alert alert-danger");
                console.log(err);
            }
        });
    });
});

// You can remove the old showId() function since we don't need it anymore

Option 2: Atomic ID Generation (If You Can't Use Auto-Increment) #

If you need to generate IDs manually (e.g., non-numeric IDs), use an atomic database operation to avoid race conditions. For MySQL, you can use a single query to get and increment the max value:

Updated getid.php (Atomic Version)

<?php
$con = mysqli_connect('','','','');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

// Use an atomic query to increment and get the new ID
mysqli_query($con, "INSERT INTO mxvalue (mx_val) SELECT COALESCE(max(mx_val), 0) + 1 FROM mxvalue");
$newId = mysqli_insert_id($con);

echo $newId;
mysqli_close($con);
?>

Then fix your frontend to wait for the AJAX call to finish before using the ID:

Fixed Frontend Async Logic

function showId(callback) {
    let xmlhttp;
    if (window.XMLHttpRequest) {
        xmlhttp=new XMLHttpRequest();
    } else {
        xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange=function() {
        if (this.readyState==4 && this.status==200) {
            const newId = this.responseText;
            document.getElementById("show-id").defaultValue = newId;
            callback(newId); // Pass the ID to the callback
        }
    }
    xmlhttp.open("GET","getid.php",true);
    xmlhttp.send();
}

$(function () {
    $('#openModal').on('click', function () {
        // Wait for showId to finish before inserting
        showId(function(mx_val) {
            $.ajax({
                type: "POST",
                url: "insert-id.php",
                data: {mx_val:mx_val},
                dataType: "JSON",
                success: function(data) {
                    $("#message").html(data);
                    getDu();
                    $("p").addClass("alert alert-success");
                },
                error: function(err) {
                    $("#message").html("Saved!");
                    $("p").addClass("alert alert-success");
                    console.log(err);
                }
            });
        });
    });
});

Note: Even with this, it's better to handle the ID generation and insertion in a single backend request to reduce round trips.

Key Takeaways #

• Always use database auto-increment for unique IDs if possible—it's designed to handle concurrency safely.
• Never rely on client-side ID generation or separate "read max + write" operations for unique values in a multi-user system.
• Handle async AJAX calls correctly—always wait for the request to complete before using the returned data.

内容的提问来源于stack exchange，提问作者Nina