Issue: db.atomic() select query cache problem
Author: serkandaglioglu
State: closed
Created: 2015-09-05 14:56:26

When i use transaction with with db.atomic() after delete or update queries, selecting from database still get same results. 

For example, deleting a user.

```
with db.atomic() as txn:
    User.get(User.user_id==3).delete_instance()
```

After delete query  `User.select()` still include user (user_id=3). If I use transaction with `db.begin()` `db.commit()` there is no problem,  `User.select()` doesn't include deleted user(user_id=3)


Can you provide more information? What database are you using? What is actually failing? You've shown the delete call, but not the select portion.

I added a test-case and it is passing, so I'm confused what might be going wrong:

``` diff

+
+    def test_atomic_with_delete(self):
+        for i in range(3):
+            User.create(username='u%s' % i)
+
+        with test_db.atomic():
+            User.get(User.username == 'u1').delete_instance()
+
+        usernames = [u.username for u in User.select()]
+        self.assertEqual(sorted(usernames), ['u0', 'u2'])
```

If you have a query object, perform a delete/update, then re-iterate the query then yes, you will get cached results. Simply call `query.clone()` in that case to blow away the result cache.


I use mysql, flask and PooledMySQLDatabase.

Database Connection

```
PooledMySQLDatabase(database,**{
            "passwd": password, "user": user,
            "max_connections":None,"stale_timeout":None,
            "threadlocals" : True
        })
```

Deleting

```
class DeleteController(OAuthApiController, BaseMethodView):

    def __init__(self):
        super(DeleteController, self).__init__()

    def get(self,id):
        data = {"error" : "no", "message" : g._("Deleting tweet from twitter takes a long time")}

        from flask import copy_current_request_context
        @async
        @copy_current_request_context
        def deleteFromTwitter(oAuthItemReply):
            app.preprocess_request()
            time.sleep(random.randint(60,80))
            try:
                try:
                    tweepyOauth = tweepy.OAuthHandler(app.config["TWITTER"]["consumer_key"], app.config["TWITTER"]["consumer_secret"])
                    tweepyOauth.set_access_token(oAuthItemReply.send_oauth_account.oauth_token, oAuthItemReply.send_oauth_account.oauth_token_secret)
                    twitterApi = tweepy.API(tweepyOauth,parser=tweepy.parsers.JSONParser())
                    a = twitterApi.destroy_status(oAuthItemReply.send_item.oauth_item_id)
                    print "deleted >> " + str(oAuthItemReply.send_item.oauth_item_id)
                except tweepy.error.TweepError, e:
                    oauthError = e.message
                    print oauthError

                with g.db.connection.atomic() as txn:
                    oAuthItemReply.delete_instance()
            except Exception, e:
                print e

            try:
                g.db.disconnect()
            except Exception, e:
                print e

            return "ok"

        try:
            with g.db.connection.atomic() as txn:
                RepliedOAuthItemAlias = OAuthItem.alias()
                SendOAuthItemAlias = OAuthItem.alias()
                oAuthItemReply = (OAuthItemReply
                                .select(OAuthItemReply,RepliedOAuthItemAlias,SendOAuthItemAlias,OAuthAccount,User)
                                .join(RepliedOAuthItemAlias,on=(OAuthItemReply.replied_item==RepliedOAuthItemAlias.id) )
                                .switch(OAuthItemReply)
                                .join(SendOAuthItemAlias,on=(OAuthItemReply.send_item==SendOAuthItemAlias.id) )
                                .switch(OAuthItemReply)
                                .join(OAuthAccount)
                                .switch(OAuthItemReply)
                                .join(User)                             .where(OAuthItemReply.organization==g.auth.getOrganizationId())
                                .where(OAuthItemReply.id==id)
                                .get()
                    )
                oAuthItemReply.send_status_id = OAuthItemSendStatusEnum.DELETING.value
                oAuthItemReply.save(only=oAuthItemReply.dirty_fields)

                # call thread
                deleteFromTwitter(oAuthItemReply)

                data["oauth_item_reply"] = model_to_dict(oAuthItemReply, exclude = [OAuthItemReply.reply_thread, OAuthItemReply.organization, User.password],recurse = True)        
        except Exception, e:
            data["error"] = "yes"
            data["message"] = g._("An error occured")
            print e
        return self.response(data)
```

Selecting

```
class ItemsTwitterController(OAuthApiController, BaseMethodView):

    def __init__(self):
        super(ItemsTwitterController, self).__init__()

    def get(self):
        data = {"error" : "no", "message" : None, "take" : request.args.get("take",10), "skip" : request.args.get("skip",0) }

        RepliedOAuthItemAlias = OAuthItem.alias()
        SendOAuthItemAlias = OAuthItem.alias()
        oAuthItemsReplies = (OAuthItemReply                     
                        .select(OAuthItemReply,RepliedOAuthItemAlias,SendOAuthItemAlias,OAuthAccount,User)
                        .join(RepliedOAuthItemAlias,on=(OAuthItemReply.replied_item==RepliedOAuthItemAlias.id) )
                        .switch(OAuthItemReply)
                        .join(SendOAuthItemAlias,on=(OAuthItemReply.send_item==SendOAuthItemAlias.id) )
                        .switch(OAuthItemReply)
                        .join(OAuthAccount)
                        .switch(OAuthItemReply)
                        .join(User)
                        .where(OAuthItemReply.organization==g.auth.getOrganizationId())
                        .limit(data["take"])
                        .offset(data["skip"])
            )

        a = list()
        for item in oAuthItemsReplies:
            a.append(model_to_dict(item, exclude = [OAuthItemReply.reply_thread, OAuthItemReply.organization, User.password],recurse = True))
        data["oauth_reply_items"] = a


        return self.response(data)
```


Can you send minimal example?
On Sep 5, 2015 10:58 AM, "serkandaglioglu" notifications@github.com wrote:

> I use mysql, flask and PooledMySQLDatabase.
> 
> Database Connection
> 
> PooledMySQLDatabase(database,**{
>             "passwd": password, "user": user,
>             "max_connections":None,"stale_timeout":None,
>             "threadlocals" : True
>         })
> 
> Deleting
> 
> class DeleteController(OAuthApiController, BaseMethodView):
> 
> ```
> def __init__(self):
>     super(DeleteController, self).__init__()
> 
> def get(self,id):
>     data = {"error" : "no", "message" : g._("Deleting tweet from twitter takes a long time")}
> 
>     from flask import copy_current_request_context
>     @async
>     @copy_current_request_context
>     def deleteFromTwitter(oAuthItemReply):
>         app.preprocess_request()
>         time.sleep(random.randint(60,80))
>         try:
>             try:
>                 tweepyOauth = tweepy.OAuthHandler(app.config["TWITTER"]["consumer_key"], app.config["TWITTER"]["consumer_secret"])
>                 tweepyOauth.set_access_token(oAuthItemReply.send_oauth_account.oauth_token, oAuthItemReply.send_oauth_account.oauth_token_secret)
>                 twitterApi = tweepy.API(tweepyOauth,parser=tweepy.parsers.JSONParser())
>                 a = twitterApi.destroy_status(oAuthItemReply.send_item.oauth_item_id)
>                 print "deleted >> " + str(oAuthItemReply.send_item.oauth_item_id)
>             except tweepy.error.TweepError, e:
>                 oauthError = e.message
>                 print oauthError
> 
>             with g.db.connection.atomic() as txn:
>                 oAuthItemReply.delete_instance()
>         except Exception, e:
>             print e
> 
>         try:
>             g.db.disconnect()
>         except Exception, e:
>             print e
> 
>         return "ok"
> 
>     try:
>         with g.db.connection.atomic() as txn:
>             RepliedOAuthItemAlias = OAuthItem.alias()
>             SendOAuthItemAlias = OAuthItem.alias()
>             oAuthItemReply = (OAuthItemReply
>                             .select(OAuthItemReply,RepliedOAuthItemAlias,SendOAuthItemAlias,OAuthAccount,User)
>                             .join(RepliedOAuthItemAlias,on=(OAuthItemReply.replied_item==RepliedOAuthItemAlias.id) )
>                             .switch(OAuthItemReply)
>                             .join(SendOAuthItemAlias,on=(OAuthItemReply.send_item==SendOAuthItemAlias.id) )
>                             .switch(OAuthItemReply)
>                             .join(OAuthAccount)
>                             .switch(OAuthItemReply)
>                             .join(User)                             .where(OAuthItemReply.organization==g.auth.getOrganizationId())
>                             .where(OAuthItemReply.id==id)
>                             .get()
>                 )
>             oAuthItemReply.send_status_id = OAuthItemSendStatusEnum.DELETING.value
>             oAuthItemReply.save(only=oAuthItemReply.dirty_fields)
> 
>             # call thread
>             deleteFromTwitter(oAuthItemReply)
> 
>             data["oauth_item_reply"] = model_to_dict(oAuthItemReply, exclude = [OAuthItemReply.reply_thread, OAuthItemReply.organization, User.password],recurse = True)
>     except Exception, e:
>         data["error"] = "yes"
>         data["message"] = g._("An error occured")
>         print e
>     return self.response(data)
> ```
> 
> Selecting
> 
> class ItemsTwitterController(OAuthApiController, BaseMethodView):
> 
> ```
> def __init__(self):
>     super(ItemsTwitterController, self).__init__()
> 
> def get(self):
>     data = {"error" : "no", "message" : None, "take" : request.args.get("take",10), "skip" : request.args.get("skip",0) }
> 
>     RepliedOAuthItemAlias = OAuthItem.alias()
>     SendOAuthItemAlias = OAuthItem.alias()
>     oAuthItemsReplies = (OAuthItemReply
>                     .select(OAuthItemReply,RepliedOAuthItemAlias,SendOAuthItemAlias,OAuthAccount,User)
>                     .join(RepliedOAuthItemAlias,on=(OAuthItemReply.replied_item==RepliedOAuthItemAlias.id) )
>                     .switch(OAuthItemReply)
>                     .join(SendOAuthItemAlias,on=(OAuthItemReply.send_item==SendOAuthItemAlias.id) )
>                     .switch(OAuthItemReply)
>                     .join(OAuthAccount)
>                     .switch(OAuthItemReply)
>                     .join(User)
>                     .where(OAuthItemReply.organization==g.auth.getOrganizationId())
>                     .limit(data["take"])
>                     .offset(data["skip"])
>         )
> 
>     a = list()
>     for item in oAuthItemsReplies:
>         a.append(model_to_dict(item, exclude = [OAuthItemReply.reply_thread, OAuthItemReply.organization, User.password],recurse = True))
>     data["oauth_reply_items"] = a
> 
> 
>     return self.response(data)
> ```
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/704#issuecomment-137970652.


@coleifer Ok I am coding a minimal example and after finish I will send example url


I did a lot of tests and finally found the my mistake.  

In my database class there is this code

```
db.set_autocommit(False)
```

I changed and the problem is solved.

```
db.set_autocommit(True)
```

But when I set db.set_autocommit(False), this error exists only when I use thread. You can see the usage above.

```
def async(f):
    def wrapper(*args, **kwargs):
        thr = Thread(target=f, args=args, kwargs=kwargs)
        thr.start()
    return wrapper

from flask import copy_current_request_context
@async
@copy_current_request_context
def deleteFromTwitter(oAuthItemReply):
       ...
```


This appears not to be an issue with peewee, but with your usage. Consult the code and documentation.

In particular I'm not sure what exactly the nature of the issue is with your code. If you want to take the time to explain it very clearly and simply I might be able to offer guidance.


Thank you for your offer I fixed for now, it seems there is no problem.