StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL and PHP: Atomicity and re-entrancy of a PHP code block executing two subsequent queries - how dangerous?
text
Body
copied!<p>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:</p> <pre><code>if(select returns at least one record) { update record; } else { insert record; } </code></pre> <p>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:</p> <ol> <li>instance 1 attempts to select the record, finds none, enters the block for insert query</li> <li>instance 2 attempts to select the record, finds none, enters the block for insert query</li> <li>instance 1 attempts to insert the record, succeeds</li> <li>instance 2 attempts to insert the record, fails, aborts the script automatically</li> </ol> <p>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.</p> <p><strong>Update: What I ended up doing (is this ok for SO?)</strong></p> <p>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:</p> <pre><code>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; </code></pre> <p>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:</p> <pre><code>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'); } </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload