session A:session B:
       
mysql> show variables like '%iso%';mysql> show variables like '%iso%';
+---------------+-----------------++---------------+-----------------+
| Variable_name | Value           || Variable_name | Value           |
+---------------+-----------------++---------------+-----------------+
| tx_isolation  | REPEATABLE-READ || tx_isolation  | REPEATABLE-READ |
+---------------+-----------------++---------------+-----------------+
1 row in set (0.00 sec) 1 row in set (0.00 sec) 
       
mysql> begin;  mysql> begin;  
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)
       
mysql> select * from user; mysql> select * from user; 
+----+--------+  +----+--------+  
| id | name   |  | id | name   |  
+----+--------+  +----+--------+  
|  1 | ligang |  |  1 | ligang |  
|  2 | lvbu   |  |  2 | lvbu   |  
|  3 | zhaoyu |  |  3 | zhaoyu |  
+----+--------+  +----+--------+  
3 rows in set (0.00 sec) 3 rows in set (0.00 sec) 
       
mysql> update user set name='ddd' where id=4;   
Query OK, 0 rows affected (0.00 sec)    
Rows matched: 0  Changed: 0  Warnings: 0   
(把存在ddd之外的记录全部加X锁)    
   mysql> insert into user values(4,'ddd');
   ERROR 1205 (HY000): Lock wait timeout exceeded; 
   try restarting transaction 
       
mysql> commit;      
Query OK, 0 rows affected (0.00 sec)    
       
   mysql> insert into user values(4,'ddd');
   Query OK, 1 row affected (0.00 sec)
       
   mysql> select * from user; 
   +----+--------+  
   | id | name   |  
   +----+--------+  
   |  4 | ddd    |  
   |  1 | ligang |  
   |  2 | lvbu   |  
   |  3 | zhaoyu |  
   +----+--------+  
   4 rows in set (0.01 sec) 
       
mysql> begin;      
Query OK, 0 rows affected (0.00 sec)    
       
mysql> select * from user;     
+----+--------+      
| id | name   |      
+----+--------+      
|  1 | ligang |      
|  2 | lvbu   |      
|  3 | zhaoyu |      
+----+--------+      
3 rows in set (0.00 sec)     
       
   mysql> commit;  
   Query OK, 0 rows affected (0.00 sec)
       
mysql> select * from user;     
+----+--------+      
| id | name   |      
+----+--------+      
|  1 | ligang |      
|  2 | lvbu   |      
|  3 | zhaoyu |      
+----+--------+      
3 rows in set (0.00 sec)     
       
mysql> commit;      
Query OK, 0 rows affected (0.00 sec)    
       
mysql> select * from user;     
+----+--------+      
| id | name   |      
+----+--------+      
|  4 | ddd    |      
|  1 | ligang |      
|  2 | lvbu   |      
|  3 | zhaoyu |      
+----+--------+      
4 rows in set (0.00 sec)

 

 

 

session A:session B:
       
mysql> show variables like '%iso%';mysql> show variables like '%iso%';
+---------------+----------------++---------------+----------------+
| Variable_name | Value          || Variable_name | Value          |
+---------------+----------------++---------------+----------------+
| tx_isolation  | READ-COMMITTED || tx_isolation  | READ-COMMITTED |
+---------------+----------------++---------------+----------------+
1 row in set (0.00 sec) 1 row in set (0.00 sec) 
       
mysql> begin;  mysql> begin;  
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)
       
mysql> select * from user; mysql> select * from user; 
+----+--------+  +----+--------+  
| id | name   |  | id | name   |  
+----+--------+  +----+--------+  
|  4 | ddd    |  |  4 | ddd    |  
|  1 | ligang |  |  1 | ligang |  
|  2 | lvbu   |  |  2 | lvbu   |  
|  3 | zhaoyu |  |  3 | zhaoyu |  
+----+--------+  +----+--------+  
4 rows in set (0.02 sec) 4 rows in set (0.02 sec) 
       
mysql> update user set name='cvvt' where id=5;  
Query OK, 0 rows affected (0.01 sec)    
Rows matched: 0  Changed: 0  Warnings: 0   
       
   mysql> insert into user values (5,'cvvt');
   Query OK, 1 row affected (0.00 sec)
       
   mysql> select * from user; 
   +----+--------+  
   | id | name   |  
   +----+--------+  
   |  5 | cvvt   |  
   |  4 | ddd    |  
   |  1 | ligang |  
   |  2 | lvbu   |  
   |  3 | zhaoyu |  
   +----+--------+  
   5 rows in set (0.00 sec) 
       
mysql>  select * from user;    
+----+--------+      
| id | name   |      
+----+--------+      
|  4 | ddd    |      
|  1 | ligang |      
|  2 | lvbu   |      
|  3 | zhaoyu |      
+----+--------+      
4 rows in set (0.00 sec)     
       
   mysql> commit;  
   Query OK, 0 rows affected (0.00 sec)
       
mysql>  select * from user;    
+----+--------+      
| id | name   |      
+----+--------+      
|  5 | cvvt   |      
|  4 | ddd    |      
|  1 | ligang |      
|  2 | lvbu   |      
|  3 | zhaoyu |      
+----+--------+      
5 rows in set (0.00 sec)