Shulou(Shulou.com)06/01 Report--

Editor to share with you the working principle of mysql row-level locks, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

Mysql row-level lock implementation principle: 1, InnoDB row lock is achieved by locking index items, which is different from mysql and oracle; 2, InnoDB row-level lock determines that only through the index condition to retrieve data, can use row-level lock, otherwise, directly use table-level lock.

The implementation principle of mysql row-level lock:

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

Query OK, 0 rows affected

Mysql > select * from developerinfo where userid ='1' for update

+-+

| | userID | name | passWord | |

+-+

| | 1 | liujie | 123456 | |

+-+

1 row in setmysql > set autocommit = 0

Query OK, 0 rows affected

Mysql > select * from developerinfo where userid ='1' for update

wait for

Mysql > set autocommit = 0

Query OK, 0 rows affected

Mysql > select * from developerinfo where userid ='3' for update

+-+

| | userID | name | passWord | |

+-+

| | 3 | tong | 123456 | |

+-+

1 row in setmysql > commit

Query OK, 0 rows affectedmysql > 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 2mysql > set autocommit=0

Query OK, 0 rows affected

Mysql > select * from developerinfo where name = 'liujie' for update

+-+

| | userID | name | passWord | |

+-+

| | 1 | liujie | 123456 | |

+-+

1 row in setmysql > set autocommit=0

Query OK, 0 rows affected

Mysql > select * from developerinfo where name = 'tong' for update

wait for

Mysql > commit

Query OK, 0 rows affectedmysql > 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 3mysql > set autocommit=0

Query OK, 0 rows affected

Mysql > select * from developerinfo where password = '123456

'for update

+-+

| | userID | name | passWord | |

+-+

| | 1 | liujie | 123456 | |

| | 3 | tong | 123456 | |

+-+

2 rows in setmysql > set autocommit = 0

Query OK, 0 rows affected

Mysql > select * from developerinfo where userid ='1' for update

wait for

Mysql > set autocommit = 0

Query OK, 0 rows affected

Mysql > 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

These are all the contents of how mysql row-level locks work. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to 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.