Description:
Supposing that we are using the default isolation level (repeatable read) and that table T1 (InnoDB) is physically stored on Node A and federated on Node B, the tests on the current version return these results:
- Transactions initiated on Node A are correctly handled and isolated until
COMMIT or ROLLBACK is executed.
- Transactions initiated on Node B are affected by any session on Node B, committing automatically the statements still in the active transaction.
How to repeat:
- Create a innodb table on node A (example, the City table in the popular world database)
- Federate the table on node B
- Run one mysql client on node A (session A1) and two mysql client on node B (sessions B1 and B2)
Execute this:
A1> SELECT population FROM city WHERE id = 1;
+------------+
| population |
+------------+
| 1780000 |
+------------+
1 row in set (0.00 sec)
B1> SELECT population FROM city WHERE id = 1;
+------------+
| population |
+------------+
| 1780000 |
+------------+
1 row in set (0.00 sec)
B2> SELECT population FROM city WHERE id = 1;
+------------+
| population |
+------------+
| 1780000 |
+------------+
1 row in set (0.00 sec)
B1> BEGIN WORK;
B1> UPDATE city SET population = 1 WHERE id = 1;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
B1> SELECT population FROM city WHERE id=1;
+------------+
| population |
+------------+
| 1 | <<<---- this is correct (in the transaction)
+------------+
1 row in set (0.13 sec)
A1> SELECT population FROM city WHERE id = 1;
+------------+
| population |
+------------+
| 1780000 | <<<---- this is correct (isolated, rep. read)
+------------+
1 row in set (0.00 sec)
B2> SELECT population FROM city WHERE id=1;
+------------+
| population |
+------------+
| 1 | <<<---- this is *wrong* (it's like dirty read)
+------------+
1 row in set (0.13 sec)
B1> ROLLBACK WORK;
B1> SELECT population FROM city WHERE id=1;
+------------+
| population |
+------------+
| 1 | <<<---- this is *wrong* (it should have been rolled back)
+------------+
1 row in set (0.13 sec)
