[Zope] MySQL Transaction/Locking/etc

[Terry Hancock]

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.

MySQL can do transactional tables nowadays, but I'm not sure if they would 
actually make sense for this application -- this "get a unique new record id" 
function is the only thing I really need to do this way. And I'm not even 
sure that transactions would be proof against this (would transactions stop 
the LAST_INSERT_ID() being incremented in the middle of the transaction? It 
doesn't seem like it would actually violate the transaction model to do that).

There doesn't seem to be much on the Zope website or in the ZDG about how to 
deal with this situation (or perhaps I don't know what to search for).  Has 
anyone else solved it?  Is the thread-locking solution adequate, or is there 
a better way?

Any suggestions?

Thanks,
Terry

--
Terry Hancock ( hancock at anansispaceworks.com )
Anansi Spaceworks  http://www.anansispaceworks.com

"Some things are too important to be taken seriously"