Why does mysql throw error message "DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at XXX line XXX" ?

Issue

• On one of our primary database server we are facing very uncommon and abnormal MySQL error. We are facing below error randomly in a perl program which is running on other server. Following error found :

[Sun Aug 28 01:50:12 PDT 2011] Error : DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at se/cm/se026.pl line 1839.

Here se026.pl is a program name.

And when this error is generating the below sql statement program is trying to execute with MySQL database server.

INSERT INTO tmp_locid SELECT DISTINCT(sd.customerlocid) FROM standarddraw sd,tmp_distlist tmpDisList,tmp_distpubidlist tmpDisPubList WHERE sd.clientlocid =tmpDisList.distLocIds AND sd.productid = tmpDisPubList.distPubIds AND ( sd.endeffdt IS NULL OR sd.endeffdt = '0000-00-00' OR sd.endeffdt > now() )

Now, most typical part is the table on which the statement is applying is a temporary table with below structure and sql statement one after another.

Drop TEMPORARY TABLE  if exists tmp_locid
CREATE TEMPORARY TABLE if not exists tmp_locid( tempLocIds bigint(20) NOT NULL, KEY (tempLocIds)) ENGINE=MyISAM

• It is very hard to believe why such misbehaving is generating because as per our knowledge "Temporary Table is also connection dependent and here this program is always running in single thread." Above this during every run temporary table first dropped and then created again. But although this the error is prompting and disturbing us.