As I read from here and here if the transaction level is REPEATABLE-READ no other transaction can read data that being accessed by this transaction.But I found this when I testing.* (MySQL server version: 5.0.21-community-nt /innodb engine)*.
Transaction A:
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE manufacturer
-> SET lead_time = 22
-> WHERE mcode = 'ACL';
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Transaction B
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM Manufacturer ;
+-------+------------+-----------+
| mcode | mname | lead_time |
+-------+------------+-----------+
| ACL | ACL Cables | 2 |
| HAY | Haycarb | 4 |
| HYL | Hayleys | 5 |
+-------+------------+-----------+
3 rows in set (0.00 sec)
The thing is I expected to transaction B to wait until the Transaction A to commit but it does not happen. How to apply this?
Repeatable Read applies within a single transaction. It says that you can read a value as often as you like within a transaction and you will get the same answer. Also there is no way to tell which transaction starts first, A or B. Furthermore, a transaction can be interrupted at any time, and another transaction run or continued. Your example does not reflect reality in that you run (on the command line) A first to completion then B to completion. This is serialization.