'lock in share mode' is for what? usefull?

[coco-b1107]

This is the place to report a bug, ask a question, or suggest an enhancement.

This is also the place to make a discussion before creating a PR.

If this is a bug report, please provide a test case (e.g., your table definition and gh-ost command) and the error output.

Please use markdown to format code or SQL: https://guides.github.com/features/mastering-markdown/

Please label the issue on the right (bug, enhancement, question, etc.).

And please understand if this issue is not addressed immediately or in a timeframe you were expecting.

Thank you!

[shlomi-noach]

Please be more specific, what is your concern, where is the relevant code?

[coco-b1107]

an enhancement

1. rowcopy query>> insert ignore into ghosttable (sharedcolumn) (select sharedcolumn from originaltable ... lock in share mode)
2. lock in share mode>> prevent from updating the row data when select a row data, get row data uniformity
3. Online DML operation (take off 'lock in share mode') * insert; no effect * delete; when select, however, the row data has been deleted, no effect * update; when select, get the row data updating, but, binlog prefer(data uniformity), no effect
4. conclusion 'lock in share mode' has no effect, would be took off

[shlomi-noach]

Thank you. In pt-online-schema-change there's a reference to https://bugs.mysql.com/bug.php?id=45694 for the reason to use lock in share mode. this bug is long since closed.

I think lock in share mode can be removed, especially since we're not using triggers and the copy is not synchronous. cc @ggunson for additional thoughts.

[ggunson]

tl;dr I say test it out, but I'm not sure.

Here are my additional thoughts.

1. My understanding has been that insert...select does a shared read lock on the selected rows already, making LOCK IN SHARE MODE redundant. The "High Performance MySQL" book first agrees with me on this (assuming you keep innodb_locks_unsafe_for_binlog=OFF).

2. Do note, insert...select behaviour is affected by binlog_format:

INSERT ... SELECT requires a greater number of row-level locks [in statement-based replication] than with row-based replication. https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html

3. And later in the "High Perf MySQL" book they state that

In particular, INSERT ... SELECT locks all the rows it reads from the source table by default when using statement-based replication High Perf MySQL 3rd edition p 503

So I think the lock in share mode is redundant, but maybe only if you're on STMT? I can't find anything saying how the locking is different with RBR. And assuming the copying and binlog writes reapplying happen in parallel, I'm wondering if the gh-ost results might change depending on the master setup (since the master can be STMT, there just needs to be RBR somewhere in the cluster).

If we tested with a large chunk size, that might reproduce a situation where, say, a delete happens (and gets logged to binlog and reapplied) in the middle of an insert...select on the range that contains the deleted row? If that's a possible scenario.

[shlomi-noach]

I've sent #547 to testing. I suspect, though, that running or not running a LOCK IN SHARE MODE on replica is one of the few things which actually differentiate between a migration on master and on replica.

[shlomi-noach]

Testing are fine. Though I still think testing on replica does not reflect how this change would affect migration on master.

[zmoazeni]

I've been thinking about this one for a bit.

I suspect, though, that running or not running a LOCK IN SHARE MODE on replica is one of the few things which actually differentiate between a migration on master and on replica.

In the case of writer and replicas using row-based-replication, I don't think anything interesting would happen because only the changes are communicated to the replica. The only potential interesting case would be when writer is using (and replicating) a statement-based binlog, so the replica would re-run the statement including the lock.

But even then, would this only be a potential problem in a multithreaded replication environment? (Unfortunately I don't know much about them). Otherwise replication defaults to a single thread and there's nothing for the lock to protect against. Either the statement replicating is the one using the lock or it's not, but only one statement runs at a time.

Edit: That doesn't take in account replica-only tests. That's a case where multiple DML statements would run at once on a replica.

[YinggangZhang]

In my opinon. lock in share mode is necessary for consistency. Suppose the follow scene:

In the transaction commit phase of MySQL, it will serially executed the following things:

      t1:  innodb trx is set to prepared status;
      t2:  BINLOG commit
      t3:  innodb trx is set to commited status;

The binlog is ready to be dump after t2. So the replica( can be gh-ost) can dump and apply the BINLOG. However, in MASTER, if we use READ-COMMIT isolation, Only after t3, other session can 'see' the changes caused by this transaction.

As we know, for gh-ost, copying data and applying BINLOG occurs alternately. So, the follow case will cause data inconsistent if we don't use IN share mode. For clarity, let me consider the following case: If this is an innodb table with the following data:

      a b
      1 1
      2 2
      3 3

At one moment, we already copy (1,1) and (2,2) to temp table. and we are trying to apply binlog. At this moment, Master has a transaction which trying to delete (3,3). This transaction is currently between t2 and t3. Meanwhile, gh-ost can ‘see’ this operation in BINLOG, And there is no (3,3) in temp table. So it ignores this DELETE operation. Then, gh-ost trying to copy data, the DELETE transaction is still not fully commit，So gh-ost still can find (3,3). Then temp table will has (3,3) record. Howerver, Master does deletd (3,3) after t3. So the data in temp table is wrong.

If we use lock in share mode, there will be a lock waiting if we select (3,3) between t2 and t3. So the data is still right in temp table.