MySQL Bugs: #24769: Delayed queries

Bug #24769	Delayed queries
Submitted:	2 Dec 2006 0:30	Modified:	21 Mar 2011 3:55
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any (all)
Assigned to:		CPU Architecture:	Any
Tags:	DELAYED

Description:
hello, today we have insert delayed that put query in another thread and execute it after table isn't locked, could we do the same thing for delete and update?
for example
i'm update an row field4=1 and i will use field2 after 10 seconds if updated return now, but if field4=1 wait table to be unlocked field2 will just be fetched after 10seconds+time to unlock. ok i will not field4 now, but i will use field2. to workaround i'm using to connections and two thread to work with database, could we implement update delayed and when table get unlocked update will occur? ok we will have problems if computer power down before updating, no problems... 
the same thing could be done for delete, we have an optimization on delete quick, but an delete quick delayed could be more fast to return to program execution
thanks guys!

How to repeat:
update delayed table set field_notused_yet=value  where where_condition
select field_used_now from table  where where_condition

or 

delete delayed from table where field1='Y'
select rows from table where field1='N'

select could execute without wait delete... :)

Suggested fix:
maybe more implementation on delayed threads

Thank you for a problem report. Please, specify the exact version (5.1.x) used and send a complete test case that demonstrates the behaviour described.

sorry it's an feature request, not implemented yet
see the question:
i have 3 process, two realtime and one 30 every seconds (not realtime and the information don't need to be realtime), just one table for 3 process

one process use fields 1,2,3 of table
other use 1(primary key) 4,5
the 30 seconds use fields 3 and 4

the first real time process make 1000 updates per second, the seconds real time just change after 5 seconds but wait +- .5 seconds on my development server (pentium 3 650) the production server don't have problem (sun fire)
.5 seconds make first process 1000 updates go to 500 updates 
the 30 seconds don't lock table for many time and don't get updates slower

if i could make the seconds real time process use update delayed i think that server could wait an good time to update table ok and don't get my 1000 updates to 500 updates

the main problem is locking (because poor hardware, i know), i think that update delayed could help, no problems with mysql server but this could help me a lot for faster applications :) thankx

Please, check last comment in bug #849. Can that event-based solution help you?

i tried event scheduler but delayed insert make an pool per table (in manual it's something like this) and execute every insert in that table once

using update could help by this way
i don't think that insert into another table is a good idea, it's an work around ok but update delayed is easier for every one that want to change updates to update delayed, and create event is not pretty work around

well i will wait UPDATED DELAYED and DELETE DELAYED 
today i'm using UPDATE LOW_PRIORITY but script is locked until it execute
i'm using too DELETE QUICK to get more speed, but i need OPTIMIZE TABLE sometimes

delete delayed and update delayed could be an very good feature

and see... 15 Jul 2003!!! 3 years ago someone had this problem... bug 849! we are on 24769! i didn't checked mysql C source to know if its difficult to implement but would be nice implement

thankx

I think, this feature request is still reasonable.

UPDATE DELAYED is hardly possible since we can't know if UPDATE fails or not in advance, and thus can't return "SUCCESS" to the client until it's completed.
In other words, only one variant of UPDATE DELAYED is at all conceivable: UPDATE DELAYED IGNORE.

DELETE DELAYED has a similar issue when we also take constraints and triggers into account.

My personal opinion is that the server can not be a "jack of all traids", and also do update or delete buffering. This feature can be better implemented outside, e.g. in a proxy or in the application itself.

insert delayed ........

does insert delayed have options for ignore?
yes?! put on update too :) no problems :)
replace could have this too?

just for delayed

reading again...
UPDATE DELAYED IGNORE could make me happy :)

it's not engine based right?! for example:
INSERT DELAYED only work with myisam (my tests), and concurrent insert work better (it's not a queue) than insert delayed...

the problem here is: a queue for update and delete, since insert can work with concurrent inserts

maybe MVCC could help here... (it's near a dirty read)