[Zope] MySQL Transaction/Locking/etc

Oliver Bleutgen myzope@gmx.net
Wed, 11 Dec 2002 13:02:00 +0100


Terry Hancock wrote:
> Hi All,
> Twice now I've come into what must be a fairly common problem when using  SQL 
> databases in Zope: I simply want MySQL to give me a unique identifier and I 
> need to retrieve what its value is to continue using the correct record.
> 
> The MySQL side of this is very straightforward -- I just make an 
> AUTO_INCREMENT field and use LAST_INSERT_ID() to get what its value was.
> 
> I'm less sure how safe this is in Zope -- the whole point being, of course, 
> that when running multi-threaded, another thread *could* stick another record 
> into the table, so that LAST_INSERT_ID() comes up with the wrong value.  As a 
> perhaps naive attempt to handle this in a previous application (for which the 
> consequences of an error were not too severe), I simply used the threading 
> module and acquired a lock -- I'm hoping that this will keep at least any 
> threads within my current Zope process from interfering.  (Please forgive my 
> ignorance of threads if this betrays a deep misunderstanding!  Corrections 
> will be much appreciated. ;-)).
> 
> I can however, picture installations with more than one Zope *process* 
> running, but accessing the same database, which would presumeably allow 
> collisions under high loads. :-(
> 
> I tried using MySQL's table-locking mechanism to do this, but the lock would 
> cause *all* my queries to fail -- including the ones that I was supposed to 
> be protecting.  Since each ZSQL method can only do one query, I couldn't 
> figure out how to run a batch of them.


Zope itself is transaction safe, and provided the database adapter and 
the database do the right thing and have the right capabilities, 
everything should work fine, without you doing anything special.

The problem here might be mysql or, more specifically, what version of 
mysql you use. There's the zope-db list where I would ask that question.

Or you could decide to go with a "real" open source database, like 
postgres, and never again worry about these questions ;).
To be fair, I think a recent mysql version should handle that thing ok, 
provide you don't have too many concurrent transactions trying to write 
to the same table.


cheers,
oliver