Can wrote:
> I'm using MySQL and when I need to lock a table, I do:
>
> conn = engine.connect()
> conn._autocommit = dummyFunc
> conn.execute("LOCK TABLES tablename WRITE")
> try:
> ...
> conn.execute("COMMIT")
> except:
> conn.execute("ROLLBACK")
> ...
> finally:
> conn.execute("UNLOCK TABLES")
> conn.close()
>
> Are there any more pythonic ways to do such tasks?
I use a Python 2.5 context manager to access MySQL named locks; the
approach could easily be adapted to table locks:
## GET_LOCK and the 'with' statement:
con = engine.connect()
with named_lock(con, 'some_thing'):
con.do_stuff()
# with a little work, could do similar for LOCK TABLES:
# with table_locks(con, table1, table2):
# con.do_stuff()
The GET_LOCK context manager looks like this:
import contextlib, exceptions, logging
_log = logging.getLogger('db')
class LockTimeoutException(exceptions.Exception): pass
@contextlib.contextmanager
def named_lock(connection, name, timeout=2):
connection.detach()
have = None
try:
have = connection.scalar("SELECT GET_LOCK(%s, %s)",
name, timeout)
if not have:
raise LockTimeoutException(
"Could not acquire lock '%s'" % name)
yield have
finally:
if have:
try:
connection.execute("SELECT RELEASE_LOCK(%s)", name)
except:
_log.error("Could not release lock '%s'!" % name)
The connection.detach() in there is a little paranoid but probably
a good idea for any of the connection-scoped MySQL trickery. It
ensures that the connection won't re-enter the connection pool and
be reused after you've released it.
I'd also be super careful with locks and transaction management,
the feature is funky and the MySQL reference LOCK TABLES page is
your friend.
-j
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
