Chris, please send your messages to [EMAIL PROTECTED] The newsgroup mailing.database.mysql is only a mirror.
----- Original Message ----- From: "Chris" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, August 28, 2003 5:00 PM Subject: Repeateable read isolation level question > I think I understand the concept of repeatable read, but this scenario > doesn't make sense to me, if someone can explain. > > Say you have an innodb table test with a string column s = 'blank' and > are using the default isolation level of repeatable read. > > Session 1: > begin; > > Session 2: > begin; > > S1: > update test set s = 'session one' where id = 1; > commit; > > S2: > mysql> select * from test; > +-------+------+ > | s | id | > +-------+------+ > | blank | 1 | > +-------+------+ > 1 row in set (0.00 sec) > > (so we cannot see the changes from S1's transaction yet, only when S2 > commits or rolls back will it see the changes) > > mysql> update test set s = 'session two' where s = 'blank' and id = 1; > Query OK, 0 rows affected (0.00 sec) > Rows matched: 0 Changed: 0 Warnings: 0 > > mysql> update test set s = 'session two' where s = 'session one' and > id = 1; > Query OK, 1 row affected (0.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > So this is very weird to me, that the first update fails. You cannot > see the updates from session 1 in a select due to the isolation level, > yet your where clause sees the updates...??? > > Mind you, this is good since it avoids lost updates, but I just don't > understand it--it seems inconsistent. The idea is that a plain SELECT is a consistent non-locking read which reads a historical snapshot of the database. But, of course, an UPDATE must not read a historical snapshot. An UPDATE must get the latest data, and lock the rows. If you make a locking SELECT, then you see what is going on in the UPDATE, because a locking SELECT always reads the latest data: SELECT ... LOCK IN SHARE MODE; or SELECT ... FOR UPDATE; > Thanks, > Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
