错误信息

exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction' in /var/www/monitor/dev/yii/framework/db/CDbCommand.php:354
Stack trace:
#0 /var/www/monitor/dev/protected/commands/cron/Comment2AttrIdCommand.php(272): CDbCommand->execute()
#1 /var/www/monitor/dev/protected/commands/cron/Comment2AttrIdCommand.php(17): Comment2AttrIdCommand->findAttrId()
#2 /var/www/monitor/dev/yii/framework/console/CConsoleCommandRunner.php(65): Comment2AttrIdCommand->run(Array)
#3 /var/www/monitor/dev/yii/framework/console/CConsoleApplication.php(91): CConsoleCommandRunner->run(Array)
#4 /var/www/monitor/dev/yii/framework/base/CApplication.php(162): CConsoleApplication->processRequest()
#5 /var/www/monitor/dev/protected/commands/run_cron.php(17): CApplication->run()

问题代码A

$sql = "update comment2
    set attr_flag=101, attr_id=concat('2_', b2c_id), purchase_date={$date}
    where comment_time>={$db_indus->quoteValue($time_s)}
    and comment_time<{$db_indus->quoteValue($time_e)}
    and attr_flag=0
    and auction_sku=''";
$db_indus->createCommand($sql)->execute(); 

参考资料

http://www.cnblogs.com/crazylqy/p/7689447.html

初步结论

评论表的建表信息如下

CREATE TABLE `comment2` (
  `b2c` int(11) NOT NULL,
  `b2c_id` varchar(80) DEFAULT NULL,
  `b2c_shop_id` varchar(80) DEFAULT NULL,
  `comment_id` varchar(80) NOT NULL,
  `auction_sku` varchar(255) DEFAULT NULL,
  `comment_content` text,
  `comment_pics` text,
  `comment_time` datetime DEFAULT NULL,
  `position` text,
  `position_flag` tinyint(3) NOT NULL DEFAULT '0',
  `comment_nick` varchar(80) DEFAULT NULL,
  `tamllSweetLevel` tinyint(4) DEFAULT NULL,
  `tmallSweetPic` varchar(80) DEFAULT NULL,
  `reply_content` text,
  `append_comment_id` varchar(80) DEFAULT NULL,
  `append_comment_content` text,
  `append_comment_pics` text,
  `append_comment_time` datetime DEFAULT NULL,
  `append_reply_content` text,
  `reply_time` datetime DEFAULT NULL,
  `attr_id` varchar(255) NOT NULL DEFAULT '',
  `attr_flag` tinyint(3) NOT NULL DEFAULT '0' COMMENT'0:未处理;1:处理失败,solidify_item中无相应记录,使用父类ID;2:处理失败,超过14天直接使用父类ID;100:处理成功,子类;101:处理成功,父类',
  `purchase_date` int(8) NOT NULL DEFAULT '0' COMMENT '以solidify_item中<=评论日期的最晚记录的日期作为宝贝购买日期',
  `insert_date` int(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_id`),
  KEY `idx_comment_time` (`comment_time`),
  KEY `idx_item` (`b2c_id`,`comment_time`),
  KEY `idx_insert_date` (`insert_date`,`b2c_id`,`comment_time`),
  KEY `idx_attr_flag` (`attr_flag`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8

$sql = "insert into comment2 (b2c, insert_date, b2c_id, b2c_shop_id, comment_id, auction_sku, comment_content, comment_pics, comment_time, comment_nick, reply_content, append_comment_id, append_comment_content, append_comment_pics, append_reply_content, reply_time) values {$commentStr} 
on duplicate key update auction_sku = values(auction_sku), 
comment_content = values(comment_content), 
comment_pics = values(comment_pics), 
comment_nick = values(comment_nick), 
reply_content = values(reply_content), 
append_comment_content = values(append_comment_content), 
append_comment_pics = values(append_comment_pics), 
append_reply_content = values(append_reply_content)";

考虑代码A与评论爬虫脚本的以上代码B产生死锁;
A会给二级索引idx_comment_time和聚簇索引PRI的相关记录分别设排他锁，
B会在插入前申请插入意向锁，插入后给PRI的相关记录设共享锁，
由于加锁顺序不同，可能产生死锁。

修改如下

// 预处理----------------------------------------------------------------------------------------------------------------
while(1){
    $sql = "select comment_id 
        from comment2 
        where comment_time>={$db_indus->quoteValue($time_s)}
        and comment_time<{$db_indus->quoteValue($time_e)}
        and attr_flag=0
        limit {$limit}";
    $comment_ids = $db_indus->createCommand($sql)->queryColumn();
    if($comment_ids){
        $comment_ids_str = "'".implode("','", $comment_ids)."'";
        $sql = "update comment2 set attr_flag=9 where comment_id in ({$comment_ids_str})";
        $db_indus->createCommand($sql)->execute();
    }else{
        break;
    }
}

// 更新auction_sku=""
$sql = "update comment2
    set attr_flag=101, attr_id=concat('2_', b2c_id), purchase_date={$date}
    where attr_flag=9
    and auction_sku=''";
$db_indus->createCommand($sql)->execute();

现将现有评论的标志位更新到中间状态9，与后续插入的comment_id隔离开来