UPDATE may hang when it does a full table scan and updates PK values

[spetrunia]

Example:

create table t4 (
  id int, 
  value int, 
  primary key (id)
) engine=rocksdb;

insert into t4 values(1,1),(2,2);
set autocommit=0;
begin;
insert into t4 values (50,50);
select * from t4;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
| 50 |    50 |
+----+-------+

Now, this query will hang:

update t4 set id=id+100;

Looking at SHOW PROCESSLIST one can see that the query is actually processing lots of rows:

+----+------+-----------+------+---------+------+-------------+-------------------------+---------------+-----------+-------+
| Id | User | Host      | db   | Command | Time | State       | Info                    | Rows examined | Rows sent | Tid   |
+----+------+-----------+------+---------+------+-------------+-------------------------+---------------+-----------+-------+
|  1 | root | localhost | j5   | Query   |   44 | updating    | update t4 set id=id+100 |       2692635 |         0 | 27233 |    
|  2 | root | localhost | NULL | Query   |    0 | cleaning up | show processlist        |             0 |         0 | 27306 |            
+----+------+-----------+------+---------+------+-------------+-------------------------+---------------+-----------+-------+

[jkedgar]

I believe the issue is that each row is being updated over and over again (from id 1 to 101 to 201 to 301, etc.). In a previous database the solution was implemented in the SQL layer - if a key was being updated we did not allow that key to be used to find the rows to update - i.e. the optimizer was not allowed to select that path. Since MySQLonRocksDB can only access rows by their keys, maybe we need to have a flag that can indicate that a row has already been updated and as we come to each row to see if we should update it, skip those with the flag set.

[spetrunia]

Yes the cause is rows being updated over and over.

I don't think a flag would be needed - MyRocks is not the first MySQL storage engine to have this property. It just fails to inform the SQL layer that full table scan is actually scanning primary key.

I already have a patch, I'll commit it shortly.

[spetrunia]

https://reviews.facebook.net/D45519