Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL's "REPEATABLE READ" and "SELECT ... FOR UPDATE"
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)
@nickhoffman
Copy link
Author

nickhoffman commented Apr 24, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment