CODEWITHSUNDEEP
phpmysqlmariadbinnodb
Jaxovee
Jaxovee

Reputation: 135

InnoDB table lock for writing but allow reading

I have a table COMMANDS that users inserts data simultaneously.

After every insert I have to make some (time consuming) calculations and save the results to another table RESULTS.

At time same time all users also reads data from COMMANDS.

My question is :

After new $command insert, I use all rows in that table in my calculations. Every row affects calculation and also may affect previous $commands, because of that I want to lock this table for new inserts until current calculations are made and results are saved. But also I dont want to block other users to view current state of COMMANDS.

Shortly I want to lock a table for writing without locking for reading.

I'am using InnoDB engine. I read some documents about locking, but i am totally confused.

Shared and exclusive locks, intention locks, gap locks ...

It seems stupid but currently I am using a mechanism like below

public function storeNewCommand($command){

    // $busy_flag is an app level global that can be read by all user sessions

    if($busy_flag){
        usleep(10000);
        return $this->storeNewCommand($command);
    } 

    $busy_flag = true; //(lock)

    /*
    ...insert new $command to COMMANDS

    ...do calculations using all comands including last one

    ...store results in RESULTS
    */

    $busy_flag = false; //(unlock)

}

I know there must be better and clever solution for this without loops and sleeps. But I dont know which one to use.

Upvotes: 0

Views: 86

Answers (1)

Rick James
Rick James

Reputation: 142503

If you can do the lengthy stuff in less than lock_wait_timeout, which defaults to 50 seconds, then use BEGIN...COMMIT. (Personally, I would limit it to more like 2 seconds, not 50.)

Otherwise, devise some other method -- have a table that every writer honors that says that you are "busy" in this "critical section". It probably does not need to be much more than a 1- or 2-column table with a single row.

When grabbing that mutex, be sure to encapsulate the fetch, set, etc, in a transaction and react to failure. After getting the lock, then proceed to do the slow stuff. Finally release the lock.

Caveat: If there is a crash or a software bug, the code to release the lock may never be executed. So, it would be good to include a timestamp, plus something to notice that the mutex has not been released in a "long" time. It could them email you about the problem and clear the lock.

Upvotes: 1

Related Questions