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

How to implement row-level locking in MySQL? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Preface

Lock is a synchronization mechanism used to forcibly restrict resource access when multithreading is executed. according to the granularity of lock, database lock can be divided into row-level lock, table-level lock and page-level lock.

Row level lock

Row-level lock is the finest-grained locking mechanism in mysql, which means that only the rows currently operated are locked. The probability of row-level lock conflict is very low, and its granularity is the smallest, but the cost of locking is the highest. Row-level locks are divided into shared locks and exclusive locks.

Features:

High cost, slow locking, deadlock will occur; lock granularity is the smallest, lock conflict probability is the highest, and concurrency is also high.

The principle of implementation:

InnoDB row locking is achieved by locking index items, which is different from oracle, which is achieved by locking the corresponding data rows in the database. InnoDB, a row-level lock, determines that row-level locks can be used only if the data is retrieved by index conditions, otherwise, table-level locks are used directly. Special note: indexes must be used when using row-level locks

Take a chestnut:

Create a table structure

CREATE TABLE `developerinfo` (`userID` bigint (20) NOT NULL, `name` varchar (255) DEFAULT NULL, `passWord` varchar (255) DEFAULT NULL, PRIMARY KEY (`userID`), KEY `userID` (`passWord`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data

INSERT INTO `developerinfo` VALUES ('1th,' liujie', '123456'); INSERT INTO `developerinfo` VALUES ('2months,' yitong', '123'); INSERT INTO `developerinfo` VALUES (' 3months, 'tong',' 123456')

(1) query the database using row locks through the primary key index

Open three command line windows to test

Command line window 1 command line window 2 command line window 3mysql > set autocommit = 0 native query OK, 0 rows affectedmysql > select * from developerinfo where userid ='1' for update +-+ | userID | name | passWord | +-+ | 1 | liujie | 123456 | +-+ 1 row in set | mysql > set autocommit = 0everything query OK, 0 rows affectedmysql > select * from developerinfo where userid ='1' for update Wait | mysql > set autocommit = 0bot query OK, 0 rows affectedmysql > select * from developerinfo where userid ='3' for update;+-+ | userID | name | passWord | +-+ | 3 | tong | 123456 | +-+ 1 row in set | mysql > commit Query OK, 0 rows affected mysql > select * from developerinfo where userid ='1' for update;+-+ | userID | name | passWord | +-+ | 1 | liujie | 123456 | +-+ 1 row in set

(2) query non-indexed fields to query the database using row locks

Open two command line windows to test

Command line window 1 Command line window 2 | mysql > set autocommit=0;Query OK, 0 rows affectedmysql > select * from developerinfo where name = 'liujie' for update +-+ userID name passWord+-+1 liujie 123456copyright copyright 1 row in set | mysql > set autocommit=0;Query OK, 0 rows affectedmysql > select * from developerinfo where name = 'tong' for update; wait | mysql > commit Query OK, 0 rows affected mysql > select * from developerinfo where name = 'liujie' for update;+-+ | userID | name | passWord | +-+ | 1 | liujie | 123456 | +-+ 1 row in set

# (3) query non-unique index fields to query the database to lock multiple rows using row locks

Mysql's row lock is a lock for index fake, not for records, so there may be scenarios in which different records are locked

Open three command line windows to test

Command line window 1 command line window 2 command line window 3

Mysql > set autocommit=0;Query OK, 0 rows affectedmysql > select * from developerinfo where password = '123456' for update +-+ | userID | name | passWord | +-+ | 1 | liujie | 123456 | | 3 | tong | 123456 | +-+ 2 rows in set mysql > set autocommit = 0 Query OK, 0 rows affectedmysql > select * from developerinfo where userid ='1' for update

wait for

Mysql > set autocommit = 0bot query OK, 0 rows affectedmysql > select * from developerinfo where userid ='2' for update;+-+ | userID | name | passWord | +-+ | 2 | yitong | 123 | +-+ 1 row in setcommit Mysql > select * from developerinfo where userid ='1' for update;+-+ | userID | name | passWord | +-+ | 1 | liujie | 123456 | +-+ 1 row in set

# (4) when the index is used in the condition to operate and retrieve the database, it is necessary for mysql to decide whether to use the index by judging different execution plans. If you need to decide how to use the index to judge the index, please listen to the next decomposition.

This is the answer to the question about how to achieve row-level locking in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.