"how many rows will be locked by select … order by xxx limit 1 for update?" Code Answer

this is a great question. innodb is a row level locking engine, but it has to set additional locks to ensure safety with the binary log (used for replication; point in time recovery). to start explaining it, consider the following (naive) example:

session1> start transaction;
session1> delete from users where is_deleted = 1; # 1 row matches (user_id 10), deleted.
session2> start transaction;
session2> update users set is_deleted = 1 where user_id = 5; # 1 row matches.
session2> commit;
session1> commit;

because statements are only written to the binary log once committed, on the slave session#2 would apply first, and would produce a different result, leading to data corruption.

so what innodb does, is sets additional locks. if is_deleted is indexed, then before session1 commits nobody else will be able to modify or insert into the range of records where is_deleted=1. if there are no indexes on is_deleted, then innodb needs to lock every row in the entire table to make sure the replay is in the same order. you can think of this as locking the gap, which is different concept to grasp from row-level locking directly.

in your case with that order by position asc, innodb needs to make sure that no new rows could be modified between the lowest key value and a "special" lowest possible value. if you did something like order by position desc.. well, then nobody could insert into this range.

so here comes the solution:

• statement based binary logging sucks. i really look forward to a future where we all switch to row based binary logging (available from mysql 5.1, but not on by default).

• with row-based replication, if you change the isolation level to read-committed, then only the one row that matches needs to be locked.

• if you want to be a masochist, you can also turn on innodb_locks_unsafe_for_binlog with statement-based replication.

update 22 april: to copy + paste my improved version of your testcase (it was not searching 'in the gap'):

session1> create table test (id int not null primary key auto_increment, data1 int, data2 int, index(data1)) engine=innodb;
query ok, 0 rows affected (0.00 sec)

session1> insert into test values (null, 1, 2), (null, 2, 1), (5, 2, 2), (6, 3, 3), (3, 3, 4), (4, 4, 3);
query ok, 6 rows affected (0.00 sec)
records: 6  duplicates: 0  warnings: 0

session1> start transaction;
query ok, 0 rows affected (0.00 sec)

session1> select id from test order by data1 limit 1 for update;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

session2> insert into test values (null, 0, 99); # blocks - 0 is in the gap between the lowest value found (1) and the "special" lowest value.

# at the same time, from information_schema:

localhost information_schema> select * from innodb_locksg
*************************** 1. row ***************************
    lock_id: 151a1c:1735:4:2
lock_trx_id: 151a1c
  lock_mode: x,gap
  lock_type: record
 lock_table: `so5694658`.`test`
 lock_index: `data1`
 lock_space: 1735
  lock_page: 4
   lock_rec: 2
  lock_data: 1, 1
*************************** 2. row ***************************
    lock_id: 151a1a:1735:4:2
lock_trx_id: 151a1a
  lock_mode: x
  lock_type: record
 lock_table: `so5694658`.`test`
 lock_index: `data1`
 lock_space: 1735
  lock_page: 4
   lock_rec: 2
  lock_data: 1, 1
2 rows in set (0.00 sec)

# another example:
select * from test where id < 1 for update; # blocks