[Zope] ZSQL and database locking

[Arjan de Knegt]

Dear all,

I'm a happy Zope user (in a private environment), experienced in
client-server application development but still quite green regarding to
Zope. I'm trying to figure out how I can use database locking using ZSQL
and MySQL. What is the case? I would like to implement a multi-user update
function avoiding the obvious concurrency pitfalls. I usually implement
this as follows:
1. Add a "counter" column to every table
2. With every record update, raise the counter for that record with 1 
3. Before an update, first read the actual value
   of the counter in the database. Compare the counter in
   the database with the counter value of the record currently 
   being edited. 
4. If the values are not the same, another user
   has obviously edited the database in the meantime. The changes
   will not be updated to the database but the current database
   record will be redisplayed on the screen (with a message to the
   user of course).
5. If values are the same, update the database.

To avoid concurrency problems, the table has to be locked before step 3
and unlocked after step 5. Because a ZSQL database connection is a
permanent connection, the database will see it as 1 single session. All
(anonymous) user of the web-update form will use the same session and
database locking will have no effect.

Now finally my question: Does anyone see a (simple) solution for this? 
Okay, I could create a seperate MSQL database connection for every user,
but that doesn't look very Zope-ish to me. 

Didn't find a comparable issue in the Zope Mailing lists.

Thnx in advance,
Arjan.