InnoDB Transaction Model From MySQL 8.0.22,  SELECT ... FOR SHARE  statements do not acquire read locks on MySQL grant tables. For more information, see Grant Table Concurrency. • SELECT ... FOR UPDATE For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an  UPDATE  statement for those rows. Other transactions are blocked from updating those rows, from doing  SELECT ... FOR SHARE , or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.) SELECT ... FOR UPDATE  requires the  SELECT  privilege and at least one of the  DELETE ,  LOCK TABLES , or  UPDATE  privileges. These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables. You traverse edges or tree branches from one place to another, while reserving the right to come back and change any of these “pointer”  values. All locks set by  FOR SHARE  and  FOR UPDATE  queries are released when the transaction is committed or rolled back. Note Locking reads are only possible when autocommit is disabled (either by beginning transaction with  START TRANSACTION  or by setting  autocommit  to 0. A locking read clause in an outer statement does not lock the rows of a table in a nested subquery unless a locking read clause is also specified in the subquery. For example, the following statement does not lock rows in table  t2 . Suppose that you want to insert a new row into a table  child , and make sure that the child row has a parent row in table  parent . Your application code can ensure referential integrity throughout this sequence of operations. Locking Read Examples First, use a consistent read to query the table  PARENT  and verify that the parent row exists. Can you safely insert the child row to table  CHILD ? No, because some other session could delete the parent row in the moment between your  SELECT  and your  INSERT , without you being aware of it. To lock rows in table  t2 , add a locking read clause to the subquery: SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE; SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE; SELECT * FROM parent WHERE NAME = ’Jones’ FOR SHARE; To avoid this potential issue, perform the  SELECT  using  FOR SHARE : After the  FOR SHARE  query returns the parent  ’Jones’ , you can safely add the child record to the  CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the  PARENT  table waits until you are finished, that is, until the data in all tables is in a consistent state. For another example, consider an integer counter field in a table  CHILD_CODES , used to assign a unique identifier to each child added to table  CHILD . Do not use either consistent read or a shared mode read to 3092