MyISAM table locking.

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-for
eign-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?

The code:  (note: I was getting connection using: from
django.db
import connection)


 def lock_tables(connection, args):
 
'''
        Performs a table lock with the given connection,
args is a
list of tuples
        with (<table_name>,
<lock_type["READ" |
"WRITE"]>)
        **Remember to call unlock_tables at some point after
this
call
    '''
    if not len(args):
        raise Exception("Must supply args to lock
table!")
    cursor = connection.cursor()
    try:
        tbl_clause = [MySQLdb.escape_string(x[0] + "
" + x[1]) for x
in args]
    except:
        raise Exception("Args was improperly
constructed")
    cursor.execute("LOCK TABLES %s" %
MySQLdb.escape_string(',
'.join(tbl_clause)))

def unlock_tables(connection):
 
'''
        Unlocks all table locks for the given
connection
    '''
    cursor = connection.cursor()
    cursor.execute("UNLOCK TABLES")


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

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='DJANGO
REINHARDT')

 >>> # 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&q
uot;, 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`.`Deri
ved`,`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`.`Note
s`

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='DJANGO
REINHARDT')
 >>> 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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Very nice, thanks for the detailed response!

-Peter

On Nov 6, 12:53 pm, Karen Tracey <kmtra...gmail.com> 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='DJANGO
REINHARDT')
>
>  >>> # 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&q
uot;, 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`.`Deri
ved`,`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`.`Note
s`
>
> 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='DJANGO
REINHARDT')
>  >>> 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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---