Re: Handling locked db tables...

On 2008-02-20 17:19, breal wrote:
On Feb 20, 8:05 am, "M.-A. Lemburg" <m...@xxxxxxxxxx> 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
.

Relevant Pages

  • Re: Handling locked db tables...
    ... When choosing a port the table is read and the first available ... When I run a "lock table mytable read" I can do all of my ... get an error unless the first process has been completed... ... This is normal database locking behavior. ...
    (comp.lang.python)
  • Re: Handling locked db tables...
    ... When choosing a port the table is read and the first available ... This is normal database locking behavior. ... lock is removed by the first process. ... send request to available port and do some stuff until finished with ...
    (comp.lang.python)
  • Re: Detecting multiple class loaders
    ... maybe do the locking in a global way. ... it extensively before trusting it--and on each platform. ... That would certainly imply the scope of the lock goes beyond ... pathnames and port numbers. ...
    (comp.lang.java.programmer)
  • Re: [VW] file locking
    ... I guess that on Windows you already have sufficient locking as-is. ... I recall it being pretty simple to implement, the only problem being that it is not robust when applications crash (stale lock files hanging around). ... the idea here is to open a server socket on a particular port. ... If you open a socket, and then use 'exec' to spawn a process, than your new child process will inherit the 'open' socket. ...
    (comp.lang.smalltalk)
  • Re: using clustered index to optimize inserts ...
    ... I will try to explain locking in terms of Sybase docs... ... Allpages Locking: Allpages locking locks both data pages and index ... the data page is locked with an exclusive lock. ... Clustered Index: The datarows will be arranged as per the clustered ...
    (comp.databases.sybase)