Handling locked db tables...

[M.-A. Lemburg]

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
> simultaneously.
> 
> 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.

-- 
Marc-Andre Lemburg
eGenix.com

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