Sarat,
are you using triggers in MySQL-5.0? It is waiting for a table lock on a table that is not even mentioned in the UPDATE statement. Or are you updating a view?
"
update user set inner_label=NULL, outer_label=NULL where user_id=401331
...
TABLE LOCK table `etrace_prod_dbo/gwuser` trx id 0 19686195 lock mode X waiting
"
InnoDB does use row-level locking. I need to check what kind of table locking MySQL uses in triggers and views.
Please post an unedited printout of a deadlock. It was not clear to me if you had edited it in some way.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php