Bug #17785 Transactions on Federated Engine do not work when initiated on federated server
Submitted: 28 Feb 2006 14:38 Modified: 12 May 2006 22:49
Reporter: Ivan Zoratti Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.1.6 OS:Linux (Linux Fedora Core 4)
Assigned to: Bugs System CPU Architecture:Any

[28 Feb 2006 14:38] Ivan Zoratti
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)
[12 May 2006 2:01] Patrick Galbraith
In your example, B2 should have returned 1780000 instead of 1, then B1 should have rolled back from 1 to 1780000? Just making sure. Working on this now.
[12 May 2006 22:49] Patrick Galbraith
I need to come up with a solution to implement true XA