Re: Handling locked db tables...

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 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?

.

Relevant Pages

  • [PATCH 2.6.14-rc2 2/2] libata: Marvell function headers
    ... * @base: port base address ... * LOCKING: ... * Inherited from caller. ... * Disable ints, cleanup host memory, call general purpose ...
    (Linux-Kernel)
  • 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: 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 ... This is normal database locking behavior. ... using an indicator that's unique to the process/thread requesting the ...
    (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. ... roll back the transaction. ...
    (comp.lang.python)
  • 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)