Bug #52663 Lost update incrementing column value under READ COMMITTED isolation level
Submitted: 7 Apr 2010 18:22 Modified: 20 Jun 2010 1:07
Reporter: Jeff Sturm Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.45, 5.1, 5.6.99 OS:Linux (CentOS 5.x)
Assigned to: Marko Mäkelä
Tags: regression
Triage: Triaged: D2 (Serious) / R1 (None/Negligible) / E1 (None/Negligible)

[7 Apr 2010 18:22] Jeff Sturm
Description:
This peculiar behavior takes place when updating a column based on its current value.  Two separate transactions perform an "UPDATE ... SET count = count + 1", both appear to succeed, however the column value increases only by one.

I could not reproduce this test case under REPEATABLE-READ, or with a simple primary key.

How to repeat:
"mysql1" and "mysql2" denote two separate mysql connections.

mysql1> create table counter (what varchar(5), id integer, count integer, primary key (what, id));
Query OK, 0 rows affected (0.02 sec)

mysql1> insert into counter values ('total', 0, 0);
Query OK, 1 row affected (0.18 sec)

mysql1> begin;
Query OK, 0 rows affected (0.00 sec)

mysql2> begin;
Query OK, 0 rows affected (0.00 sec)

mysql1> update counter set count = count + 1 where what = 'total' and id = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql2> update counter set count = count + 1 where what = 'total' and id = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql1> commit;
Query OK, 0 rows affected (0.00 sec)

mysql2> commit;
Query OK, 0 rows affected (0.00 sec)

mysql1> select * from counter;
+-------+----+-------+
| what  | id | count |
+-------+----+-------+
| total |  0 |     1 |
+-------+----+-------+
1 row in set (0.00 sec)
[8 Apr 2010 3:43] Valerii Kravchuk
Why do you think that READ COMMITTED isolation level should prevent this lost update?

Both your UPDATEs read committed row with value 0 for counter (when second one started reading the first one already changed counter to 1, but we do NOT take into account uncommitted change, right?), both of them incremented value 0 by 1, got 1 and successfully committed the change, one by one.

Read http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed and http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html:

"With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot."

Other RDBMSes (like Oracle) may work differently in this case, but I see no formal reasons to call this a bug in case of MySQL.
[8 Apr 2010 4:20] Jeff Sturm
Thanks.  I think what was surprising here to our developers is that the row is locked under some circumstances, not in others.  When a row lock is attempted by the 2nd transaction, the column value is not read until the first transaction commits, yielding the expected results.

UPDATE docs say:

"If you set a column to the value it currently has, MySQL notices this and does not update it."

Makes sense, however it is unintuitive that "SET count = count + 1" would ever meet this condition.  I'd expect "current value" and "value it currently has" to be the uncommitted value, i.e. exactly the same.

Moreover in the event the row is not updated, it is also not locked.  The docs might make mention of this fact.

If the maintainers decide this is not a software bug, I'll accept that, but it is probably a documentation bug in that case.
[8 Apr 2010 8:42] Sveta Smirnova
Thank you for the feedback.

I set this report to "Verified" because this change was introduced in version 5.1.12 and there is no note in the change log about something was fixed with this change.

Before 5.1.12 UPDATE in mysql2> failed with "lock wait timeout" error.

More strange results gives following test:

--source include/have_innodb.inc

set global transaction isolation level read committed;
set session transaction isolation level read committed;

create table counter (what varchar(5), id integer, count integer, primary key
(what, id)) engine=innodb;
insert into counter values ('total', 0, 0);
begin;

connect (addconroot, localhost, root,,);
connection addconroot;
begin;

connection default;
update counter set count = count + 1 where what = 'total' and id = 0;
select * from counter;

connection addconroot;
update counter set count = count + 1 where what = 'total' and id = 0;
select * from counter;
update counter set count = count + 1 where what = 'total' and id = 0;
select * from counter;

connection default;
commit;

connection addconroot;
commit;
select * from counter;

connection default;
select * from counter;

--

Result:

=====mysql-5.1=====
=====bug52663=====
set global transaction isolation level read committed;
set session transaction isolation level read committed;
create table counter (what varchar(5), id integer, count integer, primary key
(what, id)) engine=innodb;
insert into counter values ('total', 0, 0);
begin;
begin;
update counter set count = count + 1 where what = 'total' and id = 0;
select * from counter;
what    id      count
total   0       1
update counter set count = count + 1 where what = 'total' and id = 0;
select * from counter;
what    id      count
total   0       0
update counter set count = count + 1 where what = 'total' and id = 0;
select * from counter;
what    id      count
total   0       0
commit;
commit;
select * from counter;
what    id      count
total   0       1
select * from counter;
what    id      count
total   0       1
[8 Apr 2010 16:03] Mikhail Izioumtchenko
Not sure whether it's a bug or not. Likely not. Will assign to Marko to get another opinion. We need to go back to 5.1.12 to see why the behaviour changed. Was it a bug fix?. I think Sveta's test amounts to the same thing as the original test. The problem is as Jeff already mentioned that what a query locks depends on the access path. This is very counterintuitive but this is definitely by design in InnoDB. So in the original example:

mysql2> update counter set count = count + 1 where what = 'total' and id = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

 replace this with

update counter set count = count + 1 where what = 'total';

and you'll wait for the commit of mysql1.

This is of course strange. After all in the consistent read view there's a row with where what = 'total' and id = 0; on which we can do count = count + 1.
But in any case the well documented way to do such things is to use SELECT ... FOR UPDATE.
Regarding Oracle, iirc it would do something like this: each txn happily and concurrently updates count to count + 1 as seen in its consistent read view, so the final counter value will still be 1 unless SELECT ... FOR UPDATE is used. Not 100% sure, though, haven't worked with Oracle for a long time.
[8 Apr 2010 16:19] Mikhail Izioumtchenko
still could be a bug, hidden by the fact that an application will use SELECT ... FOR UPDATE usually.

mysql2> update counter set count = count + 1 where what = 'total' and id = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

I just fail to understand how this can be 0 rows affected. Unless we mean something subtle like 'such rows exist but we can't really update them reliably and aren't going to try'

Note that if you execute SELECT from counter where what = 'total' and id = 0; it will of course show the row as seen in the consistent read view.
[8 Apr 2010 17:10] Sinisa Milivojevic
Michael,

0 rows affected is a result of optimization, either in SQL or InnoDB layer. Simply, it is not necessary to update the row with 100 % same values ...

In my opinion !Bg, but you can make a test case with SELECT ... FOR UPDATE to check for a bug.
[8 Apr 2010 17:32] Mikhail Izioumtchenko
SELECT FOR UPDATE works fine. And without it no application can achieve anything reasonable with this txn isolation level. I'm not very convinced about 0 rows in update. It's the same rowset for different WHERE clauses, but some will lock. On the other hand I'm not sure about it being optimized away. count = count + 1 changes the row. It's a fact that the other transaction already has the resulting count of 1 for the row. However it's not committed yet so we are not supposed to look at it. 
It would be useful to walk the updates in the debugger, too bad I don't have the time at the moment. Let's see what Marko says.
[26 Apr 2010 10:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106494
[26 Apr 2010 10:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106495
[26 Apr 2010 10:28] Marko Mäkelä
This regression was introduced in Bug #3300.
[27 Apr 2010 6:20] Sunny Bains
OK to commit! Reviewed over IRC.
[3 May 2010 16:15] Jingqi Xu
Is this issue related to http://bugs.mysql.com/bug.php?id=49164 ?
[5 May 2010 15:06] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:kristofer.pettersson@sun.com-20100503172109-f9hracq5pqsaomb1) (merge vers: 5.1.47) (pib:16)
[10 May 2010 7:42] Marko Mäkelä
Yes, Bug #49164 is a duplicate of this.
[17 May 2010 5:48] Marko Mäkelä
A short description of the fix for the documentation: Never try semi-consistent read in unique searches. They are only useful in table scans.

Some context: Semi-consistent read was implemented in order to address Bug #3300. Semi-consistent reads do not block when a non-matching record is already locked by some other transaction. If the record is not locked, a lock will be acquired, but it will be released if the record does not match the WHERE condition. This bug was that semi-consistent read was attempted even for UPDATEs whose WHERE condition is of the form pk_col1=constant1,…,pk_colN=constantN. Some code that was built with the assumption that semi-consistent read would be only attempted on table scans, failed.
[20 May 2010 22:52] Paul Dubois
Noted in 5.1.47 changelog.
[28 May 2010 6:00] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100512070920-xgpmqeytp0gc183c) (pib:16)
[28 May 2010 6:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100507093037-7cykrx1n73v0tetc) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 6:56] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100507164602-8w09samq3mpvbxbn) (merge vers: 5.5.5-m3) (pib:16)
[29 May 2010 23:13] Paul Dubois
Noted in 5.5.5, 6.0.14 changelogs.
[17 Jun 2010 12:03] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:martin.skold@mysql.com-20100616204905-jxjg342w35ks9vfy) (merge vers: 5.1.47-ndb-7.0.16) (pib:16)
[17 Jun 2010 12:46] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100615090726-jotpykke96le59w5) (merge vers: 5.1.47-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:30] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:martin.skold@mysql.com-20100616120453-jh7wr05z1vf7r8pm) (merge vers: 5.1.47-ndb-6.3.35) (pib:16)
[21 Jul 2010 13:26] Shane Bester
testcase. run against a server with --innodb-locks-unsafe-for-binlog enabled

Attachment: bug52663.c (text/plain), 6.27 KiB.