开发者学堂课程【MySQL 高级应用 - 索引和锁：行锁案例讲解】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/598/detail/8634
行锁案例讲解
目录：
一、建表 SQL
二、行锁定基本演示
一、建表 SQL
create table test_innodb_lock (a int(11),b varchar(16))engine =innodb;
insert into test_innodb_lock values(1,'b2);
insert into test_innodb_lock values(3,'3&apos');
insert into test_innodb_lock values(4,'4000&apos');
insert into test_innodb_lock values(5,'5000&apos');
insert into test_innodb_lock values(6,'6000&apos');
insert into test_innodb_lock values(7,7000&apos');
insert into test_innodb_lock values(8,'8000&apos');
insert into test_nnodb_lock values(9,'9000&apos');
insert into test_ innodb_lock values(1,b1&apos');
create index test_innodb_a_ind on test_innodb_ lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);
此时单独为此表的a字段单独建立了“test_innodb_a_ind”索引，单独此表 b 字段建立了“test_innodb_lock_b_ind”引擎，此为单值索引。此时建表完成。
二、行锁定基本演示
1.大纲
2.实操
（1）首先更新表 ssesion-1，进行一行的行锁
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | 5000 |
9 rows in set (0.00 sec )
mysq1>
（2）
mysq1>update test_innodb_ lock set b=' 4001' where a=4；
QueryOK，1 row affected(0.00 sec )
//回车得到
Rows matched :1 Changed :1 Warnings :0
mysq1>
在查询时，观察一下第4条记录是否为4001
故，首先应查看自己所写的数据
（3）将 session-1的 b 字段更改为4002
session-2：mysq1> update test_innodb_lock set b= '4003' where a=4;
此时 session-1未 commit，session-2即将 update，但 session-2出现阻塞，则只能在两方进行 commit 操作，即两方多次保证了 commit 的执行。
ssesion-1
+------+--------+
| a | b |
+------+--------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4002 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | 5000 |
+------+--------+
9 rows in set (0.00 sec )
mysq1>commit
Query oK, 0 rows affected (0.00 sec )
mysq1> commi;t
ERROR1 064( 42000): You have an error in your SQL syntax; checkthe manua1 that corresponds to your MySQL server
versionorthe right syntax to use nearcommi' at 1ine l一> ;
ERROR 1064 (42000): You have an error in your SQL syntax; checkthe manua1 that corresponds to your MySQL serverversionorthe right syntaxm to use near 't' at 1ine 1
mysq1> commit ;
Query OK, 0 rows affected (0.00 sec )
mysq1>
+------+--------+
| a | b |
+------+--------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4003 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+--------+
9 rows in set (0.00 sec )
ssesion-2
mysq1>select* feom test_innodb_lock;
+------+--------+
| a | b |
+------+--------+
| 1 | b2 |
| 3 | 3 |
| 4 |4003 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+--------+
9 rows in set (0.00 sec )
mysq1>
提交更新解除阻塞，之后的更新则正常进行
session-2
mysq1>select* feom test_innodb_lock;
//进行 select
+------+--------+
| a | b |
+------+--------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4003 |
| 5 | 5000 | //存在5号记录
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 | //存在9号记录
| 1 | b1 |
+------+--------+
9 rows in set (0.00 sec )
由于添加的为行锁，如若 session-1首先改变4号记录，session-2 再次改变4号记录将会造成堵塞。
如若 session-2改变9号记录，则两者互不相干。
mysq1> update test_innodb_lock set b= '9001' where a=9;
Query OK, 1 row affected (0.00 sec )
Rowsmatched:1 Changed:1 Warnings :0
session-1
mysq1> update test_innodb_lock set b= '4005' where a=4;
//更新为4005
mysq1> update test_innodb_lock set b= '4005' where a=4;
Query OK, 1 row affected (0.00 sec )
Rowsmatched:1 Changed:1 Warnings :0
