Tim Starling wrote:
Why not try
wrapping it in a BEGIN / COMMIT block? Transactions are
designed for exactly this kind of thing.
Oh, I thought we were still using MyISAM tables. Obviously not. Plus I was
misled by the statement in the MySQL manual that you can only do full-text
search with MyISAM tables. Do any of the languages still do it that way?
The search index fields have been in a separate table for a long time
specifically so we can use innodb tables for the rest. Most of the
smaller languages haven't actually been converted, but English and
German are at least mostly on innodb.
Just thinking about it... I don't think
transactions will work anyway. The
problem here is delaying SELECT statements until a transaction is complete,
and transactions don't do that. Between the BEGIN statement, and the time
when the COMMIT finally acquires the locks it needs, AFAIK the problem still
occurs.
I guess if we use transactions, the edit will be saved in the old table
properly, it just won't trigger an edit conflict. It'll look like user 2
reverted user 1's work. I think I'll stick with my way for now, assuming it
works.
Hmm... Here's how the bug works now:
THREAD A THREAD B
SELECT data from cur SELECT data from cur
check for edit conflict check for edit conflict
UPDATE cur with new data
UPDATE cur with new data
[overwriting what A just did]
INSERT prev data to old
[the previous one, not B's]
INSERT prev data to old
[second copy of the previous,
not A's]
with the result that neither thread detects an edit conflict, old
contains two copies of the previous version, and A's edit is gone.
Here's how it would work with transactions if I understand it correctly:
THREAD A THREAD B
BEGIN BEGIN
SELECT data from cur SELECT data from cur
check for edit conflict check for edit conflict
UPDATE cur with new data UPDATE cur with new data
[not saved yet] [not saved yet]
INSERT prev data to old INSERT prev data to old
[still not saved yet] [still not saved yet]
COMMIT
[successfully saves edit]
COMMIT
[transaction is rejected,
mysql reports an auto rollback]
we know something's wrong
start over:
SELECT data from cur
check for edit conflict
display edit conflict screen
with the result that A's edit is saved intact and B can be shown an edit
conflict screen.
-- brion vibber (brion @
pobox.com)