GAE datastore, how to atomically query and insert without ancestor or known entity key?

Question

For example I have this user model:

class User(ndb.Model):
    email = ndb.StringProperty()

I want to atomically add a new User entity only if there is no other entities having a certain email property value.

In SQL I could do something like this to check and insert in a transaction:

begin; 
if count(select * from User where email='[email protected]') > 0 {
    insert into User values('[email protected]');
}
end;

But in GAE this is not allowed, since non-ancestor queries are not allowed inside transaction. I don't want to use the email address as the entity key, since probably the stable id of users may not be based on email addresses.

Answer 1

This problem is solved already in webapp2 micro framework. Take a look on this docs and on this blog post as well. Copy-pasted code with basic sample:

from google.appengine.ext.ndb import model

class Unique(model.Model):
    @classmethod
    def create_multi(cls, values):
        keys = [model.Key(cls, value) for value in values]
        entities = [cls(key=key) for key in keys]
        func = lambda e: e.put() if not e.key.get() else None
        created = [model.transaction(lambda: func(e)) for e in entities]

        if created != keys:
            # A poor man's "rollback": delete all recently created records.
            model.delete_multi(k for k in created if k)
            return False, [k.id() for k in keys if k not in created]

        return True, []

    @classmethod
    def delete_multi(cls, values):
        return model.delete_multi(model.Key(cls, v) for v in values)

# Assemble the unique values for a given class and attribute scope.
uniques = [
    'User.username.%s' % username,
    'User.auth_id.%s' % auth_id,
    'User.email.%s' % email,
]

# Create the unique username, auth_id and email.
success, existing = Unique.create_multi(uniques)

if success:
    # The unique values were created, so we can save the user.
    user = User(username=username, auth_id=auth_id, email=email)
    user.put()
    return user
else:
    # At least one of the values is not unique.
    # Make a list of the property names that failed.
    props = [name.split('.', 2)[1] for name in uniques]
    raise ValueError('Properties %r are not unique.' % props)

Answer 2

You can run into this problem in the following use case:

• thread A checks for email address and receives a response "none"
• an entity with this email address is committed to the datastore by thread B
• thread A also commits an entity with the same email address

One approach to this problem is to use email address as an entity name (i.e. key). Note that it does not have to be a user entity: it can be its own email entity - a child entity of a user entity. It may even have no other properties at all, unless you need them. This way you can use a transaction because email entity and user entity belong to the same entity group.

Answer 3

Since you don't have an ancestor you might run into some inconsistent state for some hundred milliseconds or so after the last write, but in real life situations when it comes to emails it is quite rare, but of course not impossible to happen.

Having that in mind you can check if the email already exist by something like this:

if User.query(User.email == '[email protected]').count() == 0:
  # do something with the email, it is safe to assume that is unique :)
else:
  # that email already taken