Is this update lost?

I just want unders开发者_JS百科tand the meaning of "update lost" which is solved by transaction.

Please take a look at following two transaction, I use mysql 5.5.8 and innodb storage engine:

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

      session 1 (T1)                       session 2(T2)
 -------------------------------------------------------------------------
0                               |           begin;
 -------------------------------------------------------------------------
1           begin;              |        
 -------------------------------------------------------------------------
2   select count from  counter  |
    where id = 0;               |
 -------------------------------------------------------------------------
3                               |   update counter set count = 50 
                                |   where id = 0; 
 -------------------------------------------------------------------------
4                               |   commit;
 ------------------------------------------------------------------------
5  update counter set count     |
   = 1000 where id = 0;         |
 -------------------------------------------------------------------------
6           commit;             |        
 -------------------------------------------------------------------------

you can consider value 1000 and 50 as this:

1. The update value 1000 depends on the read of count, i.e. select.
2. The update value 50 depends on another read(isn't conflict with session1).

So, this is the typical read-write-write dependency.

After session1 (T1) commit and execute 'select count from counter where id=0' again, the count will be 1000. I am wondering whether it is update lost or not? if not why? If I remembered correctly any kind of update last will be avoided in any isolation level.

One of possible fix is using "select count from counter where id = 0 for update;" at step 2, that's equivalent to add xlock on the record, and hence T2 will be blocked. So this is serial executed as [T1,T2].

Is this the (known) bug of Innodb? Note, this isn't equivalent to execute [T2,T1], because, T1 will read 50 other 0 with this sequence, and the final result will be different.

Thanks

Yes, ofcourse the update is lost. To the outside world, your transaction only really "executes" when it commits. To a third party, your schema corresponds to:

1. Session 2 updates counter to 50 for ID 0
2. Session 1 updates counter to 1000 for ID 0

You don't actually even need transactions for this: the transactions make no difference. They would matter if your session 1 did its select AFTER session 2 did its update. In that case, with transactions, Session 1 will read value 0, but without transactions it would read value 50.

This is because of the INNODB default isolation level REPEATABLE READ

yes,your scenario is exactly what lost update means. in postgresql,T1 will be aborted by the dbms to prevent lost update.