Handling locked db tables...

M.-A. Lemburg mal at egenix.com
Sat Feb 23 12:28:39 EST 2008


On 2008-02-20 17:19, 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,
> 
> 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?

Ok, so you want to use the table to manage locks on a resource.

This is tricky, since the SELECT and UPDATE operations do not
happen atomically. Also a READ lock won't help, what you need
is a WRITE lock. Note that the UPDATE causes an implicit
WRITE lock on the row you updated which persists until the end
of the transaction.

The way I usually approach this, is to mark the row for usage
using an indicator that's unique to the process/thread requesting the
resource. In a second query, I fetch the marked resource via the
indicator.

When freeing the resource, I update the row, again using the
indicator and also clear the indicator from the row.

All this is done on an auto-commit connection, so that no locking
takes place. Works great.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Feb 23 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 mailing list