Very nice, thanks for the detailed response!
-Peter
On Nov 6, 12:53 pm, Karen Tracey <[EMAIL PROTECTED]> wrote:
> At 10:40 AM 11/6/2007, you wrote:
>
> >Hi All,
>
> >I tried writing some code to do table locking on a MySQL MyISAM
> >database. I used the code below, which seemed to work in basic
> >examples, but the code would complain of accessing a non-locked table
> >whenever I made a more complex query such as
> >objects.filter(<foreignkeyname>__<value-of-that-foreign-key>) saying
> >that the table <foreignkeyname>__<value-of-that-foreign-key> wasn't
> >locked with "LOCK TABLES", even though I had locked the table for the
> >foreign key. Furthermore, it failed to lock if I tried to reference
> >the table using the syntax of the form of the complex query.
>
> >Has anyone had success running table locks?
>
> Yes, I can get this to work, but it requires knowing exactly how
> Django is going to construct the queries -- what tables will be
> listed and what aliases used. That means code you get to work now
> may easily break in the future if Django changes how it builds
> queries. I would not be at all surprised if code that works today
> for this breaks when queryset-refactor lands on the trunk. But, if
> you really need to make this work, it can be done.
>
> Note that when an alias is used, you must use "real_table AS alias
> lock_type" in the LOCK TABLES statement.
>
> Here's an annotated transcript of how I got lock tables with a
> non-trivial query to work with my DB. Where I displayed the sql for
> the query I manually split the lines to highlight the tables involved
> in the query. (Note my database existed before I ever started using
> Django so the tables and column names are not what you might expect
> for a Django DB.)
>
> ----------
>
> >>> # Contstruct a query to get a list of Clues in the DB for the
> Entry "DJANGOREINHARDT"
> >>> cl =
> Clues.objects.select_related().filter(EntryID__Entry='DJANGOREINHARDT')
>
> >>> # Naively assume the only table we need to lock is Clues itself
> >>> cursor.execute("LOCK TABLES Clues READ")
> 0L
>
> >>> # See how wrong we are
> >>> cl
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File "/homedir/django/newforms-admin/django/db/models/query.py",
> line 108, in __repr__
> return repr(self._get_data())
> File "/homedir/django/newforms-admin/django/db/models/query.py",
> line 482, in _get_data
> self._result_cache = list(self.iterator())
> File "/homedir/django/newforms-admin/django/db/models/query.py",
> line 189, in iterator
> cursor.execute("SELECT " + (self._distinct and "DISTINCT " or
> "") + ",".join(select) + sql, params)
> File "/homedir/django/newforms-admin/django/db/backends/util.py",
> line 18, in execute
> return self.cursor.execute(sql, params)
> File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line
> 166, in execute
> self.errorhandler(self, exc, value)
> File "/var/lib/python-support/python2.5/MySQLdb/connections.py",
> line 35, in defaulterrorhandler
> raise errorclass, errorvalue
> OperationalError: (1100, "Table 'Clues__EntryID' was not locked with
> LOCK TABLES")
>
> >>> Examine the sql for the last query to see what all really needs
> to be locked
> >>> connection.queries[-1]['sql']
> u'SELECT `Clues`.`ID`,`Clues`.`Theme`,`Clues`.`Entry
> ID`,`Clues`.`Puzzle
> ID`,`Clues`.`Clue`,`Clues`.`Num`,`Clues`.`Dir`,`Clues`.`Derived`,`Entries`.`Entry
> ID`,`Entries`.`Entry`,`Entries`.`Exclude`,`Puzzles`.`Puzzle
> ID`,`Puzzles`.`Publisher ID`,`Puzzles`.`Date`,`Puzzles`.`Day of
> Week`,`Puzzles`.`Author
> ID`,`Puzzles`.`Title`,`Puzzles`.`Columns`,`Puzzles`.`Rows`,`Puzzles`.`Entry
> Count`,`Puzzles`.`Block Count`,`Puzzles`.`Avg Entry
> Length`,`Puzzles`.`Avg Scrabble Value`,`Puzzles`.`Unused
> Letters`,`Puzzles`.`DateAux`,`Publishers`.`Publisher
> ID`,`Publishers`.`Publisher`,`Publishers`.`Short
> Name`,`Publishers`.`Editor`,`Authors`.`Author
> ID`,`Authors`.`Author`,`Authors`.`Pseudonym`,`Authors`.`Notes`
>
> FROM `Clues`
>
> INNER JOIN `Entries` AS `Clues__EntryID` ON `Clues`.`Entry ID` =
> `Clues__EntryID`.`Entry ID` ,
>
> `Entries`,
>
> `Puzzles`,
>
> `Publishers`,
>
> `Authors`
>
> WHERE ((NOT ((`Clues`.`Derived` = True))) AND
> `Clues__EntryID`.`Entry` = DJANGOREINHARDT) AND `Clues`.`Entry ID` =
> `Entries`.`Entry ID` AND `Clues`.`Puzzle ID` = `Puzzles`.`Puzzle ID`
> AND `Puzzles`.`Publisher ID` = `Publishers`.`Publisher ID` AND
> `Puzzles`.`Author ID` = `Authors`.`Author ID` ORDER BY `Clues`.`Dir`
> ASC, `Clues`.`Num` ASC'
>
> >>> # Try again, this time locking all the tables & aliases used in the query
> >>> cursor.execute("LOCK TABLES Clues READ, Entries as
> Clues__EntryID READ, Entries READ, Puzzles READ, Publishers READ,
> Authors READ")
> 0L
>
> >>> # And now the query works
> >>> cl =
> Clues.objects.select_related().filter(EntryID__Entry='DJANGOREINHARDT')
> >>> cl
> [<Clues: DJANGOREINHARDT: Belgian jazz great>]
> >>>
>
> ----------
>
> Karen
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
