MySQL's "REPEATABLE READ" and "SELECT ... FOR UPDATE"

gistfile1.txt

mysql> show create table numbers\G
*************************** 1. row ***************************
       Table: numbers
Create Table: CREATE TABLE `numbers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


##### Session A

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

mysql> SELECT @@global.tx_isolation, @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+
1 row in set (0.00 sec)

mysql> select * from numbers;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
+----+------+
1 row in set (0.00 sec)


##### Session B

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

mysql> SELECT @@global.tx_isolation, @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+
1 row in set (0.00 sec)

mysql> select * from numbers;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
+----+------+
1 row in set (0.00 sec)


##### Session A

mysql> select * from numbers for update;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
+----+------+
1 row in set (0.00 sec)


##### Session B

mysql> select * from numbers for update;


##### Session A

mysql> update numbers set num = 20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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


##### Session B

+----+------+
| id | num  |
+----+------+
|  1 |   20 |
+----+------+
1 row in set (20.09 sec)

Referenced in a comment on this blog post:
http://www.barbarycodes.com/2010/02/18/how-to-make-100-with-mysql-repeatable-read-transactions/