MySQL中的锁
1. 锁
1.1. 锁的简介
1.1.1. 为什么需要锁？
到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？
1.1.2. 锁的概念
l 锁是计算机协调多个进程或线程并发访问某一资源的机制。
l 在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
l 锁对数据库而言显得尤其重要，也更加复杂。
1.1.3. MySQL中的锁
l 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
l 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
l 页面锁(gap锁,间隙锁)：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
在这个部分只讲表级锁、行级锁，gap锁放到事务中讲
1.1.4. 表锁与行锁的使用场景
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适
1.2. MyISAM锁
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
1.2.1. 共享读锁
语法：lock table 表名 read
1. lock table testmysam READ 启动另外一个session select * from testmysam 可以查询
2. insert into testmysam value(2);
update testmysam set id=2 where id=1;
报错
3.在另外一个session中
insert into testmysam value(2); 等待
4.在同一个session中
insert into account value(4,'aa',123); 报错
select * from account ; 报错
5.在另外一个session中
insert into account value(4,'aa',123); 成功
6.加索在同一个session 中 select s.* from testmysam s 报错
lock table 表名 as 别名 read;
1.2.2. 独占写锁
1.lock table testmysam WRITE
在同一个session中
insert testmysam value(3);
delete from testmysam where id = 3
select * from testmysam
2.对不同的表操作（报错）
select s.* from testmysam s
insert into account value(4,'aa',123);
3.在其他session中 （等待）
select * from testmysam
1.2.3. 总结：
l 读锁，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
l 读锁，对MyISAM表的读操作，不会阻塞当前session对表读，当对表进行修改会报错
l 读锁，一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；
l 写锁，对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
l 写锁，对 MyISAM表的写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错
1.3. InnoDB锁
在mysql 的 InnoDB引擎支持行锁
共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。
1.3.1. 语法
上共享锁的写法：lock in share mode
例如： select * from 表 where 条件 lock in share mode；
上排它锁的写法：for update
例如：select * from 表 where 条件 for update；
1.3.2. 注意：
1.两个事务不能锁同一个索引。
2.insert ，delete ， update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
CREATE TABLE testdemo (
`id` int(255) NOT NULL ,
`c1` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`c2` int(50) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `idx_c2` (`c2`) USING BTREE
)
ENGINE=InnoDB;
insert into testdemo VALUES(1,'1',1),(2,'2',2);
1.
BEGIN
select * from testdemo where id =1 for update
在另外一个session中
update testdemo set c1 = '1' where id = 2 成功
update testdemo set c1 = '1' where id = 1 等待
2.BEGIN
update testdemo set c1 = '1' where id = 1
在另外一个session中
update testdemo set c1 = '1' where id = 1 等待
3.
BEGIN
update testdemo set c1 = '1' where c1 = '1'
在另外一个session中
update testdemo set c1 = '2' where c1 = '2' 等待
4.第一个session中
select * from testdemo where id =1 for update
第二个session
select * from testdemo where id =1 lock in share mode
回到第一个session UNLOCK TABLES 并不会解锁
使用commit 或者 begin或者ROLLBACK 才会解锁
5.再来看下表锁
lock table testdemo WRITE
使用commit，ROLLBACK 并不会解锁
使用UNLOCK TABLES 或者begin会解锁
1.4. 锁的等待问题
那么现在来说一个实际的问题，在工作中经常一个数据被锁住，导致另外的操作完全进行不下去。
你肯定碰到过这问题，有些程序员在debug程序的时候，经常会锁住一部分数据库的数据，而这个时候你也要调试这部分功能，却发现代码总是运行超时，你是否碰到过这问题了，其实这问题的根源我相信你也知道了。
举例来说，有两个会话。
程序员甲，正直调试代码
BEGIN
SELECT * FROM testdemo WHERE id = 1 FOR UPDATE
你正直完成的功能也要经过那部分的代码，你得上个读锁
BEGIN
SELECT * FROM testdemo WHERE id = 1 lock in share mode
这个时候很不幸，你并不知道发生了什么问题，在你调试得过程中永远就是一个超时得异常，而这种问题不管在开发中还是在实际项目运行中都可能会碰到，那么怎么排查这个问题呢？
这其实也是有小技巧的。
select * from information_schema.INNODB_LOCKS;
真好，我通过这个sql语句起码发现在同一张表里面得同一个数据有了2个锁其中一个是X（写锁），另外一个是S（读锁），我可以跳过这一条数据，使用其他数据做调试
可能如果我就是绕不过，一定就是要用这条数据呢？吼一嗓子吧（哪个缺德的在debug这个表，请不要锁这不动），好吧，这是个玩笑，其实还有更好的方式来看
select * from sys.innodb_lock_waits
我现在执行的这个sql语句有了，另外看下最下面，kill命令，你在工作中完全可以通过kill吧阻塞了的sql语句给干掉，你就可以继续运行了，不过这命令也要注意使用过，如果某同事正在做比较重要的调试，你kill，被他发现可能会被暴打一顿。
上面的解决方案不错，但如果你的MySQL不是5.7的版本呢？是5.6呢，你根本就没有sys库，这个时候就难办了，不过也是有办法的。
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread
FROM
information_schema.innodb_lock_waits w
INNER JOIN
information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
看到没有，接下来你是否也可以执行kill 29 这样的大招了。
