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

This article is to share with you about how to achieve pseudo-row-level locks in MYSQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

one。 Data preparation

Mysql > use test

Database changed

Mysql > show create table t_kenyon\ G

* * 1. Row *

Table: t_kenyon

Create Table: CREATE TABLE `t_ kenyon` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Www.2cto.com

Mysql > show variables like'% autocommit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from t_kenyon

+-+

| | id |

+-+

| | 1 |

| | 123 |

| | 789 |

| | 345 |

| | 78 |

| | 78 |

+-+

6 rows in set (0.00 sec)

The above is the test table t_kenyon, and the submission method is set to manual submission.

two。 Procedure (open two session and set autocommit=off respectively)

Www.2cto.com

1.session one update

Mysql > update t_kenyon set id = 999 where id = 1

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from t_kenyon

+-+

| | id |

+-+

| | 999 |

| | 123 |

| | 789 |

| | 345 |

| | 78 |

| | 78 |

+-+

6 rows in set (0.00 sec)

2.session two update

Mysql > show variables like 'autocommit'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

+-+ +

1 row in set (0.00 sec)

Www.2cto.com

Mysql > select * from t_kenyon

+-+

| | id |

+-+

| | 1 |

| | 123 |

| | 789 |

| | 345 |

| | 78 |

| | 78 |

+-+

6 rows in set (0.00 sec)

Mysql > update t_kenyon set id = 88888 where id = 345

The value of the second session update is 345, but it is also blocked until session1 is rollback or commit. If session1 does not rollback or commit, the blocking in session2 automatically rolls back when the lock time limit of mysql is exceeded. This parameter is innodb_lock_wait_timeout. The default 50-second phenomenon is as follows.

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

Indexed test

3.session one update

Mysql > create index ind_kenyon on t_kenyon (id)

Query OK, 0 rows affected (28.58 sec)

Records: 0 Duplicates: 0 Warnings: 0

Www.2cto.com

Mysql > update t_kenyon set id = 999 where id = 1

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from t_kenyon

+-+

| | id |

+-+

| | 78 |

| | 78 |

| | 123 |

| | 345 |

| | 789 |

| | 999 |

+-+

6 rows in set (0.00 sec)

4.session two update

Mysql > select * from t_kenyon

+-+

| | id |

+-+

| | 1 |

| | 78 |

| | 78 |

| | 123 |

| | 345 |

| | 789 |

+-+

6 rows in set (0.00 sec)

Www.2cto.com

Mysql > update t_kenyon set id = 7777 where id = 345

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from t_kenyon

+-+

| | id |

+-+

| | 1 |

| | 78 |

| | 78 |

| | 123 |

| | 789 |

| | 7777 |

+-+

6 rows in set (0.00 sec)

Carry out the plan

Mysql > explain select * from t_kenyon where id = 345\ G

* * 1. Row *

Id: 1 www.2cto.com

Select_type: SIMPLE

Table: t_kenyon

Type: ref

Possible_keys: ind_kenyon

Key: ind_kenyon

Key_len: 5

Ref: const

Rows: 1

Extra: Using where; Using index

1 row in set (0.00 sec)

You can see that after indexing, different data updates are not blocked, and the real row lock is realized.

The above is how to achieve pseudo-row-level locks in MYSQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow 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.