Brion wrote:
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.
Actually the old INSERT happens before the cur UPDATE, so the problem exists
for the duration of the old query, plus the time it takes to acquire the
lock in the UPDATE query. This means that there are a number of possible
manifestations, for example
A B
SELECT
INSERT
SELECT
INSERT
UPDATE
UPDATE
or
A B
SELECT
SELECT
INSERT
INSERT
UPDATE
UPDATE
or even
A B
SELECT
SELECT
INSERT
UPDATE
INSERT
UPDATE
They all have the same effect: like you say, two copies of the previous
revision in old, and B's version in cur.
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.
I couldn't find any reference to this sort of behaviour in the MySQL manual,
so I did a couple of tests. It seems transactions work like this:
BEGIN
UPDATE cur... -- gets a lock on the cur table
COMMIT -- writes to cur regardless of previous activity
I opened up a connection to the test DB in an SSH window, and did something
along the lines of the following:
mysql: set autocommit=0;
mysql: begin;
Then I changed [[Test2]] to "5" using my browser. This worked, because MySQL
hadn't acquired a lock. Then I did this...
mysql: update cur set cur_text="6" where cur_title="Test2";
mysql: commit;
MySQL had no problem with either of the last two queries, successfully
updating cur to 6. When I did this:
mysql: begin;
mysql: update cur set cur_text="hello" where cur_title="Test2";
update in browser...
mysql: commit;
The attempt to update in the browser failed, returning a lock timeout
error -- like that time I told you to flush the tables when you were already
doing it. So having PHP threads die on us in the middle of transactions is
currently very, very bad. Perhaps we should use register_shutdown_function()
to rollback any active transactions. Or kill on timeout like in my scheme.
Killing a thread which is serving an unrelated legitimate query is really
much better than attempting to contact someone with root access in the
middle of the US night.
Note that using register_shutdown_function() will effectively disable user
aborts -- see chapter 20 of the PHP manual.
-- Tim Starling.