You might try having that delete
job operate by first inserting the key of each row to be deleted into a temp table like this pseudocode
create temporary table deletetemp (userid int);
insert into deletetemp (userid)
select userid from onlineusers where datetime <= now - interval 900 second;
delete from onlineusers where userid in (select userid from deletetemp);
Breaking it up like this is less efficient but it avoids the need to hold a key-range lock during the delete
.
Also, modify your select
queries to add a where
clause excluding rows older than 900 seconds. This avoids the dependency on the cron job and allows you to reschedule it to run less often.
Theory about the deadlocks: I don't have a lot of background in MySQL but here goes... The delete
is going to hold a key-range lock for datetime, to prevent rows matching its where
clause from being added in the middle of the transaction, and as it finds rows to delete it will attempt to acquire a lock on each page it is modifying. The insert
is going to acquire a lock on the page it is inserting into, and then attempt to acquire the key lock. Normally the insert
will wait patiently for that key lock to open up but this will deadlock if the delete
tries to lock the same page the insert
is using because thedelete
needs that page lock and the insert
needs that key lock. This doesn't seem right for inserts though, the delete
and insert
are using datetime ranges that don't overlap so maybe something else is going on.
http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html