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 -- http://mail.python.org/mailman/listinfo/python-list