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"
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
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?
I am working on the chapter in the Zope Book at the moment on this.
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.
You're safe on this - Zope bundles the whole query into a transaction for you automatically. I'm a Postgres user so I don't know MySQL so well but from what I've read it should handle it fine nowadays. A -- Logical Progression Ltd, 3 Randolph Crescent, Edinburgh, EH3 7TH, UK Tel: +44 (0)131 466 9585 Web: http://www.logicalprogression.net/
participants (3)
-
Andrew Veitch -
Oliver Bleutgen -
Terry Hancock