SELECT FOR UPDATE is very slow

[Premal Shah]

I have a table like this:
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| priority | int(11) | NO | | NULL | |
| expiration | datetime | YES | | NULL | |
| pickled_obj | longtext | NO | | NULL | |
| guid | varchar(20) | YES | MUL | NULL | |
| created | datetime | NO | | NULL | |
| name | varchar(20) | NO | MUL | NULL | |
+-------------+-------------+------+-----+---------+----------------+

there are 3 indexes:
1) PRIMARY
2) guid
3) name_expiration

There are 3 types of queries that run on this table
1) SELECT `queue_queueobject`.`id`, `queue_queueobject`.`priority`, `queue_queueobject`.`expiration`, `queue_queueobject`.`pickled_obj`, `queue_queueobject`.`guid`, `queue_queueobject`.`created`, `queue_queueobject`.`name` FROM `queue_queueobject` WHERE (`queue_queueobject`.`name` = 'News_Feeds' AND `queue_queueobject`.`expiration` < '2009-06-15 16:02:09' ) LIMIT 5 FOR UPDATE;
2) DELETE FROM `queue_queueobject` WHERE `id` IN (332134);
3) INSERT INTO `queue_queueobject` (`priority`, `expiration`, `pickled_obj`, `guid`, `created`, `name`) VALUES (5, '0001-01-01 00:00:00', 'Y2NvcHlfcmVnCl9yZWNvbnN0cnVjdG9yCnAxCihjbWVkaWEucm9sbApSb2xsCnAyCmNfX2J1aWx0aW5fXwpvYmplY3QKcDMKTnRScDQKKGRwNQpTJ2lwJwpwNgpOc1MnbWV0YScKcDcKZzEKKGNiYXNlCkxhenlKU09ORGljdApwOApjX19idWlsdGluX18KZGljdApwOQooZHRScDEwCihkcDExClMnX2pzb24nCnAxMgpOc2JzUydndWlkJwpwMTMKUycnCnNTJ3BhcnRuZXJfaWQnCnAxNApMNkwKc1MndGl0bGUnCnAxNQpTJ0FidSBEaGFiaSBUcmlhdGhsb24gLSBQcmVzcyBDb25mZXJlbmNlJwpwMTYKc1MnX3BhcnRuZXJfY2FjaGUnCnAxNwpnMQooY2Jhc2UucGFydG5lcl9sb29rdXAKUGFydG5lcl9Mb29rdXAKcDE4CmczCk50UnAxOQooZHAyMApTJ3BhcnRuZXJfbmFtZScKcDIxClZHZXR0eSBJbWFnZXMKcDIyCnNTJ21ldGEnCnAyMwpnMQooZzgKZzkKKGR0UnAyNAooZHAyNQpTJ19qc29uJwpwMjYKTnNic1MnaWQnCnAyNwpMNkwKc2JzUydjcmVhdGVkX2J5JwpwMjgKUycnCnNTJ3ZlcnNpb24nCnAyOQpJMQpzUydhY3Rvcl9pZCcKcDMwCk5zUydsZWdhY3lfaWQnCnAzMQpOc1MnZGJfY3JlYXRlZCcKcDMyCk5zUydjb250YWluZXJzJwpwMzMKKGxwMzQKZzEKKGNjb250YWluZXIubW9kZWxzCkNvbnRhaW5lcgpwMzUKZzMKTnRScDM2CihkcDM3ClMndXBkYXRlZCcKcDM4CmNkYXRldGltZQpkYXRldGltZQpwMzkKKFMnXHgwN1x4ZDlceDAzXHgxZlx4MGU5XHgwOFx4MDBceDAwXHgwMCcKdFJwNDAKc1MndXBkYXRlZF9ieScKcDQxClYKc1Mnb2JqX2lkJwpwNDIKTDEzTApzUyd0aHVtYm5haWxfaWQnCnA0MwpOc2c2Ck5zUydjcmVhdGVkJwpwNDQKZzM5CihTJ1x4MDdceGQ5XHgwM1x4MWZceDBlOVx4MDhceDAwXHgwMFx4MDAnCnRScDQ1CnNnMjgKVgpzUydjcmVhdG9yX2lkJwpwNDYKTnNnNwpnMQooZzgKZzkKKGR0UnA0NwooZHA0OApnMTIKTnNic1MnZmxhZ3MnCnA0OQpnMQooZzgKZzkKKGR0UnA1MAooZHA1MQpnMTIKTnNic1MncGFydG5lcl9vYmplY3RfaWQnCnA1MgpOc1Mnb2JqZWN0X3R5cGVfaWQnCnA1MwpMMUwKc1Mnc3RhdHVzX2lkJwpwNTQKTDJMCnNnMTUKVlNwb3J0cwpwNTUKc1MnZGVzYycKcDU2ClYKc2cxMwpTJ2NRNGswOWFqc1V5JwpwNTcKc2cxNApOc1MnbmFtZScKcDU4ClZTcG9ydHMKcDU5CnNiYXNnMzgKTnNnNDEKUycnCnNnNTIKUyc4ODQ5MjI5MycKcDYwCnNTJ2lzX2RlbGV0ZWQnCnA2MQpJMDAKc1Mnb3RoZXJfdGl0bGUnCnA2MgpnMTYKc2c1NgpTJycKc1Mnb3JpZ2luYWxfdmVyc2lvbicKcDYzCk5zZzQyCk5zUydpc19sb2NrZWQnCnA2NApJMDAKc2c0NApnMzkKKFMnXHgwN1x4ZDlceDA2XHgwZlx4MDBceDAwXHgwMFx4MDBceDAwXHgwMCcKdFJwNjUKc1MnX29iamVjdF90eXBlX2NhY2hlJwpwNjYKZzEKKGNiYXNlLmxvb2t1cApMb29rdXAKcDY3CmczCk50UnA2OAooZHA2OQpTJ29iamVjdF90eXBlJwpwNzAKVnBhcnRuZXJfYWxidW0KcDcxCnNTJ2lkJwpwNzIKTDIxTApzUydpc192ZXJzaW9uZWQnCnA3MwpJMApzYnNTJ29sZF92ZXJzaW9uJwpwNzQKSTAKc2c0NgpOc2c1MwpMMjFMCnNTJ2dldHR5X2NhdGVnb3J5JwpwNzUKUydTcG9ydCcKcDc2CnNiLg==', '88492293', '2009-06-15 15:39:39', 'NEWS_Feeds');

These queries are executed by few processes which run in parallel ie. some will INSERT and others will SELECT and DELETE. Its basically a database-based queuing system.

There are at most 500 rows in this table. I need to SELECT FOR UPDATE so that one process does not get rows which the other process already got. The SELECT FOR UPDATE takes over 30 seconds. Im suspecting a lot of lock contention.

I also get a lot of deadlocks. (mebbe this info will help)
------------------------
LATEST DETECTED DEADLOCK
------------------------
090615 16:04:22
*** (1) TRANSACTION:
TRANSACTION 0 69395908, ACTIVE 27 sec, process no 9572, OS thread id 1081510224 updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1216, undo log entries 2
MySQL thread id 252886, query id 186831792 172.30.11.105 zimbio updating
DELETE FROM `queue_queueobject` WHERE `id` IN (338328)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 908 page no 5 n bits 592 index `queue_queueobject_name_expiration` of table `zimbio/queue_queueobject` trx id 0 69395908 lock_mode X locks rec but not gap waiting
Record lock, heap no 456 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 10; hex 4e6577735f4665656473; asc News_Feeds;; 1: len 8; hex 80001245b5fae9d1; asc E ;; 2: len 4; hex 80052998; asc ) ;;

*** (2) TRANSACTION:
TRANSACTION 0 69395917, ACTIVE 26 sec, process no 9572, OS thread id 1090111824 starting index read, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216
MySQL thread id 247785, query id 186831845 172.30.11.105 zimbio Sending data
SELECT `queue_queueobject`.`id`, `queue_queueobject`.`priority`, `queue_queueobject`.`expiration`, `queue_queueobject`.`pickled_obj`, `queue_queueobject`.`guid`, `queue_queueobject`.`created`, `queue_queueobject`.`name` FROM `queue_queueobject` WHERE (`queue_queueobject`.`name` = 'News_Feeds' AND `queue_queueobject`.`expiration` < '2009-06-15 16:03:49' ) LIMIT 5 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 908 page no 5 n bits 592 index `queue_queueobject_name_expiration` of table `zimbio/queue_queueobject` trx id 0 69395917 lock_mode X locks rec but not gap
Record lock, heap no 456 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 10; hex 4e6577735f4665656473; asc News_Feeds;; 1: len 8; hex 80001245b5fae9d1; asc E ;; 2: len 4; hex 80052998; asc ) ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 908 page no 901 n bits 80 index `PRIMARY` of table `zimbio/queue_queueobject` trx id 0 69395917 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
0: len 4; hex 80052998; asc ) ;; 1: len 6; hex 00000422e5c4; asc " ;; 2: len 7; hex 000000003a1e7d; asc : };; 3: len 4; hex 8000000a; asc ;; 4: len 8; hex 80001245b5fae9d1; asc E ;; 5: len 30; hex 59324e7663486c66636d566e436c39795a574e76626e4e30636e566a6447; asc Y2NvcHlfcmVnCl9yZWNvbnN0cnVjdG;...(truncated); 6: len 0; hex ; asc ;; 7: len 8; hex 80001245b5faddb7; asc E ;; 8: len 10; hex 4e6577735f4665656473; asc News_Feeds;;

*** WE ROLL BACK TRANSACTION (2)


Pls advice. Thanx in advance.