Handling locked db tables...

[Larry Bates]

breal wrote:
> 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?
> 

I think you want to use SELECT for UPDATE or SELECT LOCK IN SHARE MODE.

Here is a link that might help:

http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html

-Larry