Handling locked db tables...

[breal]

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?

Thanks.

 

[M.-A. Lemburg]

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)________________________________________________________________________

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

 

[breal]

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/

________________________________________________________________________

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

 

[Larry Bates]

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

 

[John Nagle]

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?

Ah. You're just using MySQL as a lock manager. Check out
GET_LOCK, RELEASE_LOCK, and IS_FREE_LOCK. That may be simpler
for this application.

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html

I use those functions regularly, for coordinating multiple
processes and servers. They're quite useful when you have
multiple servers, and OS-level locking isn't enough.

But what you're doing should work. It could be improved;
use "SELECT * FROM port_usage WHERE in_use = 0 LIMIT 1;",
since you only need one value returned. Also, unless port usage
persists over reboots or you have millions of ports, use the
MEMORY engine for the table; then it's just in RAM. Each
restart of MySQL will clear the table.

You shouldn't get an error if the table is locked; the
MySQL connection just waits. What error are you getting?
You wrote "But, when another cursor then tries to do the read
I get an error unless the first process has been completed...
unlocking the tables." Bear in mind that you can only have one
cursor per database connection. The MySQLdb API makes it look
like you can have multiple cursors, but that doesn't actually
work.

John Nagle

 

[M.-A. Lemburg]

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)________________________________________________________________________

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