Re: Handling locked db tables...

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
.

Relevant Pages

  • Re: Help - Timing Logic
    ... put a trigger on the database table. ... Transaction and Database Locking - look at isolation levels / settings ... Maybe implement a message broker ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... build a trigger on the database .. ... Transaction and Database Locking - look at isolation levels / settings ... If you need to implement a locking mechanism / or / logging mechanism / or / a checking mechanism to avoid duplicate messages caused by multi-threading ... ... message broker gets all the necessary messages or message id's from the database to be sent ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... If you need to implement a locking mechanism / or / logging mechanism / or / a checking mechanism to avoid duplicate messages caused by multi-threading ... ... Is it the READ from the database ... ... Perhaps a stored proc may be faster to execute and return the values as opposed to building the transaction in the code. ... implement as above locking only the records you retrieve / update - need to watch out here for table locking ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... put a trigger on the database table. ... Transaction and Database Locking - look at isolation levels / settings ... Maybe implement a message broker ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... If you DBA screams and yells and jumps up and down - this is a database server, we are not install you little program on it ... ... Retrieve list of messages from the database... ... will create a lot of wasted CHECKING and UPDATING and DATABASE LOCKING to ensure messages are not duplicated between recipients. ... Transaction and Database Locking - look at isolation levels / settings ...
    (microsoft.public.dotnet.languages.vb)