[Zope] MySQL Transaction/Locking/etc

[Michael Long]

I believe that your premise that ZSQL can only do one query is false. I am able to handle this exact situation (using postgres) with the following in a single ZSQL method:

insert into mytable(id,col1,col2) values(nextval('seq_id'),val1, val2);
select curval('seq_id');

This will cause the ZSQL method to return the newly inserted id to you and this all happens within a single transaction.

-mike

> 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"
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
> 
>