MySQL Bugs: #52663: Lost update incrementing column value under READ COMMITTED isolation level

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ä	CPU Architecture:	Any
Tags:	regression

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)

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.

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.

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

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.

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.

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.

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.

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

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

This regression was introduced in Bug #3300.

OK to commit! Reviewed over IRC.

Is this issue related to http://bugs.mysql.com/bug.php?id=49164 ?

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)

Yes, Bug #49164 is a duplicate of this.

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.

Noted in 5.1.47 changelog.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100512070920-xgpmqeytp0gc183c) (pib:16)

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)

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)

Noted in 5.5.5, 6.0.14 changelogs.

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)

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)

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)

testcase. run against a server with --innodb-locks-unsafe-for-binlog enabled

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