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

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

Thank you very much.
Your two solutions are same as what I figure out last night? I read the
document of MySQL and find the default isolation level is
repeatable-read.Improve this level  or use a single update statement maybe
solve my problem.

:)

I am trying now.

This is not a bug,Sorry for that.

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


On Sun, Oct 30, 2011 at 10:00 PM, Artyom Beilis <art...@ya...> wrote:

> To be honest I don't know I don't think it is problem with cppdb unless
> you show some specific case that I can reproduce.
>
> I can see
>
> 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)
> 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)
>
> Which looks exactly the same so it is not inconsistent.
> Maybe innodb allows to do updates if they do not conflict?
>
> I'd suggest to recheck carefully the SQL and other assumptions that
> transaction
> isolation provides (especially in MySQL).
>
> I'd also suggest to do this in one operation like this
>
> stmt = sql << "update event_prize set account_id=? ,award_time=now() where
> prizeId=? and account_id is NULL" << ....
> stmt.exec()
> if(stmt.affected() > 0)
>   //  awarded
> else
>   // not awarder
>
>
> ------------------
>
> About the isolation level.
>
> Shouldn't
>
>     sql << "SET SESSION TRANSACTION ISOLATION Level" << cppdb::exec;
>
> work?
>
> Artyom Beilis
> --------------
> CppCMS - C++ Web Framework: http://cppcms.sf.net/
> CppDB - C++ SQL Connectivity: http://cppcms.sf.net/sql/cppdb/
>
> ------------------------------
> *From:* 陈抒 <csf...@gm...>
> *To:* cpp...@li...
> *Sent:* Sunday, October 30, 2011 2:05 PM
> *Subject:* Re: [Cppcms-users] Bug about transaction in cppdb
>
> 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
>
>
>
>
> ------------------------------------------------------------------------------
> Get your Android app more play: Bring it to the BlackBerry PlayBook
> in minutes. BlackBerry App World™ now supports Android™ Apps
> for the BlackBerry&reg; PlayBook™. Discover just how easy and simple
> it is! http://p.sf.net/sfu/android-dev2dev
>
> _______________________________________________
> Cppcms-users mailing list
> Cpp...@li...
> https://lists.sourceforge.net/lists/listinfo/cppcms-users
>
>
>
>
> ------------------------------------------------------------------------------
> Get your Android app more play: Bring it to the BlackBerry PlayBook
> in minutes. BlackBerry App World™ now supports Android™ Apps
> for the BlackBerry® PlayBook™. Discover just how easy and simple
> it is! http://p.sf.net/sfu/android-dev2dev
>
> _______________________________________________
> Cppcms-users mailing list
> Cpp...@li...
> https://lists.sourceforge.net/lists/listinfo/cppcms-users
>
>