Read lock for Mysql InnoDB engine

MySQL official manual read lock instructions

If, in the same transaction, you query the data and insert/Update data related to this data, the usual SELECT statement will not give us enough protection. Because in the time period between the Select and update of our current transaction, other transactions may be updated/ Delete the row we just read. And we don't even notice.

InnoDB supports two types of read locks that provide sufficient security for us.

1.SELECT ... LOCK IN SHARE MODE 

这是共享锁，在读取的任何数据上设定一个共享模式的锁.其他事务可以读取这些数据.但是不能修改这些数据,除非我们设定锁的事务提交了.如果我们查询的数据正被其他事务修改,而且这些事务还没有提交,那么我们的查询会一直等待,直到这些事务提交,然后我们的查询就使用这些最新的数据.

Note: To correctly use Select ... For Update/select ... LOCK in SHARE MODE, you must turn off autocommit transactions (set autocommit = 0), or manually open a transaction (Begin/start transaction), and if the transaction is not turned on, the data that is queried will not be locking

2.SELECT ..... For UPDATE;

This is an exclusive lock that blocks other transactions that are trying to modify, using Select .... LOCK in SHARE mode transactions and some transaction isolation level transactions (such as for UPDATE).

Example Select ... For update, this method reads the latest results

In the first case, transaction 1 opens the transaction and queries a record and then executes the subsequent action,

Transaction 1 opens a transaction and queries a record

Note that transaction 1 is not committed, and transaction 2 attempts to update this record at this time

However, since transaction 1 has not yet been committed, the update for transaction 2 waits, and at this point, query this record again with transaction 1 to see if the record has been changed

It can be found that the password has not changed.

Then, transaction 1 waited a moment, and finally submitted the

At this time to see the transaction 2, because the transaction 1commit after releasing the lock, transaction 2 got the lock, immediately returned the result:

Then I query this record, and found that finally updated to 333

In a business scenario, if two transactions are using lock in share mode, then the update operation is performed. If two transactions have acquired a shared lock, before releasing the lock (commit or roll back) to perform the update, because the update operation needs to wait for the other party to release the lock, resulting in a deadlock (deadlock), which is not allowed by the business, so the general use for Update lets the transaction acquire an exclusive lock, and the transaction is completed before another transaction is allowed to acquire the lock.

Attention:

1. If transaction 1 is turned on, transaction 1 executes select (not yet committed) in transaction 1, transaction 2 modifies the data and commits, and even if transaction 1 executes select again, transaction 1 does not check for changes made in transaction 2. Unless you reopen the transaction.

2. A shared/exclusive lock is provided that the other transaction has already released the exclusive lock

3. About Autocommit

Because of the transaction involved, so InnoDB to automatically submit this said. Although the use of set autocommit in the MyISAM engine does not make an error, the use of this statement is meaningless because there is no transactional capability.

In InnoDB , all user behavior occurs in a transaction , and if autocommit allows each SQL statement to form a separate transaction on its own, MySQL always starts a new connection with the Allow autocommit.

However, if autocommit mode is turned off with "SET autocommit = 0", then we can assume that a user always has a transaction open.

A "commit" or "ROLLBACK" statement ends the current transaction and begins a new transaction, and a "commit" statement means that the changes made in the current transaction are generated permanently and become visible to other users. A "ROLLBACK" is a modification that revokes all current transactions.

Read lock for Mysql InnoDB engine