Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is Innodb lock reading". In daily operation, I believe many people have doubts about what Innodb lock reading is. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the questions of "what is Innodb lock reading"! Next, please follow the editor to study!

If you query data and then insert or modify related data in the same transaction, regular select statements do not protect the data from being modified by other transactions. Other transactions can update or delete the same row in the query transaction. InnoDB supports two types of locked reads:

1 、 SELECT... LOCK IN SHARE MODE sets a shared S to lock on the row being queried. Other sessions can read these rows, but cannot modify the data of the corresponding rows until the end of the transaction. If another transaction DML the corresponding row before ending the transaction, then SELECT is performed in the current transaction. LOCK IN SHARE MODE will wait until another transaction is completed.

2 、 SELECT... FOR UPDATE sets an exclusive X to lock on the corresponding line. When select for update queries are made by index, the relevant index entries and row data are locked. Similarly, during this period, other transactions update the relevant rows, SELECT. LOCK IN SHARE MODE, reading data at some transaction isolation levels will be blocked, but consistent reads ignore any locks that exist, because older versions of data cannot be locked, and they construct copies of the data in memory through undo log.

Note: when using a lock read operation, the transaction must be opened (the transaction can be opened through START TRANSACTION or set autocommit=0). The lock related to the read will be released immediately when the transaction commit or rollback. If no transaction is opened, the related rows are not locked.

Examples of usage

If you want to insert a record into a child table, you must first confirm whether the parent table has a related record before inserting. Only when the parent table has a corresponding record can the integrity constraint of the application data be satisfied. If you use consistent unlocked reads to check the corresponding records of the parent table, and at the moment the corresponding data is inserted into the child table, the transactions of other sessions delete or modify the rows you have just checked before inserting the child table after you query the parent table. in this way, the next insert operation may not be completed successfully.

You can use LOCK IN SHARE MODE for locked reads to avoid this potential problem. As follows:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE

Use LOCK IN SHARE MODE to query to jones in the parent table, and you can safely insert jones-related data into the child table. During this period, if there are other session transaction views, the corresponding rows in the DML parent table will be blocked until you finish locking the read transaction after your operation is completed, which can ensure the consistency of the data of the parent table and the child table.

In another scenario, if there are two session that need to read a row in table A, update the row in the same transaction after a successful read, and insert the row that was initially read in another table B. If you use SELECT...LOCK IN SHARE MODE at this time, you will add an S lock to the record read to table A, and a deadlock occurs when two session apply for an X lock to update at the same time. In addition, because the same row of table An is read, two session will cause the error of key duplication when inserting data into the same table B. It is not a good idea to use LOCK IN SHARE MODE in this case, but it is more appropriate to use SELECT...FOR UPDATE to block read and update requests for other things while reading.

At this point, the study of "what is Innodb locked reading" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.