InnoDB Transaction Model read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the  CHILD  table. Here,  FOR SHARE  is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter. To implement reading and incrementing the counter, first perform a locking read of the counter using  FOR UPDATE , and then increment the counter. For example: SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1; A  SELECT ... FOR UPDATE  reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL  UPDATE  would set on the rows. The preceding description is merely an example of how  SELECT ... FOR UPDATE  works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table: The  SELECT  statement merely retrieves the identifier information (specific to the current connection). It does not access any table. If a row is locked by a transaction, a  SELECT ... FOR UPDATE  or  SELECT ... FOR SHARE  transaction that requests the same locked row must wait until the blocking transaction releases the row lock. This behavior prevents transactions from updating or deleting rows that are queried for updates by other transactions. However, waiting for a row lock to be released is not necessary if you want the query to return immediately when a requested row is locked, or if excluding locked rows from the result set is acceptable. To avoid waiting for other transactions to release row locks,  NOWAIT  and  SKIP LOCKED  options may be used with  SELECT ... FOR UPDATE  or  SELECT ... FOR SHARE  locking read statements. A locking read that uses  NOWAIT  never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked. A locking read that uses  SKIP LOCKED  never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set. • NOWAIT • SKIP LOCKED Locking Read Concurrency with NOWAIT and SKIP LOCKED UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1); SELECT LAST_INSERT_ID(); Note Queries that skip locked rows return an inconsistent view of the data.  SKIP LOCKED  is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue- like table. NOWAIT  and  SKIP LOCKED  only apply to row-level locks. Statements that use  NOWAIT  or  SKIP LOCKED  are unsafe for statement based replication. 3093