mysql行级锁实现道理有哪些
mysql行级锁实现道理:1、InnoDB行锁是通过给索引项加锁来实现的,这一点mysql和 oracle不一样;2、InnoDB这种行级锁决议,只要通过索引前提来检索数据,才干运用行级锁,不然, 直接运用表级锁。
mysql行级锁实现道理:
锁是在施行多线程时用于强行限制资源拜访的同步机制,数据库锁依据锁的粒度可分为行级锁, 表级锁和页级锁
行级锁
行级锁是mysql中粒度最细的一种锁机制,表示只对目前所操纵的行进行加锁,行级锁产生冲突 的概率很低,其粒度最小,但是加锁的代价最大。行级锁分为同享锁和排他锁。
特色:
开销大,加锁慢,会涌现死锁;锁定粒度最小,产生锁冲突的概率最大,并发性也高;
实现道理:
InnoDB行锁是通过给索引项加锁来实现的,这一点mysql和oracle不一样,后者是通过在数据库中 对响应的数据行加锁来实现的,InnoDB这种行级锁决议,只要通过索引前提来检索数据,才干运用行 级锁,不然,直接运用表级锁。
特殊注意: 运用行级锁一定要运用索引
举个栗子:
新建表构造
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 INTO `developerinfo` VALUES ('1', 'liujie', '123456'); INSERT INTO `developerinfo` VALUES ('2', 'yitong', '123'); INSERT INTO `developerinfo` VALUES ('3', 'tong', '123456');
(1)通过主键索引来查询数据库运用行锁
打开三个下令行窗口进行测试
下令行窗口1 | 下令行窗口2 | 下令行窗口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; 期待 | 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)查询非索引的字段来查询数据库运用行锁
打开两个下令行窗口进行测试
下令行窗口1 | 下令行窗口2 |
mysql> 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 set | mysql> set
autocommit=0; Query OK, 0 rows affected mysql> select * from developerinfo where name = 'tong' for update; 期待 |
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)查询非独一索引字段来查询数据库运用行锁锁住多行
mysql的行锁是针对索引假的锁,不是针对记载,所以可能会涌现锁住不一样记载的场景
打开三个下令行窗口进行测试
下令行窗口1 | 下令行窗口2 | 下令行窗口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; 期待 | 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)前提中运用索引来操纵检索数据库时,可否运用索引还需有mysql通过推断不一样施行规划来
决议,可否运用该索引,如需断定怎样运用explain来推断索引,请听下回分解
更多相干免费学习举荐:mysql教程(视频)
以上就是mysql行级锁实现道理有哪些的细致内容,更多请关注 百分百源码网 其它相干文章!