MySQL and PHP: Atomicity and re-entrancy of a PHP code block executing two subsequent queries - how dangerous?

Question

In MySQL I have to check whether select query has returned any records, if not I insert a record. I am afraid though that the whole if-else operation in PHP scripts is NOT as atomic as I would like, i.e. will break in some scenarios, for example if another instance of the script is called where the same record needs to be worked with:

if(select returns at least one record)
{
    update record;
}
else
{
    insert record;
}

I did not use transactions here, and autocommit is on. I am using MySQL 5.1 with PHP 5.3. The table is InnoDB. I would like to know if the code above is suboptimal and indeed will break. I mean the same script is re-entered by two instances and the following query sequence occurs:

1. instance 1 attempts to select the record, finds none, enters the block for insert query
2. instance 2 attempts to select the record, finds none, enters the block for insert query
3. instance 1 attempts to insert the record, succeeds
4. instance 2 attempts to insert the record, fails, aborts the script automatically

Meaning that instance 2 will abort and return an error, skipping anything following the insert query statement. I could make the error not fatal, but I don't like ignoring errors, I would much rather know if my fears are real here.

Update: What I ended up doing (is this ok for SO?)

The table in question assists in a throttling (allow/deny, really) amount of messages the application sends to each recipient. The system should not send more than X messages to a recipient Y within a period Z. The table is [conceptually] as follows:

create table throttle
(
    recipient_id integer unsigned unique not null,
    send_count integer unsigned not null default 1,
    period_ts timestamp default current_timestamp,
    primary key (recipient_id)
) engine=InnoDB;

And the block of [somewhat simplified/conceptual] PHP code that is supposed to do an atomic transaction that maintains the right data in the table, and allows/denies sending message depending on the throttle state:

function send_message_throttled($recipient_id) /// The 'Y' variable
{
    query('begin');

    query("select send_count, unix_timestamp(period_ts) from throttle where recipient_id = $recipient_id for update");

    $r = query_result_row();

    if($r)
    {
        if(time() >= $r[1] + 60 * 60 * 24) /// The numeric offset is the length of the period, the 'Z' variable
        {/// new period
            query("update throttle set send_count = 1, period_ts = current_timestamp where recipient_id = $recipient_id");
        }
        else
        {
            if($r[0] < 5) /// Amount of messages allowed per period, the 'X' variable
            {
                query("update throttle set send_count = send_count + 1 where recipient_id = $recipient_id");
            }
            else
            {
                trigger_error('Will not send message, throttled down.', E_USER_WARNING);
                query('rollback');
                return 1;
            }
        }
    }
    else
    {
        query("insert into throttle(recipient_id) values($recipient_id)");
    }

    if(failed(send_message($recipient_id)))
    {
        query('rollback');
        return 2;
    }

    query('commit');
}

Well, disregarding the fact that InnoDB deadlocks occur, this is pretty good no? I am not pounding my chest or anything, but this is simply the best mix of performance/stability I can do, short of going with MyISAM and locking entire table, which I don't want to do because of more frequent updates/inserts vs selects.

Answer 1

This can and might happen depending on how often this page is executed.

The safe bet would be to use transactions. The same thing you wrote would still happen, except that you could safely check for the error inside the transaction (In case the insertion involves several queries, and only the last insert breaks) allowing you to rollback the one that became invalid.

So (pseudo):

#start transaction
if (select returns at least one record)
{
    update record;
}
else
{
    insert record;
}

if (no constraint errors)
{
    commit; //ends transaction
}
else
{
    rollback; //ends transaction
}

You could lock the table as well but depending on the work you're doing, you'd have to get an exclusive lock on the entire table (you cannot SELECT ... FOR UPDATE non-existing rows, sorry) but that would also block reads from your table until you are finished.

Answer 2

It seems like you already know the answer to the question, and how to solve your problem. It is a real problem, and you can use one of the following to solve it:

• SELECT ... FOR UPDATE
• INSERT ... ON DUPLICATE KEY UPDATE
• transactions (don't use MyIsam)
• table locks