MySQL中的读锁和写锁（InnoDb行锁表锁 MyISAM共享读锁 MyISAM独占写锁）

须知少年凌云志，曾许人间第一流。这篇文章主要讲述MySQL中的读锁和写锁（InnoDb行锁表锁 MyISAM共享读锁 MyISAM独占写锁）相关的知识，希望能为你提供帮助。
mysql锁（数据库都是悲观锁，java、redis、zookeeper会有乐观锁）锁的概念
MySQL中的锁
MyISAM锁
MyISAM共享读锁
MyISAM独占写锁
MyISAM锁调度
InnoDb
InnoDb行锁
InnoDb表锁
锁的概念

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁

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

MySQL中的锁
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
仅从锁的角度来说：
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适
MyISAM的表锁
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

读的时候可以读，读的时候不能写，写的时候不能读，写的时候不能写（读就是共享读，其他的可以读，不能写，写是独立写，其他的不能读也不能写）
MyISAM的表锁-共享读锁
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 testdemo value(2,2,3); 报错
select * from testdemo ; 报错
5.在另外一个session中
insert into testdemo value(2,2,3); 成功
6.加索在同一个session 中 select s.* from testmysam s 报错
lock table 表名 as 别名 read;
查看 show status LIKE table_locks_waited 表被锁过几次

MyISAM的表锁-独占写锁
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 testdemo value(2,2,3);
3.在其他session中（等待）
select * from testmysam
MyISAM的表锁
总结

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

InnoDb行锁在mysql 的 InnoDB引擎支持行锁
行锁:

共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

语法
上共享锁的写法：lock in share mode
例如： select * from 表 where 条件 lock in share mode；
上排它锁的写法：for update
例如：select * from 表 where 条件 for update；
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 等待
注意：
1.两个事务不能锁同一个索引。
2.insert ，delete ， update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
4. InnoDb表锁和MyISAM差别不大。注意：开启一个新事务的时候会解锁表
innodb行级锁
写锁：写不同数据可以，同条数据等待
用commit和rollback

表锁不用begin，表锁不用commit和rollback释放

开启一个新事物的时候，自动引用unlock()
面试题：系统运行一段时间，数据量已经很大，这时候系统升级，有张表A需要增加个字段，并发量白天晚上都很大，请问怎么修改表结构
面试考点：（修改表结构会导致表锁,数据量大修改数据很长，导致大量用户阻塞，无法访问！）

1. 首先创建一个和你要执行的alter操作的表一样的空的表结构。
2. 执行我们赋予的表结构的修改，然后copy原表中的数据到新表里面。
3. 在原表上创建一个触发器在数据copy的过程中，将原表的更新数据的操作全部更新到新的表中来。
4. copy完成之后，用rename table 新表代替原表，默认删除原表。
【MySQL中的读锁和写锁（InnoDb行锁表锁 MyISAM共享读锁 MyISAM独占写锁）】