Handling locked db tables...
mal at egenix.com
Wed Feb 20 17:05:18 CET 2008
On 2008-02-20 16:24, breal wrote:
> I have a db table that holds a list of ports. There is a column
> in_use that is used as a flag for whether the port is currently in
> use. When choosing a port the table is read and the first available
> port with in_use = 0 is used, updated to in_use = 1, used, then
> updated to in_use = 0. I am using MySQLdb and want to make sure I am
> locking the table when doing reads, writes, updates since there will
> be several instances of my program looking for available ports
> When I run a "lock table mytable read" I can do all of my
> transactions. But, when another cursor then tries to do the read I
> get an error unless the first process has been completed... unlocking
> the tables. How is this handled generally?
This is normal database locking behavior. If you do an update to
a table from one process, the updated row is locked until the
transaction is committed.
If another process wants to access that row (even if only indirectly,
e.g. a select that does a query which includes the data from the locked
row), that process reports a database lock or times out until the
lock is removed by the first process.
The reason is simple: you don't want the second process to report
wrong data, since there's still a chance the first process might
roll back the transaction.
Most modern database allow row-level locking. I'm not sure whether
MySQL supports this. SQLite, for example, only support table locking.
Professional Python Services directly from the Source (#1, Feb 20 2008)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
More information about the Python-list