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.
|
