why would these two SQL statements resulted an operational deadlock error?

I have this table:

CREATE TABLE `notify` (
  `id` int(11) NOT NULL auto_increment,
  `notify_type` char(1) NOT NULL,
  `notify_id` int(10) unsigned NOT NULL,
  `create_time` timestamp NOT NULL default '0000-00-00 00:00:00',
  `user` int(10) unsigned NOT NULL,
  `update_time` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `notify_id` (`notify_id`,`notify_type`,`user`),
  KEY `Index_time` (`create_time`),
  KEY `user_update` (`user`,`update_time`)
)

and two SQL queries executed consecutively and then committed together

if user_id:
    insert into notify (notify_type,notify_id,user,create_time) 
    values (%s,%s,%s,CURRENT_TIMESTAMP) 
    ON DUPLICATE KEY UPDATE update_time=update_time, (type, id, user_id)

    update notify set update_time=NOW() where notify_type = %s 
    and notify_id =%s and user!=%s,(type, id, user_id)
else:
    update reply_notify s开发者_如何学运维et update_time=NOW() where notify_type = %s 
    and notify_id =%s, (type, id)
commit()

it often resulted in

OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

Would anyone could help me figure out why it is? I've consulted the MySQL document, I doubt there is some thing related to the ON DUPLICATE KEY UPDATE clause, but I still can't figure it out.

Could it be some code execute to the if branch, while other code execute to the else branch, and then those two transaction deadlocked?

---

According to MySQL's doc, your query looks fine. However there is a bug that may be related.

You can try switching the engine used by the table, in 5.0 you can try BDB (Berkeley DB) but it is unsupported now, otherwise I afraid the best bet is MyISAM.

Or, putting LOCK TABLES ... UNLOCK TABLES around the insert may solve the problem. Failing that, you can try to put lock tables around the whole block (including the updates) to see whether it is really this part that is giving you trouble.

Keep in mind that table lock is usually not good for performance, and MyISAM is not designed for frequent update, but you may not have much choice.

---

lock tables notify write;
insert into notify (notify_type,notify_id,user,create_time) 
values (%s,%s,%s,CURRENT_TIMESTAMP) 
ON DUPLICATE KEY UPDATE update_time=update_time, (type, id, user_id);
unlock tables;