zlm@192.168.56.100: [zlm]>create table t1(

     -> c1 int unsigned not null default '',

     -> c2 int unsigned not null default '',

     -> c3 int unsigned not null default '',

     -> c4 int unsigned not null default '',

     -> primary key(c1),

     -> key(c2)

     -> ) engine=innodb;

 Query OK,  rows affected (0.02 sec)

 zlm@192.168.56.100: [zlm]>insert into t1(c1,c2,c3,c4) values(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,);

 Query OK,  rows affected (0.01 sec)

 Records:   Duplicates:   Warnings: 

 zlm@192.168.56.100: [zlm]>select * from t1;

 +----+----+----+----+

 | c1 | c2 | c3 | c4 |

 +----+----+----+----+

 |   |   |   |   |

 |   |   |   |   |

 |   |   |   |   |

 |   |   |   |   |

 |   |   |   |   |

 |   |   |   |   |

 |  |   |   |   |

 +----+----+----+----+

  rows in set (0.01 sec)

 zlm@192.168.56.100: [(none)]>select @@transaction_isolation;

 +-------------------------+

 | @@transaction_isolation |

 +-------------------------+

 | REPEATABLE-READ         | //Make surej in RR transaction isolation level.

 +-------------------------+

  row in set (0.00 sec)

 zlm@192.168.56.100: [(none)]>show variables like 'innodb_status_output_locks';

 +----------------------------+-------+

 | Variable_name              | Value |

 +----------------------------+-------+

 | innodb_status_output_locks | ON    |

 +----------------------------+-------+

  row in set (0.00 sec)

 //Session1：

 zlm@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;

 Query OK,  rows affected (0.00 sec)

 +----+----+----+----+

 | c1 | c2 | c3 | c4 |

 +----+----+----+----+

 |   |   |   |   |

 +----+----+----+----+

  row in set (0.00 sec)

 //Session2:

 monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= lock in share mode;

 Query OK,  rows affected (0.00 sec)

 ERROR  (HY000): Lock wait timeout exceeded; try restarting transaction

 //Session2 requested a "S" record lock on the primary key column where c1=3 while session1 has holded the "X" record lock on the same position,so session2 was blocked util lock timeout.

 //Session1：

 zlm@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;

 Query OK,  rows affected (0.00 sec)

 +----+----+----+----+

 | c1 | c2 | c3 | c4 |

 +----+----+----+----+

 |   |   |   |   |

 +----+----+----+----+

  row in set (0.00 sec)

 //Session2:

 monitor@192.168.56.100: [zlm]>select * from t1 where c1=;

 +----+----+----+----+

 | c1 | c2 | c3 | c4 |

 +----+----+----+----+

 |   |   |   |   |

 +----+----+----+----+

  row in set (0.00 sec)

 //Session1 didn't change this time and session2 request for non-lock consistent read.It read records from a consistent snapshop without locking.

 //Session1:

 zlm@192.168.56.100: [zlm]>begin;select * from t1 where c3= lock in share mode;

 Query OK,  rows affected (0.01 sec)

 Empty set (0.00 sec)

 //Session2:

 monitor@192.168.56.100: [zlm]>begin;select * from t1 where c3= for update;

 Query OK,  rows affected (0.00 sec)

 ERROR  (HY000): Lock wait timeout exceeded; try restarting transaction

 //Although there's no record satisfied with c3=7 but notice that there's no index on c3 column.Therefore,the session1 has holded a "S" record for all the records on column c1 in table t1.Then session2 asked for the "X" record lock for "c3=10"(even it does not exixt),it was blocked.

 //Session1:

 zlm@192.168.56.100: [zlm]>begin;select * from t1 where c3= lock in share mode;

 Query OK,  rows affected (0.00 sec)

 Empty set (0.00 sec)

 //Session2:

 monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;

 Query OK,  rows affected (0.00 sec)

 ERROR  (HY000): Lock wait timeout exceeded; try restarting transaction

 //This is similar with "test 3".Session1 has holded a "S" record lock of all records on column c3.The record where c1=6 means c3=5,it's also in the range of all records.So session2 was blocked.

 //Session1:

 zlm@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;

 Query OK,  rows affected (0.00 sec)

 +----+----+----+----+

 | c1 | c2 | c3 | c4 |

 +----+----+----+----+

 |   |   |   |   |

 +----+----+----+----+

  row in set (0.00 sec)

 //Session2:

 monitor@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;

 Query OK,  rows affected (0.00 sec)

 ERROR  (HY000): Lock wait timeout exceeded; try restarting transaction

 //Because of the secondary index key on column c2,it generated a "X" record lock and a gap lock(record + gap = next key lock).Although the gap lock between two sessions can be coexistent,but record locks do not.So session2 was blocked.

 //Session1:

 zlm@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;

 Query OK,  rows affected (0.00 sec)

 +----+----+----+----+

 | c1 | c2 | c3 | c4 |

 +----+----+----+----+

 |   |   |   |   |

 +----+----+----+----+

  row in set (0.00 sec)

 //Session2:

 monitor@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;

 Query OK,  rows affected (0.00 sec)

 Empty set (0.00 sec)

 //This time session2 was not blocked.They've requested a different "X" record lock individually even they still hold the gap lock.

 //Session1:

 zlm@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;

 Query OK,  rows affected (0.00 sec)

 +----+----+----+----+

 | c1 | c2 | c3 | c4 |

 +----+----+----+----+

 |   |   |   |   |

 +----+----+----+----+

  row in set (0.00 sec)

 //Session2:

 monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= and c3= for update;

 Query OK,  rows affected (0.00 sec)

 ERROR  (HY000): Lock wait timeout exceeded; try restarting transaction

 //The query condition in session2 is c1=4.It means c2=2,this is similar with test 5(asked for the equal line).

 //Session1:

 zlm@192.168.56.100: [zlm]>begin;update t1 set c4= where c2>=;

 Query OK,  rows affected (0.00 sec)

 Query OK,  rows affected (0.00 sec)

 Rows matched:   Changed:   Warnings: 

 //Session2:

 monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;

 Query OK,  rows affected (0.00 sec)

 Empty set (0.00 sec)

 //The records according to the query condition c2>=4 were c1=8 and c1=10.

 //Even though there's a index key on c1 but it's a primary key which doesn't generate gap lock.So session2 's asking for "X" record lock of c1=7 was not blocked.

• We should pay more attention to innodb row-level locks.If there's no key on the relevant column,the locks will be escalated to "table-level"(all records will be locked) locks.
• In the RR transaction isolation level,Secondary index generates gap locks(LOCK_ORDINARY) to prevent phantom read while primary index and unique index do not.They only hold record locks(LOCK_REC_NOT_GAP).
• In the RC transaction isolation level,there're no gap locks.Therefore,it's concurrency is better than that in RR mode,but the consistency is poor as well.
• As for which transaction isolation level we should choose is depend on your purpose:for more consistency or for more concurrency.