I have two sessions doing this.
Session 1>start transaction;
Session 1>select * from account for update;
Session 2>start transaction;
Session 2>select * from account for update; //waiting.
Since IX lock is compatible with IX lock shouldn't session two also get IX lock without waiting.
Even the following set of statemnts do not work.
Session 1>start transaction;
Session 1>select * from account where i = 1 for update;
Session 2>start transaction;
Session 2>select * from account where i = 2 for update; //waiting.
The isolation level is Repeatable Read. Are things different in this isolation level?
Source for compatibility this
The two queries you show acquire IX locks for the table, which are compatible with each other.
But SELECT...FOR UPDATE also proceeds to acquire X locks on the rows it examines. These are conflicts, and that's why the second SELECT...FOR UPDATE waits.
In your example where one query is for rows WHERE i = 1 and the other is for rows WHERE i = 2, they will not return the same rows, but they will examine an overlapping set of rows if i has no index. Examining rows means InnoDB fetches the rows and then tests them against the conditions in the WHERE clause. Based on the conditions, it may skip some rows it examines, and it only returns examined rows if they test true.
If you have an index on i, InnoDB can exclude non-matching rows without examining the rows. Under these conditions, your example will show no conflict.
Session 1>ALTER TABLE account ADD INDEX (i);
Session 1>start transaction;
Session 1>select * from account where i = 1 for update;
Session 2>start transaction;
Session 2>select * from account where i = 2 for update; // does NOT wait