[SQLObject] Table locking with MySQL

[Jason H. <jas...@gm...>]

I am using SQLObject (0.12.4, but could be later) with MySQL 5.1 as
the storage for a CherryPy based web app. I have a table "assembly"
which refers to another table "assembly_results"; the SQLObject class
def is something like:

    class Assembly(SQLObject):
        dbAssemblyResults = MultipleJoin('AssemblyResult',
joinColumn='db_assembly_id')
        ...

    class AssemblyResult(SQLObject):
        dbAssembly = ForeignKey('Assembly')
        ...

Both tables are InnoDB.

To upload new results, I need to generate a filename that includes an
index of the file, eg. the 5th result uploaded against an assembly is
named "05. Some file...".  So I have the following:

    # asmbly is an instance of Assembly
    number = len(asmbly.dbAssemblyResults) + 1
    new_name = construct_name(number, ...)
    doc = create_document(new_name, ...)
    # create_document returns an instance of another SQLObject subclass
    AssemblyResult(doc, ...)

The trouble is that there's a race hazard here — two uploads at almost
the same time will see the same number of uploaded documents and I'll
end up with two file names with the same index.

The simplest way I could think of to make this safe is to lock the
assembly_result table for the duration of these calls (not
unreasonable in my situation), but I can't find support for table
locking in the SQLObject docs. Is it there, or should I use raw SQL
calls? Or is there a better way?

— Jason