Mysql locking entire table when using 'in' predicate

I have a strange problem with Mysql locking the entire table when making a query with a particular 'in' predicate.

This is the table concerned:

create table TableA ( 
USERID bigint(20) unsigned not null, 
BLOBID tinyint unsigned not null, 
blob_contents mediumblob not null, 
primary key(ID1, ID2)) engine=innodb default charset=latin1; 

There is a composite primary key set of (USERID, BLOBID). Here is some example data:

{[1, 1, <blob>], 
[1, 2, <blob>], 
[1, 3, <blob>], 
[2, 1, <blob>], 
[2, 2, <blob>]} 

Say I want to get the data for blobs 1,2 and 3 for user 2 (I don't know at this point that there is no row for the blob with id 3 for this user). This is the query I would run:

select * from TableA where USERID = 2 and BLOBID in (1, 2, 3) for update;

The query returns blobs 1 and 2 for user2 and finds nothing for blobid 3. Unfortunately even with the primary key it seems that this query locks the entire table. You can see the join type is 'ALL' when explaining the query:

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | TABLEA | ALL  | PRIMARY       | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

When I remove '3' from the in clause, Mysql correctly uses the primary key and locks just the 2 row开发者_运维知识库s required:

select * from TableA where USERID = 2 and BLOBID in (1, 2) for update;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | TableA | range | PRIMARY       | PRIMARY | 9       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

Is it possible to make this query and yet only lock the rows actually returned by the query?