mysql---锁

MySQL中的锁

MySQL的锁机制比较简单
其最显著的特点是不同的存储引擎支持不同的锁机制。
比如：
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）。
Innodb存储引擎既支持行级锁（row-level locking）,也支持表级锁，但默认情况是采用行级锁。

锁的比较

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发送锁冲突的概率最低，并发布也最高。
3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

仅从锁的角度来说：
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统。

行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更适合。

MyISAM的表锁

MyISAM的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

共享读锁

共享读锁语法：给表加锁，加共享锁，lock table 表名 read
实例演示：
1、lock table testmyisam READ （给testmyisam表加锁）
启动另外一个session
select * from testmyisam 可以正常查询。

2、insert into testmyisam value（20）；
update testmyisam set id = 2 where id = 1;
更新或者插入操作都会报错。

3、在另外一个session中（相当于开启另外一个输入命令的窗口）
insert into testmyisam value（20）；等待，等待加锁的session释放锁

4、在同一个session中
insert into testdemo value（2, ‘2’,‘3’）；插入数据到其他表报错。
select * from testdemo; 查询其他表报错。
相当于你已经进入了1号房间的门，如果你不从1号的房间出来，就永远也操作不了2号房间的东西。

5、在另外一个session中
insert into testdemo value（2, ‘2’,‘3’）；成功。
A在1号房间，不妨碍B去2号房间。

6、加锁在同一个session中 select s.* from testmyisam s 报错。
如果需要用别名，可以如下加锁：
lock table 表名 as 别名 read；

查看 show status LIKE ‘table_locks_waited’ 表被锁过几次。

独占写锁

表独占写锁语法：给表加锁，加独占锁， lock table 表名 write。
实例演示：
1、lock table testmyisam WRITE
在同一个session中
insert testmyisam value(3); 阻塞等待
delete from testmyisam where id = 3; 阻塞等待
select * from testmyisam ；成功

2、对不同的表操作（报错）
insert into testdemo value（2, ‘2’,‘3’）；
select * from testdemo;

3、在其他session中
select * from testmyisam ; 等待

总结

1、对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求。
2、对MyISAM表的读操作，不会阻塞当前session对表读，当对其他表进行修改会报错。
3、一个session使用Lock Table 命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误。
4、另外一个session可以查询表中的记录，但更新就会出现锁等待。
5、对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
6、对MyISAM表的写操作，当前session可以对本表做CRUD，但对其他表进行操作会报错。

Innodb行锁

在MySQL的Innodb引擎支持行锁。
行锁：
1、共享锁又称读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
语法：
lock in share mode
例如：select * from 表 where 条件 lock in share mode。

2、排它锁又称写锁。当一个事务对某几个上写锁时，不允许其他事务写，当允许读。更不允许其他事务给这几行上任何锁。包括写锁。
语法：
for update
例如：select * from 表 where 条件 for update。

注意：
1、两个事务不能锁同一个索引。
2、insert、delete、update在事务中都会自动默认加上排它锁。
3、行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

执行锁的互斥例子

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 = 2 等待，等待上一个session的解锁。

2、
BEGIN
update testdemo set c1 = ‘1’ where id = 1
在另外一个session中
update testdemo set c1 = ‘1’ where id = 2 成功

3、
BEGIN
update testdemo set c1 = ‘1’ where c1 = ‘1’
在另外一个session中
update testdemo set c1 = ‘2’ where c1 = ‘2’ 等待，当一个不是索引的列加锁的时候就是表锁了

Innodb表锁

和MyISAM差别不大。
注意：开启一个新事务的时候会解锁表。

行锁和表锁如何解锁总结

1、先看看行锁
第一个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 才会解锁。

2、再来看看表锁
lock table testdemo WRITE
使用commit，rollback并不会解锁。
使用UNLOCK TABLES或者BEGIN会解锁。

面试题

1、系统运行一段时间，数据量已经很大，这时候系统升级，有张表A需要增加个字段，并发量白天晚上都很大，请问怎么修改表结构。
面试考点：修改表结构会导致表锁，数据量大修改数据很长，导致大量用户阻塞，无法访问！

物理结构修改工具pt-online-schema-change

使用场景

在 mysql 5.6 版本以前，修改表结构如添加索引、修改列，需要锁表，期间不能写入，对于一个有几千万数据的大表来说，这个简直不能接收，对于分秒必争的互联网时代，服务中断很长时间的后果是很严重的。

升级Mysql 到5.6 版本后，情况会好转，支持online-DDL, 但是实际在Alter表的时候，还会有可能锁表。

pt-online-schema-change是Percona-toolkit一员， 支持在不锁表的情况下，在线改表。