irek,
Quoting your example/test:
--Session 1
>mysql> create index exp on ezd(expired);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from ezd where expired=0 for update;
+----+---------+---------------------+------------+
| id | expired | mtime | name_trunk |
+----+---------+---------------------+------------+
| 1 | 0 | 2012-02-18 23:53:09 | car0 |
+----+---------+---------------------+------------+
1 row in set (0.00 sec)
--Session 2
>mysql> update ezd set name_trunk='car0' where expired=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
>mysql> update ezd set name_trunk='car0' where expired=0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
>Hurray !!! Now MySql locks only rows with expired=0, and doesn't touch rows with expired=1.
>But wait a minute - what would be if we tried to modify the 'expired' column instead of 'name_trunk' ?
(still in session 2):
>mysql> update ezd set expired=0 where expired=0;
>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
You are trying to update rows that are LOCKED by session 1, no?
Related bug
http://bugs.mysql.com/bug.php?id=57973
This bug explains why a range scan (expired!=1) locks a row that should be skipped even in READ COMMITTED and innodb_locks_unsafe_for_binlog = 1.
Edited 1 time(s). Last edit at 02/21/2012 05:02AM by Aftab Khan.