How mysql Row level Lock works
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!
