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

MySQL 5.7 SELECT is a MySQL 5.7 SELECT . LOCK IN SHARE MODE| What is the description of the FOR UPDATE statement, the content is concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

In the same transaction, if you want to insert or update related data after querying the data, the regular SELECT statement does not provide sufficient protection.

Other transactions can update or delete the rows you query. The InnoDB engine supports two types of read locks to provide additional security.

I. SELECT... LOCK IN SHARE MODE Sets the shared lock on the queried row. Other replies can read these lines, but cannot modify them until after the transaction is committed.

If LOCK IN SHARE MODE is used on a storage engine that supports page or row locks, the rows covered by the query are subject to shared locks that allow other transactions to read, but do not allow other transactions to update or delete the rows.

View the data in the test table. There is only one field in the table, and the field is the primary key.

mysql> desc t5;

+-------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| id | tinyint(3) unsigned | NO | PRI | NULL | |

+-------+---------------------+------+-----+---------+-------+

1 row in set (0.01 sec)

mysql> show create table t5;

+-------+----------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+----------------------------------------------------------------------------------------------------------------------+

| t5 | CREATE TABLE `t5` (

`id` tinyint(3) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+----------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from t5;

+----+

| id |

+----+

| 10 |

| 20 |

| 30 |

| 40 |

| 50 |

+----+

5 rows in set (0.00 sec)

Session 1 opens a transaction and adds a shared lock to data with id ranging from 10 to 30

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t5 where id >= 10 and id start transaction;

Query OK, 0 rows affected (0.00 sec)

+----+

| 10 |

| 20 |

| 30 |

| 40 |

| 50 |

| 60 |

+----+

6 rows in set (0.00 sec)

mysql> insert into t5 values(60);

Query OK, 1 row affected (0.04 sec)

mysql> update t5 set id=100 where id=10;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The length of time an InnDB transaction waits for a row lock. The default value is 50 seconds.

mysql> show variables like 'innodb_lock_wait_timeout';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 50 |

+--------------------------+-------+

1 row in set (0.01 sec)

Session 2, Deleting locked rows from Session 1 blocks

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from t5 where id=30;

Query OK, 1 row affected (15.31 sec)

Session 1, commit transaction

mysql> commit;

Query OK, 0 rows affected (0.06 sec)

Session 2, the previously blocked REMOTE operation takes effect

mysql> delete from t5 where id=30;

Query OK, 1 row affected (15.31 sec)

mysql> rollback;

Query OK, 0 rows affected (0.02 sec)

If FOR UPDATE is used on a storage engine that supports page or row locks, the rows covered by the query are write-locked until the end of the current transaction.

Other replies can read these lines, but cannot modify them until after the transaction is committed.

Session 1 opens a transaction and adds a write lock to data with id in the range 10 to 30

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t5 where id >= 10 and id start transaction;

Query OK, 0 rows affected (0.00 sec)

+----+

| 10 |

| 20 |

| 30 |

| 40 |

| 50 |

| 60 |

+----+

6 rows in set (0.00 sec)

mysql> insert into t5 values(70);

Query OK, 1 row affected (0.04 sec)

mysql> update t5 set id=100 where id=10;

Session 1, commit transaction

mysql> commit;

Query OK, 0 rows affected (0.06 sec)

Session 2, previously blocked UPDATE operations will succeed

mysql> update t5 set id=100 where id=10;

Query OK, 1 row affected (15.38 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> rollback;

Query OK, 0 rows affected (0.04 sec)

MySQL 5.7 SELECT is a MySQL 5.7 SELECT . LOCK IN SHARE MODE| What is the description of the FOR UPDATE statement, and have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

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.