Why transaction blocking simple insert in MySQL?

All we need is an easy explanation of the problem, so here it is.

transaction Query

    mysqli_begin_transaction($conn);
    $sql = "UPDATE foldertable SET trashed = 1 WHERE serverToken = (SELECT serverToken from servertoken where userToken = ? limit 1) and 
    
    (folderId = ?
    or 
    RootFolderPath LIKE 
                CONCAT (
                    (SELECT RootFolderPath FROM foldertable WHERE serverToken = (SELECT serverToken from servertoken where userToken = ? ) AND folderid = ? limit 1)
                    ,'/'
                    , ?
                    ,'%'

                )
    )";
    $stmt =  mysqli_stmt_init($conn);
    mysqli_stmt_prepare($stmt, $sql);
    mysqli_stmt_bind_param($stmt, "sisii", $data["userToken"], $id, $data["userToken"], $id, $id);
    if (!mysqli_stmt_execute($stmt)) {
        echo mysqli_stmt_error($stmt);
        $serverError = true;
        return;
    }
    // echo "\n\n affected rows " . mysqli_affected_rows($conn);
    if (mysqli_affected_rows($conn) > 0) {
        sleep(15);
        // Insert into bin folder
        $sql = "INSERT INTO binfolder (serverToken , folderId) value ( (SELECT serverToken from servertoken where userToken = ? limit 1) , ?)";
        $stmt =  mysqli_stmt_init($conn);
        mysqli_stmt_prepare($stmt, $sql);
        mysqli_stmt_bind_param($stmt, "si", $data["userToken"], $id);
        if (!mysqli_stmt_execute($stmt)) {
            echo mysqli_stmt_error($stmt);
            $serverError = true;
            return;
        }
        if (mysqli_affected_rows($conn) > 0) {
            $success = true;
            mysqli_commit($conn);
        }

    }

Insert Query

INSERT INTO foldertable(serverToken) SELECT (123456) 

Problem

if execute the insert query after transaction started but the insert does not have to wait for transaction complete . Because the update query on transaction does lock row involved with insert query.

What I wanted

the Insert query will run immediately if it does not contain the same serverToken needed in Insert query.

Thank You in Advance.

Why transaction blocking simple insert in MySQL?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

For transaction blocking, the first line of defense is to speed up the query. Though this does not completely eliminate the possibly of block/delay/deadlock, it will often make the situation more tolerable.

Re the LIMIT 1 — Is there only 1? If so, why not do a JOIN. Or might there be more than one? If so, which one? That is, don’t you need an ORDER BY?

servertoken needs INDEX(userToken, serverToken)

Would a single INSERT ... ON DUPLICATE KEY UPDATE ... suffice for your lengthy set of queries? If so, it would probably be a lot faster.

I see an OR in the long query. This is likely to prevent usage of an index. (I don’t understand the intent of the query well enough to suggest an alternative.)

“RootFolderPath LIKE CONCAT (…” — This smells like something that should be redesigned. Consider putting path parts in separate columns? Or something else.

Is folderId the PRIMARY KEY of folderTable?

Plese provide SHOW CREATE TABLE (as text, not image) for each relevant table.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply