Movatterモバイル変換

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

  Note

  SELECT ... FOR SHARE is a replacement forSELECT ... LOCK IN SHARE MODE, butLOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent. However,FOR SHARE supportsOFtable_name,NOWAIT, andSKIP LOCKED options. SeeLocking Read Concurrency with NOWAIT and SKIP LOCKED.

  SELECT ... FOR SHARE requires theSELECT privilege.

  SELECT ... FOR SHARE statements do not acquire read locks on MySQL grant tables. For more information, seeGrant 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 anUPDATE statement for those rows. Other transactions are blocked from updating those rows, from doingSELECT ... 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 applyingundo logs on an in-memory copy of the record.)

  SELECT ... FOR UPDATE requires theSELECT privilege and at least one of theDELETE,LOCK TABLES, orUPDATE privileges.

Locking reads are only possible when autocommit is disabled (either by beginning transaction withSTART TRANSACTION or by settingautocommit to 0.

Suppose that you want to insert a new row into a tablechild, and make sure that the child row has a parent row in tableparent. Your application code can ensure referential integrity throughout this sequence of operations.

First, use a consistent read to query the tablePARENT and verify that the parent row exists. Can you safely insert the child row to tableCHILD? No, because some other session could delete the parent row in the moment between yourSELECT and yourINSERT, without you being aware of it.

To avoid this potential issue, perform theSELECT usingFOR SHARE:

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

After theFOR SHARE query returns the parent'Jones', you can safely add the child record to theCHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in thePARENT 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 tableCHILD_CODES, used to assign a unique identifier to each child added to tableCHILD. Do not use either consistent read or a shared mode read to 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 theCHILD 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 usingFOR 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;

ASELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQLUPDATE would set on the rows.

The preceding description is merely an example of howSELECT ... 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:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);SELECT LAST_INSERT_ID();

TheSELECT 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, aSELECT ... FOR UPDATE orSELECT ... 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 andSKIP LOCKED options may be used withSELECT ... FOR UPDATE orSELECT ... FOR SHARE locking read statements.

NOWAIT andSKIP LOCKED only apply to row-level locks.

Statements that useNOWAIT orSKIP LOCKED are unsafe for statement based replication.

The following example demonstratesNOWAIT andSKIP LOCKED. Session 1 starts a transaction that takes a row lock on a single record. Session 2 attempts a locking read on the same record using theNOWAIT option. Because the requested row is locked by Session 1, the locking read returns immediately with an error. In Session 3, the locking read withSKIP LOCKED returns the requested rows except for the row that is locked by Session 1.

# Session 1:mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;mysql> INSERT INTO t (i) VALUES(1),(2),(3);mysql> START TRANSACTION;mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;+---+| i |+---+| 2 |+---+# Session 2:mysql> START TRANSACTION;mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;ERROR 3572 (HY000): Do not wait for lock.# Session 3:mysql> START TRANSACTION;mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;+---+| i |+---+| 1 || 3 |+---+