Hi Dietrich,
Dietrich Bollmann wrote:
As this should be a very common situation (different users accessing and
manipulating the same data) I thought there should be a simple
solution :)
What you're describing is, in fact, a very common situation. However,
concurrency issues in general are among the most difficult to deal with.
There isn't a really "simple" solution in ZF or elsewhere, to my knowledge.
In this case, the easiest thing to do would probably be to cause the DB
to set an exclusive lock on the row with the counter at the time it
reads it. Then, your application can decide if it needs to decrement the
value, delete the row, or whatever, and do the appropriate write.
I have a situation similar to yours in my application: users can put in
applications in a number of different categories, but each category has
a specific limit it can take. So the limit counter is decremented each
time an application is accepted, or a demand tracking record is written
if the category is full.
In MySQL/InnoDB, I do essentially this:
beginTransaction();
SELECT (counter > 0) as isOpen FROM categories WHERE id = ? FOR UPDATE
if ($isOpen) {
INSERT INTO applications (...) VALUES (...)
UPDATE categories SET counter = counter - 1 WHERE id = ?
} else {
INSERT INTO demand_tracking (...) VALUES (...)
}
commit();
In MySQL/InnoDB, the "FOR UPDATE" on the select sets an exclusive lock
on all the rows matching the query. Thus, any other transaction that
wants to write to that row must wait for the lock to be released (when
the current transaction ends), so the counter value cannot be changed by
another transaction between the first select and the commit. At the same
time, the exclusive lock prevents other transactions from reading the
counter. This is lowers concurrency, but increases safety since multiple
transactions don't see the same value of the counter and try to insert
more applications.
There's certainly a way to achieve the same effect in PostgreSQL, but I
don't remember what it is off the top of my head (maybe "SERIALIZABLE"
transaction isolation level is sufficient?). You should be able to find
the answer in the Postgres docs.
Hope this helps,
Bryce Lohr
