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