13518219792
本篇文章给大家分享的是有关MySQL中怎么实现伪行级锁，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
网站建设哪家好，找创新互联！专注于网页设计、网站建设、微信开发、成都小程序开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了沈丘免费建站欢迎大家使用！
一.数据准备
mysql> use test;
Database changed
mysql> show create table t_kenyon \G
*************************** 1. row ***************************
Table: t_kenyon
Create Table: CREATE TABLE `t_kenyon` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
www.2cto.com
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 123 |
| 789 |
| 345 |
| 78 |
| 78 |
+------+
6 rows in set (0.00 sec)
以上是测试表t_kenyon,设置提交方式为手动提交.
二.过程(开启两个session,分别设置autocommit=off)
www.2cto.com
1.session one update
mysql> update t_kenyon set id = 999 where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 999 |
| 123 |
| 789 |
| 345 |
| 78 |
| 78 |
+------+
6 rows in set (0.00 sec)
2.session two update
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
www.2cto.com
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 123 |
| 789 |
| 345 |
| 78 |
| 78 |
+------+
6 rows in set (0.00 sec)
mysql> update t_kenyon set id = 88888 where id = 345;
第二个session更新的值是345，但是也一直被阻塞，直到session1被rollback或者commit,如果session1未做回滚或者提交，session2中的该阻塞在超出mysql的锁时间限制时自动回滚,该参数为innodb_lock_wait_timeout，默认值50秒 现象如下
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
加索引后的测试
3.session one update
mysql> create index ind_kenyon on t_kenyon(id);
Query OK, 0 rows affected (28.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
www.2cto.com
mysql> update t_kenyon set id = 999 where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 78 |
| 78 |
| 123 |
| 345 |
| 789 |
| 999 |
+------+
6 rows in set (0.00 sec)
4.session two update
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 78 |
| 78 |
| 123 |
| 345 |
| 789 |
+------+
6 rows in set (0.00 sec)
www.2cto.com
mysql> update t_kenyon set id = 7777 where id = 345;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 78 |
| 78 |
| 123 |
| 789 |
| 7777 |
+------+
6 rows in set (0.00 sec)
执行计划
mysql> explain select * from t_kenyon where id = 345 \G
*************************** 1. row ***************************
id: 1 www.2cto.com
select_type: SIMPLE
table: t_kenyon
type: ref
possible_keys: ind_kenyon
key: ind_kenyon
key_len: 5
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
可以看到加了索引后，不同的数据更新并没有被阻塞，实现了真正意义上行锁
以上就是MYSQL中怎么实现伪行级锁，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注创新互联行业资讯频道。
