content_image/827110.jpg
SELECT * FROM t; --------------------- | 1 | 2 | --------------------- 

If you want to see the “freshest”  state of the database, use either the READ COMMITTED  isolation level or a locking read: 

SELECT * FROM t FOR SHARE; 

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” ). 

Consistent read does not work over certain DDL statements: 

•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” . 

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 : 

•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 

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: 

• SELECT ... FOR SHARE 

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. 

<img src='content_image/827110.jpg'>

## Note 

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. 

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. 

