flawed architecture creating race condition for database writes

[Carl]

the flaw is in my architecture not web2py. can you help with a better
approach? Needs to be BigTable-friendly to boot.

I have two user types: Agents and Candidates
Agents invite Candidates to Parties.

I keep a Party table recording all such Party invites.
Candidates can be invited to the same Party by several Agents so I
have an Invite table recording which Agents invited which Candidates
to which Parties.

1. On a new Party invitation I check if the Candidate has already been
invited by looking in the Party table.
2. If a party isn't found then I insert into the Party table
3. I insert the Agent into Invite table has a value pointing to the
appropriate Party row.

Here's the "race condition"...

**Between** steps 1 and 2 above another party invite is sent and
checked for pre-existance. it's not found because step 2 by the 1st
agent's run through hasn't yet executed.

Thus duplicate party invitations are inserted into the database.

What's the better approach to employ?

[Iceberg]

Here is my attempt. Not a perfect one.

db.define_table('Agent', Field('name', unique=True))
db.define_table('Candidate', Field('name', unique=True))
db.define_table('Party', Field('name', unique=True))
db.define_table('Invitation',
Field('agent', db.Agent),
Field('candidate', db.Candidate),
Field('party', db.Party),
# Ideally, we should set up two fields, candidate and party,
# as combinated primary key in db level. But I don't know how.
)

# Then, in controller
def invite():
def no_duplicate(form):
if db( (db.Invitation.candidate==form.vars.candidate)
& (db.Invitation.party==form.vars.party) ).count():
form.errors.candidate = 'Already invited to this party'
form = SQLFORM(db.Invitation)
if form.accepts( ..., onvalidation=no_duplicate):
response.flash = 'ok'
# However, theoretically speaking there is still a slim time gap,
# maybe 0.2 second, between the onvalidation and the later
insertion.
return {'':form}

[Carl]

thanks Iceberg.

your approach is pretty much what I have right now. I'm looking for a
bullet-proof approach.
otherwise I'm running code with "fingers crossed two users don't use
the system at the same time"

and I want to encourage usage :)

C

[Iceberg]

The bullet-proof solution should be setting twin-field primary key in
db level, I believe. Hope somebody can show us how to do that in
web2py db.define_table(...), if not using db.executesql('set up the
primary key blah blah').

On the other hand, in a non-db level, inside web2py, perhaps Massimo
will introduce a global ThreadLock so that developers can do:

def invite():
with web2py_lock:
form = ...
if form.accepts(...):
...

or maybe a syntactic sugar:

@with_lock
def invite():
all_the_original_code

Wait and see what other may say.

[Carl]

tricky.
I'm deploying on GAE which may run my code across multiple servers so
locking isn't going to be an option.

I need to employ a completely different approach. I just need to find
it :)

[Richard]

I am not sure how to do it through web2py, but if working directly
with the GAE API you can set the entities "key_name" to avoid
duplicates.

[Carl]

thanks Richard.
I'm going to stick with a web2py solution and remodel my approach.

Instead of creating the troublesome row at invite time I'm going to
create it when a Candidate checks their invites. In the service I'm
building each invite is unique to a Candidate so I won't have
Candidates checking the same invite simultaneously.