breal wrote: > On Feb 20, 8:05 am, "M.-A. Lemburg" <[EMAIL PROTECTED]> 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? >
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 -- http://mail.python.org/mailman/listinfo/python-list
