Re: [Cppcms-users] Bug about transaction in cppdb

[陈抒 <csf...@gm...>]

My table is using InnoDB engin.

 | event_prize | CREATE TABLE `event_prize` (
  `prizeId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type_id` int(10) unsigned NOT NULL COMMENT '奖品类别Id',
  `promo_id` int(10) unsigned NOT NULL COMMENT '活动Id',
  `account_id` int(10) unsigned DEFAULT NULL COMMENT '获奖人',
  `probility` int(10) unsigned DEFAULT NULL COMMENT '1-100之间，进入计算时会除以5000',
  `flag` smallint(5) unsigned DEFAULT '0' COMMENT
'0：用户可以在任何活动中反复获得;1:只能在一个活动中反复获得；2：每个活动中最多获得一个；3：只能在一个活动中获得一个；4：每个时间段内都确保发出;',
  `status` smallint(5) unsigned DEFAULT '1' COMMENT '1激活 0 冻结',
  `info` varchar(256) DEFAULT NULL COMMENT '保存奖品独特的特征信息',
  `quantity` smallint(5) unsigned DEFAULT '0' COMMENT '一个奖品中标明的数量，比如金额',
  `award_time` datetime DEFAULT NULL,
  PRIMARY KEY (`prizeId`),
  KEY `index_prize_event_promo` (`promo_id`),
  KEY `index_prize_event_type` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1174582 DEFAULT CHARSET=utf8

陈抒
Best regards
http://blog.csdn.net/sheismylife


On Sun, Oct 30, 2011 at 8:04 PM, 陈抒 <csf...@gm...> wrote:

> Hello,Artyom:
>
>   I run into one problem,I try to update one record in one MySQL table
> using transaction like so:
>
>   static shared_ptr<prize> award(shared_ptr<prize> p,shared_ptr<user>
> user, int promo_id){
>     my_application & app = app_singleton_holder::Instance();
>     cppdb::session session(app.config().master_mysql_uri);
>     cppdb::statement stat;
>
>     cppdb::transaction guard(session);
>     cppdb::result res = session <<"select account_id from event_prize
> where prizeId = ? and account_id is NULL"<< p->id();
>     unsigned int account_id;
>     if(res.next() ){
>  stat = session << "update event_prize set account_id=? ,award_time=now()
> where prizeId=?"
>    << user->account_id() << p->id();
>  stat.exec();
> guard.commit();
>  //发送站内短信
> send_message(p , user , promo_id);
> BOOSTER_NOTICE("award") << "obtain a real prize, the account_id is:" <<
> user->account_id() << ".the prize_id is:" << p->id();
>  return p;
>     }else{
> guard.rollback();
>  BOOSTER_NOTICE("award") << "already have got a prize, the account_id
> is:"<< user->account_id() << ".the prize_id is:" << p->id();
>  return create_default_prize(user,promo_id);
>     }
>   }
>
>   Then I write a Java test application using TestNG to test my service
> with 50 threads.But the service updates one record twice.Here is my log:
>
> 20071:2011-10-30 11:48:37 GMT; award, notice: obtain a real prize, the
> account_id is:2011.the prize_id is:906326 (default_prize_policy.h:196)
> 20074:2011-10-30 11:48:37 GMT; award, notice: already have got a prize,
> the account_id is:2011.the prize_id is:906326 (default_prize_policy.h:200)
> 20121:2011-10-30 11:48:37 GMT; award, notice: obtain a real prize, the
> account_id is:2011.the prize_id is:906326 (default_prize_policy.h:196)
>
>   Maybe it's a bug.Another question,How to change the transaction
> isolation level for MySQL?
>
>
> 陈抒
> Best regards
> http://blog.csdn.net/sheismylife
>