MySQL Bugs: #849: Update Delayed

Bug #849	Update Delayed
Submitted:	15 Jul 2003 9:11	Modified:	6 Apr 2005 22:53
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	4.0.x	OS:	Linux (Linux)
Assigned to:	Peter Gulutzan	CPU Architecture:	Any

Description:
There is already an INSERT DELAYED and having an Update Delayed 
would be very useful in speeding up some applications.

A query such as UPDATE table1 SET col=col+1 WHERE x=54; would be a 
great candidate for the feature if col's realtime value is not vital. If this 
query is run very often and runs into locking issues UPDATE DELAYED 
would help.

How to repeat:
N/A

Suggested fix:
N/A

There is an option which is intended for a similar purpose: UPDATE LOW_PRIORITY. To 
quote from the MySQL Reference Manual: "If you specify the keyword LOW_PRIORITY, 
execution of the UPDATE is delayed until no other clients are reading from the table."  So 
please explain exactly what UPDATE DELAYED would do, that UPDATE LOW_PRIORITY 
wouldn't do.

UPDATE LOW_PRIORITY does not free the script to continue on with 
more queries. A user would have to wait for the update to complete.

We will not add this feature at this time, although we see that it has some merit. 
The idea is sound, so if a serious demand arises (from many other users or from 
sponsoring customers) we will have this plan available. Thank you. 
 
UPDATE LOW PRIORITY might still be worth considering, perhaps on a separate 
thread/connection.

I too would like to see this feature.

UPDATE DELAYED would be a great feature to have.  I feel like I am missing an arm because I have INSERT DELAYED but not UPDATE DELAYED.

I vote for this feature. This feature will gain more speed to our system.

We develope web-based software, and it seems that an insert delayed would be a helpfull feature to have.

Please add this feature soon! We'd use it very often too...

I would also very appriciate that feature; I just moved to LOW_PRIORITY and it made my performance boost (because tables stopped getting locked), but, when the table is released, the DB is busy processing all the delayed jobs. Now, I don't care they'll be executed even in 2 days, as long as they are (those are counters) - so DELAYED would make an excellent option for me!

This looks like a wise option. I have a web application that is called about 500,000+ times a day and it would really make a difference if we had UPDATE DELAYED as well.

My vote for this ;-)

cya
<a href="http://www.lafraia.com/">Daniel Lafraia</a>

This looks like a wise option. I have a web application that is called about 500,000+ times a day and it would really make a difference if we had UPDATE DELAYED as well.

My vote for this ;-)

cya
Daniel Lafraia
http://www.lafraia.com/

Was this feature ever implemented?  It would be very helpful to instantly return to the client even before the update has been committed to the database.... would there not also be a performance benefit to queue a number of updates into memory, like INSERT DELAYED does, thereby requiring less individual writes to the database?

I don't know if it has been implemented for 5.0, but it does not work in 4.1.10.  However, I tested the following query:

INSERT DELAYED INTO foo (key) VALUES ('key') ON DUPLICATE KEY UPDATE bar='newBar';

against:

UPDATE foo SET bar='newBar' WHERE key='key';

The execution time of the two queries was identical (0.0010s).  My test database has zero load so I can't speak to whether DELAYED is observed, but from my understanding of mySQL internals, it is.  Any further insight into this workaround, particularly if it's a bad idea for some reason, would be greatly appreciated.

Ah, nevermind.  I just found this gem on the INSERT DELAYED syntax page:

The server ignores DELAYED for INSERT DELAYED ... ON DUPLICATE UPDATE statements.

In closing, I support the addition of an UPDATE DELAYED feature.

We would very, very much like to see UPDATE DELAYED also. And for the completeness, also an DELETE DELAYED.

Right now we're doing a workaround for UPDATE DELAYED by first doing a SELECT from the record, change the values we want to update, and then doing a REPLACE DELAYED INTO ... 
In that way the web-application can at least continue if there is a read-lock on the table. But it's not a very elegant solution. The content of the record could possibly be changed by antoher thread between the SELECT and the REPLACE, and an UPDATE DELAYED would probably also be a lot faster and also work for write-locked tables... 

So we really need it too!

Regards,
Tobias Lind

I would very much like to see UPDATE DELAYED added.

We would very much appreciate this as well. It would help a lot in our particular application.

Update delayed would be very useful for me too.

This feature would be very useful for large-scale MySQL deployments.

Okay, you win.

We now have a way to allow "separate thread" execution of any
statement. It's in alpha in version 5.1. For delayed update,
it works like this:

mysql> set global event_scheduler = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> create event e on schedule at current_timestamp do
       update low_priority t set s1 = s1 + 1;
Query OK, 1 row affected (0.00 sec)

This creates a "scheduled event" which runs UPDATE LOW_PRIORITY
on a separate thread, and then drops itself. There is a lot of
overhead because (a) it always involves an INSERT into another
table in the mysql database (b) the body of the statement becomes
a stored procedure behind the scenes. However, taking into account
the comments that users have expressed here, it looks appropriate.

For a description of all the "Events" features, read Trudy
Pelzer's article on MySQL's dev-zone
http://dev.mysql.com/tech-resources/articles/event-feature.html

Thanks everyone for your votes.

"scheduled event..it always involves an INSERT into another
table in the mysql database"

INSERT for an Update ? It seem to me as an very big overhead.. I don't think it's a solution for a so big amount of UPDATEs what we have.. We have UPDATE at every user access (last_action), at every forum topic read (topic views), insert for every of this is a very big overhead.. It doesn't matter to us when that will happen, even if that will happen at midnight we don't mind.

Hope at least inserting into EVENT table is and INSERT Delayed ;o) Or user will wait until mysql will write to that table..

Update Delayed look very similar to Insert Delayed, I think this will be a very big gift to us, web developers and for simple users too (because they don't want to wait until the mysql will update the table on disk), the time until user will see the page will decrease considerably and much more if there is any table locks..

Hm, just realized what in php you can use register_shutdown_function to call any sql query after the page was sended to the user..

This feature would be very useful.  I noticed this request has been more than 5 years.  Any chance for this feature to be implemented in the near future?

We implemented "events" as described in my comment from January 2006.
This is part of 5.1. 5.1 is now GA. This bug is closed.

Old but usefull open bug..

I just thought about it (and now I am waiting for it until the feature is available) to implement hit counters for arcticles. It is not important, that the hitcounter is 100% precise, but it is important, that it does not slow down the application:

UPDATE DELAYED foo SET hitcounter = hitcounter+1 WHERE id = ?

This just would be quite faster than UPDATE without DELAYED, especially, if the table is locked...

I also vote for this, non critical updates get waiting because table locks and insert delayed will speed up things a lot in this cases