InnoDB Transaction Model SELECT * FROM t; --------------------- | 1 | 2 | --------------------- SELECT * FROM t FOR SHARE; 3091 Prior to MySQL 8.0.22,  SELECT ... FOR SHARE  requires the  SELECT  privilege and at least one of the  DELETE ,  LOCK TABLES , or  UPDATE  privileges. From MySQL 8.0.22, only the  SELECT  privilege is required. SELECT ... FOR SHARE  is a replacement for  SELECT ... LOCK IN SHARE MODE , but  LOCK IN SHARE MODE  remains available for backward compatibility. The statements are equivalent. However,  FOR SHARE  supports OF  table_name ,  NOWAIT , and  SKIP LOCKED  options. See Locking Read Concurrency with NOWAIT and SKIP LOCKED. Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values. If you query data and then insert or update related data within the same transaction, the regular  SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried.  InnoDB  supports two types of locking reads that offer extra safety: The type of read varies for selects in clauses like  INSERT INTO ... SELECT ,  UPDATE ... (SELECT) , and  CREATE TABLE ... SELECT  that do not specify  FOR UPDATE  or  FOR SHARE : Consistent read does not work over certain DDL statements: With  READ COMMITTED  isolation level, each consistent read within a transaction sets and reads its own fresh snapshot. With  FOR SHARE , a locking read occurs instead: A  SELECT  blocks until the transaction containing the freshest rows ends (see Section 15.7.2.4, “Locking Reads” ). If you want to see the “freshest”  state of the database, use either the  READ COMMITTED  isolation level or a locking read: •Consistent read does not work over  DROP TABLE , because MySQL cannot use a table that has been dropped and  InnoDB  destroys the table. •Consistent read does not work over  ALTER TABLE  operations that make a temporary copy of the original table and delete the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction’s snapshot was taken. In this case, the transaction returns an error: ER_TABLE_DEF_CHANGED , “Table definition has changed, please retry transaction” . •By default,  InnoDB  uses stronger locks for those statements and the  SELECT  part acts like  READ COMMITTED , where each consistent read, even within the same transaction, sets and reads its own fresh snapshot. •To perform a nonlocking read in such cases, set the isolation level of the transaction to  READ UNCOMMITTED  or  READ COMMITTED  to avoid setting locks on rows read from the selected table. 15.7.2.4 Locking Reads • SELECT ... FOR SHARE Note