Read lock

If you query data and then insert or update related data in the same transaction, the regular SELECT statement does not provide sufficient protection. Other transactions can update or delete the same row that was just queried. InnoDB supports two types of lock reads that provide additional security:

  • SELECT ... LOCK IN SHARE MODE

    Sets shared mode locking on any rows read. Other sessions can read rows, but they cannot be modified until the transaction commits. If any of these rows is changed by another transaction that has not yet committed, the query will wait until the transaction ends and then use the latest value.

  • SELECT ... FOR UPDATE

    In the case of index records, the search encounters locking rows and any associated index entries just as you would issue a statement on those rows if you UPDATE them. Other transactions blocked SELECT… LOCK IN SHARE MODE updates these rows to perform operations or read data at certain transaction isolation levels. A consistent read ignores any locks set on records that exist in the read view. (Older versions of records cannot be locked; You can reconstruct them by applying undo logging on an in-memory copy of the record.

These clauses are most useful when working with a single table or tree – or graph-structured data that spans multiple tables. You traverse the edge or branch from point to point, reserving the right to return and change any of these “pointer” values.

All locks FOR UPDATE set by LOCK IN SHARE MODE and are released when a transaction is committed or rolled back.

Pay attention to

Lock reads can only be performed when autocommit is disabled (START TRANSACTION starts transactions at 0 via autocommit or is set to 0).

A lock read clause in an external statement does not lock rows of a table in a nested subquery unless it is also specified in the subquery. For example, the following statement does not lock row T2 in the table.

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;
Copy the code

To lock rows in the table, t2 adds a locked read clause to the subquery:

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
Copy the code
Lock reading examples

Suppose you want to insert a new row child into the table and make sure that the child row has a parent row in the table. Your application code ensures referential integrity throughout the sequence of operations.

First, a consistent read is used to query the PARENT table and verify that the PARENT row exists. Can you safely insert CHILD rows into the table CHILD? No, because some other session may delete the parent row INSERT at the moment between your SELECT and you without you realizing it.

To avoid this potential problem, use LOCK IN SHARE MODE by executing the following SELECT:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Copy the code

After the LOCK IN SHARE MODE query returns the parent ‘Jones’, you can safely add the CHILD record to the CHILD table and commit the transaction. Any transaction that attempts to acquire an exclusive lock on the applicable row in the PARENT table will wait until you have completed the operation (that is, the data in all tables is in a consistent state).

As another example, consider an integer counter field in a table that CHILD_CODES is used to assign a unique identifier CHILD to each CHILD added to the table. Do not use consistent reads or shared mode reads to read the current value of the counter, because two users of the database may see the same value for that counter, and a duplicate key error will occur if two transactions try to add rows using the same identifier as the CHILD table.

IN this case, LOCK IN SHARE MODE is not a good solution, because if two users read the counter at the same time, at least one of them will be deadlocked when trying to update the counter.

To implement read and increment counters, use lock read FOR UPDATE first and then increment the counter. Such as:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
Copy the code

A SELECT … FOR UPDATE reads the latest available data and sets an exclusive lock on each row read. Therefore, it sets the same lock as the lock set on the search SQL UPDATE line.

The previous description is just an example of how this works SELECT… FOR UPDATE. In MySQL, the specific task of generating unique identifiers can actually be done with a single access to the table:

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

This SELECT statement retrieves only identifier information (specific to the current connection). It does not access any tables.

More content welcome to pay attention to my personal public number “Han Elder brother has words”, 100G artificial intelligence learning materials, a large number of back-end learning materials waiting for you to take.