REPLACE DELAYED behavior

[Josh SP]

Hi, maybe I'm misunderstanding how REPLACE DELAYED works, but I seem to be having some trouble, and I'm wondering if someone can point me in the right direction. Thanks.

THE SITUATION

I have a PHP/MySQL website that needs to update a value in a table upon each page load. The problem is that this table is also accessed by many SELECT statements, a few of which are unavoidably slow. When one of these slow SELECTs is running and an UPDATE is attempted, my understanding is that the UPDATE locks the table and doesn't let any other SELECTs access the table until the original slow SELECT *and* the UPDATE have completed. I'm witness this occuring, so I'm fairly certain I understand this part correctly. Because of this, whenever a slow SELECT occurs, all the pages on the site (the ones that access the table in question, at least) hang for several seconds.


MY ATTEMPTED SOLUTION

I changed the UPDATE to a REPLACE in order to take advantage of the DELAYED option. I figured this would solve my problems because the table would not be locked to the exclusion of other SELECTS. I thought that the DELAYED option would cause the REPLACE to be nice and let other pending SELECTs access the table.


DOESN'T WORK AS INTENDED

This doesn't seem to be working. The DELAYED option seems to *not* allow other SELECTs to access the table. I have verified this via the mysql command line interface.


SOME DETAILS

Here is the REPLACE SQL I'm using:

REPLACE DELAYED `object_scores` (`object_id`, `hits`, `score`, `weighted_num_votes`, `weighted_vote_sum`) VALUES (172582, 68, 88.01, 3.67371, 36.3206);

Here's the table:

> show create table object_scores;

+----------
| object_scores | CREATE TABLE `object_scores` (
`object_id` int(10) unsigned NOT NULL default '0',
`hits` int(10) unsigned NOT NULL default '0',
`score` float NOT NULL default '0',
`weighted_num_votes` float NOT NULL default '0',
`weighted_vote_sum` float NOT NULL default '0',
PRIMARY KEY (`object_id`),
KEY `hits` (`hits`),
KEY `score` (`score`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
+----------

Any advice would be greatly appreciated. Thanks!

[PASHA_SANYAL]

The REPLACE command you've written is used to insert data in the table. The following contains the syntax of the REPLACE command and see if it works...

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...

[Josh SP]

Thanks for the reply PASHA_SANYAL.

Unfortunately I don't understand what you are trying to get me to try. I believe that the syntax is ok.

The issue isn't with the syntax. It is with multiple queries occuring at the same time in the database. There is a *SLOW* query that starts and then an UPDATE locks the table thus making any other SELECTs that come in after the lock created by the UPDATE (or REPLACE now), wait until the UPDATE (REPLACE) has completed.

The REPLACE DELAYED syntax is supposed to make it so that any SELECT queries that come in after the REPLACE would be allowed to access the table, *BUT* this isn't happening. The question is why isn't this working?

Thanks