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

[Artyom B. <art...@ya...>]

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
>
>
>