[web2py] Re: flawed architecture creating race condition for database writes

[Iceberg]

On May27, 11:32pm, Carl <carl.ro...@gmail.com> wrote:
> 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?

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}