Re: [SQLObject] Table locking with MySQL

[Oleg B. <ph...@ph...>]

On Tue, Nov 30, 2010 at 10:18:58AM +0800, Jason Heeris wrote:
> 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?

   You can use low-level API like connection.query('LOCK...'). But I think
a better way would be to insert an AssemblyResult row in advance, use its
generated id (databases guarantee uniqueness and monotony of generated
id's) to create a document and then update the row with the name of the
document:

    row = AssemblyResult(doc, ...)
    number = row.id
    new_name = construct_name(number, ...)
    doc = create_document(new_name, ...)
    row.doc = doc

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.