Re: Do SELECT statements create some kind of row/table lock?
Here is the information you requested. Thanks.
SHOW CREATE TABLE:
Create Table: CREATE TABLE `channel_diggs` (
`digg_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`channel_id` int(10) unsigned NOT NULL DEFAULT '0',
`rave_count` int(10) unsigned NOT NULL DEFAULT '0',
`created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`digg_id`),
KEY `newindex` (`created_date`),
KEY `uid_cid` (`user_id`,`channel_id`),
KEY `IDX_cd_channel_id` (`channel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24290704 DEFAULT CHARSET=latin1
TABLE STATUS: Name: channel_diggs
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 14008191
Avg_row_length: 45
Data_length: 642777088
Max_data_length: 0
Index_length: 1090355200
Data_free: 7340032
Auto_increment: 24290705
Create_time: 2012-08-16 09:02:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
INDEXES:
*************************** 1. row ***************************
Table: channel_diggs
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: digg_id
Collation: A
Cardinality: 14008191
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: channel_diggs
Non_unique: 1
Key_name: newindex
Seq_in_index: 1
Column_name: created_date
Collation: A
Cardinality: 14008191
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: channel_diggs
Non_unique: 1
Key_name: uid_cid
Seq_in_index: 1
Column_name: user_id
Collation: A
Cardinality: 875511
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: channel_diggs
Non_unique: 1
Key_name: uid_cid
Seq_in_index: 2
Column_name: channel_id
Collation: A
Cardinality: 14008191
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: channel_diggs
Non_unique: 1
Key_name: IDX_cd_channel_id
Seq_in_index: 1
Column_name: channel_id
Collation: A
Cardinality: 2801638
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
This is the UPDATE statement that appears to be causing locks:
UPDATE __make_population_cache mpc SET
number_of_diggs =
(
SELECT count(digg_id)
FROM kaneva.channel_diggs cd
INNER JOIN kaneva.communities_game cg ON cg.community_id = cd.channel_id
WHERE mpc.game_id = cg.game_id AND
cd.created_date >= DATE_SUB(DATE(NOW()),INTERVAL 7 DAY)
);
There are indexes on the various key/join columns used by this update.
I cannot get an explain plan from this statement because it's an UPDATE, and since the SELECT is correlated to the UPDATE, I can't get an explain plan on it, either. However, I'm not updating the channel_diggs table. So, I'm puzzled why there would be locks on it at all. Sometimes this update will run quite quickly. Other times it takes several minutes to complete.
Thanks,
Dave