Handling locked db tables...

[breal]

On Feb 20, 8:05 am, "M.-A. Lemburg" <m... at egenix.com> wrote:
> 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

Marc-Andre,

Thanks for the reply.  I understand that this is normal locking
behavior.  What I am looking for is a standard method to either loop
the query until the table is unlocked, or put the query into some sort
of queue.  Basically my queries work like this.

Request comes in

PART I:
LOCK TABLE port_usage READ;
SELECT * FROM port_usage WHERE in_use = 0;
Get available port
UPDATE port_usage SET in_use = 1 WHERE port = available_port;
UNLOCK TABLES;

send request to available port and do some stuff until finished with
port

PART II:
LOCK TABLE port_usage READ
UPDATE port_usage SET in_use = 0 WHERE port = available_port;
UNLOCK TABLES;

Several of these *may* be happening simultaneously so when a second
request comes in, and the first one has the table locked, I want to
have the PART I sql still work.  Any suggestions here?