[Zope] ZSQL and database locking
Arjan de Knegt
Tue, 12 Sep 2000 16:49:53 +0200 (MET DST)
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
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,