But if you always issue a LOCK TABLE before SELECT MAX from... your LOCKs will compete against themselves, so you'll be safe. Exact, this may save my day ! :) One last problem though: How do I pass the id value from the SELECT to the INSERT, and then return that to the caller of the ZSQL method. I tried : SELECT max(id)+1 as new_id ... and then INSERT INTO table VALUES (<dtml-var new_id>,....) but it complains about new_id not declared as an input variable. If I do INSERT INTO table VALUES (SELECT ...,....) , will I have new_id as a returned result ?
I ran out of time, so I've put the LOCK+SELECT in a first ZSQL method, the INSERT in another, and both are called sequentially from a DTML document. I hope the LOCK is still held between the 2 ZSQL methods. I've been told on IRC it is since the transaction is committed only at the end of the http request. anyway, thank you again for your invaluable help!