mysql - Explain inexplicable deadlock -

first of all, don't see how getting any deadlock @ all, since using no explicit locking, there's 1 table involved, there's separate process each insert, select, , update rows, 1 row inserted or updated @ time, , each process (perhaps once minute) runs @ all.

it's email queue:

create table `emails_queue` (   `id` varchar(40) not null,   `email_address` varchar(128) default null,   `body` text,   `status_time` timestamp not null default current_timestamp on update current_timestamp,   `status` enum('pending','inprocess','sent','discarded','failed') default null,   key `status` (`status`),   key `status_time` (`status`,`status_time`) ) engine=innodb default charset=latin1  

the generating process, in response user action every 90 seconds, insert table, setting status "pending".

there's monitoring process every minute checks number of "pending" , "failed" emails not excessive. takes less second run , has never given me trouble.

every minute, sending process grabs pending emails. loops through , 1 email @ time, sets status "inprocess", tries send it, , sets status accordingly "sent", "discarded" (it has reasons deciding email shouldn't go out), or "failed" (rejected smtp system).

the statement setting status unusual.

update emails_queue set status=?, status_time=now() id=? , status = ? 

that is, update status if current status believe be. before mechanism, accidentally kicked off 2 sending processes , each try send same email. now, if happen, 1 process move email "pending" "inprocess", second 1 update 0 rows, realize there's problem, , skip email.

the problem is, 1 time in 100, update fails altogether! com.mysql.jdbc.exceptions.jdbc4.mysqltransactionrollbackexception: deadlock found when trying lock; try restarting transaction

wth?

this table , query happens , happens in production (to maximize difficulty in investigating it).

the 2 things seem @ unusual (1) updating column participates in clause, , (2) (unused) automatic updating of status_time.

i'm looking suggestions or diagnostic techniques.

firstly, deadlocks not depend on explicit locking. mysql's lock table or using non-default transaction isolation modes not required have deadlock. can still have deadlocks if never use explicit transaction.

deadlocks can happen on single table, quite easily. commonly it's single hot table.

deadlocks can even happen if transactions single row insert.

a deadlock can happen if have

• more 1 connection database (obviously)
• any operation internally involves more 1 lock.

what not obvious, of time, single row insert or update involves more 1 lock. reason secondary indexes need locked during inserts / updates.

selects won't lock (assuming you're using default isolation mode, , aren't using update) can't cause.

show engine innodb status friend. give bunch of (admittedly confusing) information deadlocks, specifically, recent one.

• you can't eliminate deadlocks, continue happen in production (even on test systems if stress them properly)
• aim low amount of deadlocks. if 1% of transactions deadlock, possibly many.
• consider changing transaction isolation level of transactions read-committed if understand implications
• ensure software handles deadlocks appropriately.