Optionally lock scanned rows with join/subquery

[yoshinorim]

InnoDB locks scanned rows. This includes join and subquery like below:

• INSERT INTO t1 SELECT .. FROM t2
• CREATE TABLE t1 SELECT .. FROM t2
• UPDATE (DELETE) t1 SET ... WHERE ... (SELECT ... FROM t2)
• UPDATE (DELETE) t1 JOIN t2 ON ...

InnoDB holds shared lock on scanned rows in t2. MyRocks does not hold any lock in t2. Locking t2 is not really needed with RBR. But to make transition from InnoDB to MyRocks easier, it might be useful to have an option to lock scanned rows from t2. Since MyRocks currently does not support shared lock, hold exclusive lock instead. Also, when the option is turned on, scanned but not found rows should be locked too (#165 should be skipped).

Here is an example:

create table t1 (id1 int, id2 int, value int, primary key (id1, id2));
create table t2 (id1 int, id2 int, value int, primary key (id1, id2));
insert into t1 values (1,1,1),(2,2,2),(3,3,3);
insert into t2 values (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

BEGIN;
UPDATE t1 JOIN t2 ON t1.id1 = t2.id1 AND t1.id2 = t2.id2 SET t1.value=t1.value+100 WHERE t2.id1=3 and t2.id2=3;

With this option enabled, id1=3, id2=3 in t2 should be locked.

InnoDB implements this logic in ha_rocksdb::store_lock(). We may reuse this in MyRocks.

        } else if ((lock_type == TL_READ && in_lock_tables)
                   || (lock_type == TL_READ_HIGH_PRIORITY && in_lock_tables)
                   || lock_type == TL_READ_WITH_SHARED_LOCKS
                   || lock_type == TL_READ_NO_INSERT
                   || (lock_type != TL_IGNORE
                       && sql_command != SQLCOM_SELECT)) {

                /* The OR cases above are in this order:
                1) MySQL is doing LOCK TABLES ... READ LOCAL, or we
                are processing a stored procedure or function, or
                2) (we do not know when TL_READ_HIGH_PRIORITY is used), or
                3) this is a SELECT ... IN SHARE MODE, or
                4) we are doing a complex SQL statement like
                INSERT INTO ... SELECT ... and the logical logging (MySQL
                binlog) requires the use of a locking read, or
                MySQL is doing LOCK TABLES ... READ.
                5) we let InnoDB do locking reads for all SQL statements that
                are not simple SELECTs; note that select_lock_type in this
                case may get strengthened in ::external_lock() to LOCK_X.
                Note that we MUST use a locking read in all data modifying
                SQL statements, because otherwise the execution would not be
                serializable, and also the results from the update could be
                unexpected if an obsolete consistent read view would be
                used. */

                /* Use consistent read for checksum table */

                if (sql_command == SQLCOM_CHECKSUM
                    || ((srv_locks_unsafe_for_binlog
                        || !(thd_test_options(thd, OPTION_BIN_LOG))
                        || trx->isolation_level <= TRX_ISO_READ_COMMITTED)
                        && trx->isolation_level != TRX_ISO_SERIALIZABLE
                        && (lock_type == TL_READ
                            || lock_type == TL_READ_NO_INSERT)
                        && (sql_command == SQLCOM_INSERT_SELECT
                            || sql_command == SQLCOM_REPLACE_SELECT
                            || sql_command == SQLCOM_UPDATE
                            || sql_command == SQLCOM_DELETE
                            || sql_command == SQLCOM_CREATE_TABLE))) {

                        /* If we either have innobase_locks_unsafe_for_binlog
                        option set or if bin log is switched off or
                        this session is using READ COMMITTED
                        isolation level and isolation level of the transaction
                        is not set to serializable and MySQL is doing
                        INSERT INTO...SELECT or REPLACE INTO...SELECT
                        or UPDATE ... = (SELECT ...)
                        or DELETE ... WHERE (SELECT ...) or CREATE  ...
                        SELECT... without FOR UPDATE or IN SHARE
                        MODE in select, then we use consistent read
                        for select.
                        Note that deletes and updates that use multi-table
                        syntax are SQLCOM_UPDATE_MULTI and SQLCOM_DELETE_MULTI
                        rather than SQLCOM_UPDATE and SQLCOM_DELETE. */

                        prebuilt->select_lock_type = LOCK_NONE;
                        prebuilt->stored_select_lock_type = LOCK_NONE;
                } else {
                        prebuilt->select_lock_type = LOCK_S;
                        prebuilt->stored_select_lock_type = LOCK_S;
                }

[jkedgar]

@yoshinorim, I've started looking into this. In ha_rocksdb::store_lock() we set two things - we set lock_rows (and lock_for_write) to tell ourselves how to lock later and we set lock.type which appears to tell MySQL information about what we are allowing.

The change that I need to do would appear to be only related to the first part (lock_rows and lock_for_write) and not for the second. Is that correct?

If so, I think what I need to do is if I would be setting lock_rows to false, check the option and the above criteria and if everything fits I would set lock_rows to true (but leave lock_for_write as false). Does that sound right?

[yoshinorim]

@jkedgar Yes, updating lock_rows and lock_for_write depending on config (session) variable would work.

[jkedgar]

https://reviews.facebook.net/D56313

[jkedgar]

This added a new option (named rocksdb-lock-scanned-rows) that can be used to act more like InnoDB in regards to locking rows that are scanned during an insert/update but are not going to be changed.