What is the implementation principle of mysql row level lock?

mysql row-level lock implementation principle: 1. InnoDB row lock is achieved by locking index items. This is the difference between mysql and Oracle is different; 2. InnoDB determines row-level locks. Row-level locks can only be used to retrieve data through index conditions. Otherwise, Use table-level locks directly.

mysql row-level lock implementation principle:

The lock is used to forcibly limit resource access when executing multi-threads Synchronization mechanism, database locks can be divided into row-level locks according to the lock granularity. Table-level locks and page-level locks

Row-level locks

Row-level locks are the most fine-grained locking mechanism in mysql, which means that only the currently operated The row is locked and a row-level lock conflict occurs. The probability is very low, 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:

The overhead is large, locking is slow, and deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the highest, and the concurrency is also high;

Implementation principle:

InnoDB row lock is implemented by locking index items. This is different from mysql and oracle. The latter is achieved by locking index items in the database. This is achieved by locking the corresponding data rows. InnoDB's row-level lock determines that rows can only be used to retrieve data through index conditions. Level lock, otherwise, use table-level lock directly.

pay attention: When using row-level locks, you must use indexes

For example:

Create table structure

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

Insert data

INSERT INTO `developerinfo` VALUES ('1', 'liujie', 

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

'123456');

(1) Query the database through the primary key index and use row locks

Open three command line windows for testing

Command line window 1	Command line window 2	Command line window 3
##mysql> 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 set	mysql> set autocommit = 0; Query OK, 0 rows affected mysql> select * from developerinfo where userid = '1' for update; wait	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 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 for testing

##mysql> set autocommit=0;##mysql> commit;Query OK, 0 rows affected -------- -------- -------- ----------

(3) Query non-unique index fields to query the database and use row locks to lock multiple rows

Mysql's row lock is a fake lock for the index, not for the record, so different records may be locked. Scenario

Open three command line windows for testing

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

Command line window 1	Command line window 2	Command line window 3
mysql> 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 set	mysql> set autocommit =0 ; Query OK, 0 rows affected mysql> select * from developerinfo where userid = '1' for update; Waiting	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 set
commit ;	mysql> select * from developerinfo where userid = '1' for update; -------- -------- -------- ---------- | userID | name | passWord | ------ -- -------- ---------- | 1 | liujie | 123456 | -------- -------- - --------- 1 row in set

(4) Index is used in the condition to operate the retrieval When using a database, whether to use an index requires mysql to determine different execution plans. Decide whether to use the index. If you need to determine how to use explain to determine the index, please listen to the next chapter for decomposition

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of What is the implementation principle of mysql row level lock?. For more information, please follow other related articles on the PHP Chinese website!