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 django-users@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---