MySQL Forums
Forum List  »  Performance

SELECT FOR UPDATE is very slow
Posted by: Premal Shah
Date: June 15, 2009 05:39PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
SELECT FOR UPDATE is very slow
10644
June 15, 2009 05:39PM
3901
June 15, 2009 11:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.