MySQL Deadlocks in ZSQL Methods
17 March 2006 | Zope | No Comments
I ran across an issue with a deadlock in a ZSQL method, which seems obvious looking back on it, but which took me a while to figure out.
The problem is that I have a table containing a queue of rss feeds and I have several threads that will use that queue to grab a feed and parse it. I have a column “available” that is 1 if the feed is up for parsing and 0 if the feed is being parsed or has been parsed.
I was using SQL roughly like this:
set @id=(select min(id) from queue for update)
update queue set available = 0 where id=@id
select @id
And I was consistently getting deadlocks. The above operation takes milli if not microseconds, so I couldn’t understand what the issue was.
The problem was that after executing the above SQL, I was then parsing the feed and since all of that (SQL + parsing) was in a Zope transaction, all the other threads were waiting not only for the SQL to finish, but for the parse to finish as well. Thus, what I thought should take milliseconds was taking seconds.
The solution is to call get_transaction().commit() after the SQL call. That commits the Zope transaction and in so doing, commits the MySQL transaction as well.
Technorati Tags: zope, mysql, zsql
ZODB-less Zope
12 March 2006 | Zope | No Comments