MySQL Bugs: #63751: "SELECT ... WHERE w_id%4=1 FOR UPDATE" locks whole table

Bug #63751	"SELECT ... WHERE w_id%4=1 FOR UPDATE" locks whole table
Submitted:	14 Dec 2011 20:06	Modified:	21 Dec 2011 20:55
Reporter:	Grigori Khmyrov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
SELECT 1 FROM s1_tpcc.warehouse WHERE w_id%12=2 GROUP BY 1 FOR UPDATE;
or 
SELECT * FROM s1_tpcc.warehouse WHERE w_id%12=2 FOR UPDATE;

locks whole table instead of few records.

How to repeat:
Use two console 1 and 2 connecting for example to DBT2 schema (TPC-C test).
1. SET AUTOCOMMIT=0;
2. SET AUTOCOMMIT=0;
1. SELECT * FROM s1_tpcc.warehouse WHERE w_id%12=2 FOR UPDATE; returns (2,14)
2. UPDATE warehouse SET w_name='ttt1' WHERE w_id=5; already locked!!!

If in select we use w_id=2 it lock only that record.

Suggested fix:
Should lock correct set of records. Same as in returning result set.

Please, send the output of:

explain SELECT * FROM s1_tpcc.warehouse WHERE w_id%12=2;

I wonder if any index is used.

There are results from "locking wrong" and "locking right":
mysql> explain SELECT * FROM s1_tpcc.warehouse WHERE w_id%12=2;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | warehouse | ALL  | NULL          | NULL | NULL    | NULL |   13 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM s1_tpcc.warehouse WHERE w_id=2;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | warehouse | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

It sounds roght for me. When expression in where it's OK to use natural order.

Well, "ALL" means that index is not used and every row is locked (as I expected).

Please, send the output of:

show table status like 'warehouse'\G
show indexes from warehouse;

But as you compare expression on column with some constant, optimizer can hardly use proper index...

mysql> show table status like 'warehouse'\G
*************************** 1. row ***************************
           Name: warehouse
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 1197473792
 Auto_increment: NULL
    Create_time: 2011-11-21 12:51:24
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.05 sec)

mysql> show indexes from warehouse;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| warehouse |          0 | PRIMARY  |            1 | w_id        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

Looks like because it is expression in where clause engine will read all records and mark them as locked for update.

Statistics is missing for your table it seems. Please, execute

analyze table warehouse;

and check EXPLAIN results for the original query again.

mysql> analyze table warehouse;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| s1_tpcc.warehouse | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.30 sec)

mysql> explain SELECT * FROM s1_tpcc.warehouse WHERE w_id%12=2;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | warehouse | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.15 sec)

Well, I still do not see any way for optimizer to use index to find 1/4 of all rows in the table (potentially), with large intervals between them (values like 1, 5, 9, 13 etc) in general case. So "ALL" access path is expected and locking is unavoidable.

Is it any way to lock only actually selected records instead of all records read by engine? Why do I care how many records and how engine read to select my data? I know "SELECT ... WHERE id IN (3, 7, 9)" will do it if "id" is key field.