打开三个命令行窗口进行测试
| 命令行窗口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行级锁实现原理是什么的详细内容，更多文章请关注木庄网络博客！
相关阅读 >>
介绍mysql数据库的预处理（prepared statement）性能测试
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
