您可以尝试通过首先将要删除的每一行的键插入到临时表(如下面的伪代码)来操作该删除作业

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);

像这样分解它的效率较低，但它避免了在删除过程中持有key-range锁的需要。

另外，修改选择查询以添加where子句，排除时间超过900秒的行。这避免了对cron作业的依赖，并允许您重新安排它以减少运行频率。

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

当两个事务相互等待以获得锁时，就会发生死锁。例子:

Tx 1:锁定A，然后锁定B Tx 2:锁定B，然后是A

关于死锁有许多问题和答案。每次插入/更新/或删除一行时，都会获得一个锁。为了避免死锁，必须确保并发事务不会按照可能导致死锁的顺序更新行。一般来说，即使在不同的事务中，也尽量以相同的顺序获取锁(例如，总是先获取表A，然后获取表B)。

Another reason for deadlock in database can be missing indexes. When a row is inserted/update/delete, the database needs to check the relational constraints, that is, make sure the relations are consistent. To do so, the database needs to check the foreign keys in the related tables. It might result in other lock being acquired than the row that is modified. Be sure then to always have index on the foreign keys (and of course primary keys), otherwise it could result in a table lock instead of a row lock. If table lock happen, the lock contention is higher and the likelihood of deadlock increases.

delete语句可能会影响表中全部行的很大一部分。最终，这可能导致在删除时获得表锁。持有一个锁(在本例中是行锁或页锁)并获得更多锁始终存在死锁风险。然而，我不能解释为什么插入语句会导致锁升级——它可能与页面分割/添加有关，但更了解MySQL的人必须在那里填写。

首先，值得尝试立即为delete语句显式地获取一个表锁。请参阅锁表和表锁定问题。

一个可以帮助解决大多数死锁的简单技巧是按特定顺序对操作进行排序。

当两个事务试图以相反的顺序锁定两个锁时，就会出现死锁，例如:

连接1:锁键(1)、锁键(2); 连接2:锁键(2)、锁键(1);

如果两者同时运行，连接1将锁定密钥(1)，连接2将锁定密钥(2)，并且每个连接将等待另一个释放密钥->死锁。

现在，如果你改变了你的查询，这样连接就会以相同的顺序锁定键，即:

连接1:锁键(1)、锁键(2); 连接2:锁键(1)、锁键(2);

这样就不可能出现僵局。

这就是我的建议:

确保除了delete语句外，没有其他查询一次锁定多个键的访问权。如果你这样做了(我怀疑你这样做了)，将它们在(k1,k2，..kn)中的WHERE按升序排列。 修正你的delete语句以升序工作:

改变

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

To

DELETE FROM onlineusers 
WHERE id IN (
    SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND 
    ORDER BY id
) u;

另一件需要记住的事情是，MySQL文档建议在出现死锁的情况下，客户端应该自动重试。您可以将此逻辑添加到客户端代码中。(比如，在放弃之前，在这个特定的错误上重试3次)。

您可以尝试通过首先将要删除的每一行的键插入到临时表(如下面的伪代码)来操作该删除作业

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);

像这样分解它的效率较低，但它避免了在删除过程中持有key-range锁的需要。

另外，修改选择查询以添加where子句，排除时间超过900秒的行。这避免了对cron作业的依赖，并允许您重新安排它以减少运行频率。

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

对于使用Spring的Java程序员，我使用一个AOP方面避免了这个问题，该方面可以自动重试陷入瞬时死锁的事务。

更多信息请参见@RetryTransaction Javadoc。