目录
1、锁的分类：
根据对数据的操作类型分类：
- 读锁（共享锁）；
- 写锁（排他锁）
根据对数据的操作粒度分类：
- 表锁（偏读）；
- 行锁（偏写）；
- 页锁
2、表锁（偏读）
MyISAM存储引擎只支持表锁。
特点：开销小、加锁快；锁定粒度大，发生锁冲突的概率高，并发度低。
2.1 表共享读锁
在一个会话中为一个表增加了读锁，那么这个表不能再被任何会话（包括加锁的这个会话）修改，这个会话也不能再去读其他的表。前者是为了保证读操作的安全性，后者是为了避免用户只去加锁不去解锁，导致残留很多不再需要的锁。
读锁对写操作的影响：如果会话自身要去修改这个加了读锁的表，会直接报错；而如果其他会话想要修改这个表，会被阻塞，直到读锁被解开。
但是读锁和读锁之间是兼容的，一个会话为一个表增加了读锁，此时仍然允许另一个会话给这个表增加读锁。
MyISAM在执行查询语句(select)之前，会自动给涉及到的所有表加读锁，在执行增删改操作之前，会自动给设计的表增加写锁。
2.2 表独占写锁
在一个会话中为一个表增加了写锁，那么这个会话可以任意读写这个表，而其他会话对这个表的读和写操作都会被阻塞；这个表也不能再去读写其他的表，会直接报错。
2.3 表锁分析
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。
命令：show status like ‘table%’;
- table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；
- table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁则值加1。
2.4 加锁和解锁
自动加锁（隐式加锁）：MyISAM在执行查询语句（Select）之前会自动给所有涉及到的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前会自动给表加写锁。这种隐式的加锁，会在执行完commit语句后自动解锁。
查询语句默认会自动commit，也可以关闭这种配置：set autocommit = 0;
手动加锁（显式加锁）：lock table 表名 read/write, 表名2 read/write;
查看哪些表有锁的命令：show open talbes; 这个命令会显示出所有库的所有表，其中In_use字段为0，表示这个表没有加锁，大于0，表示这个表有锁
解锁：unlock tables; 这个命令会解锁当前会话获取的全部的锁。
2.5 注意事项
- 显示加锁时，必须一次性取得所有涉及到的表的锁，因为加锁后不能再访问无锁的表，这也是MySQL不会出现死锁的原因；
- 读锁还有一种“read local”格式，这种是满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录；
- 应用中应该尽量避免使用过于复杂的查询操作，如果执行操作过程，应该对操作进行分解，以减少锁冲突；
- 当显示加锁的时候，同一个表如果有多个别名，那么每个别名都要加锁，否则也会出错。锁定别名的命令：
lock table table_name read;
lock table table_name as a read, table_name as b read;2.6 并发插入
上文提到的MyISAM表的读和写是串行的，这是总体而言，在一定条件下，MyISAM也是支持查询和插入操作的并行的。
MyISAM存储引擎有一个系统变量concurrent_insert，专门用来控制器并发插入的行为，值可以设置成0,1,2。
- concurrent_insert = 0：不允许并发插入；
- concurrent_insert = 1：如果MyISAM表空间中没有空洞（指表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这是MyISAM的默认设置
- concurrent_insert = 2：无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
2.7 MyISAM的锁调度
MyISAM的读写锁调度是写优先，也就是说，如果两个进程分别请求读锁和写锁，那么MySQL会优先让写进程获得锁。这是因为MyISAM认为写操作比读操作更重要。这就导致了Myisam不适合作为主表的引擎，因为增加写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成长时间的阻塞。
当然，还可以通过一些配置来调节MyISAM的调度行为：
- 指定启动参数low-priority-updates，使MyISAM引擎默认给与读请求以优先的权利；
- 执行命令SET LOW_PRIOTITY_UPDATES=1，使该连接发出的更新请求优先级更低；
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级
另外，还有一种折中的方式来调节读写操作，给系统参数max_wirte_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定的获得锁的机会。
3、行锁（偏写）
3.1 行锁基础
特点：开销大，加锁慢；会出现死锁；锁定粒度小，发生冲突的概率最低，并发度最高。
InnoDB和Myisam最大的区别有两点：一是支持事务；二是采用了行级别的锁。事务可以参考文章：事务。
在创建表的时候，可以通过engine=innodb指定存储引擎为InnoDB：create table test_table (a int(10), b varchar(16)) engine = innodb;
3.2 查看行锁：
命令：
show status like ‘innodb_row_lock%’;- innodb_row_lock_current_waits：当前正在等待锁定的数量；
- innodb_row_lock_time：从系统启动到现在锁定总时间长度；
- innodb_row_lock_time_avg：每次等待所花的平均时间；
- innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；
- innodb_row_lock_waits：系统启动后到现在总共等待的次数。
如果innodb_row_lock_time和innodb_row_lock_waits都比较高，说明锁的争用比较严重，此时可以通过查询information_schema数据库中相关的表来查看锁的情况，也可以通过设置InnoDB Monitor来进一步观察发生锁冲突的表、数据行等，分析锁争用的原因。
1、查询information_schema数据库中的表，其中\G是让现实的结果转换90度：
select * from innodb_locks \G;2、设置InnoDB Monitor来观察锁冲突：
CREATE TABLE innodb_monitor(a INT) ENGINE = INNODB;
show engine innodb status \G;可以通过以下命令来停止：
DROP TABLE innodb_monitor;需要注意的是，打开监视器后，默认会每15s向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常巨大，所以用户在确认问题原因后，要删除监控表以关闭服务器。
3.3 行锁模式
行锁：
InnoDB实现了两种行锁：
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；
- 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁
意向锁：
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Lock），这两种意向锁都是表锁：
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行家共享锁之前必须先取得该表的IS锁；
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁之前必须先取得该表的IX锁。
意向锁是InnoDB自动加的，不需要用户干预。对于UPDATE、DELETE、INSERT语句，InnoDB会自动给设计数据集添加排他锁（X）；对于普通的SELECT语句，InnoD不会加任何锁。
显式加锁：
- 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
- 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE;
3.4 行锁的实现方式
InnoDB是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。
InnoDB行锁分为3中情况：
- Record lock：对索引项加锁；
- Gap lock：对索引项直接的间隙、第一条记录前的间隙、或者最好一条记录后的间隙加锁；
- Next-key lock：前两种的组合，对记录及其前面的间隙加锁。
InnoDB这种行锁实现特点意味着：
- 如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。也就是说，如果没有索引或者索引失效，将会导致行锁变成表锁；
- 行锁是对索引加的锁，而不是对记录加的锁，如果是针对相同索引的方式，即使不是同一条记录，也会出现锁冲突；
- 当表中有多个索引的时候，不同事物可以通过使用不同的索引来锁定不同的行，不论是主键索引、唯一索引或者普通索引，InnoDB都会使用行锁来对数据加锁；
- 即使在条件中使用了索引字段，但是是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下，InnoDB也会对所有记录加锁。因此，在分析MySQL锁冲突的情况下，还需要检查SQL的执行计划，确认是否真正使用了索引。
3.5 Next-key锁
当我们使用范围条件而不是相等条件检索数据，并请求行锁的时候，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的Next-key锁。
例如一个进程对where id > 100加锁，即使表中没有id = 200，但是其他进程也不能去插入id = 200的记录，因为这是被锁定的。
InnoDB使用Next-key的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，例如上一段的例子；另一方面，是为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录的时候，InnoDB这种加锁机制会阻塞符合条件范围内简直的并发插入，这往往会造成很严重的锁等待。
另外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-key的锁。
3.6 使用表锁的时刻
对于InnoDB，绝大多数情况下都应该使用行级锁，但在个别特殊情况下，也可以考虑使用表级锁：
- 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务的执行效率低，而且可能造成其他事务长时间等待和锁冲突；
- 事务涉及多个表，比较复杂，如果使用行锁有可能引起死锁，造成大量事务回滚。
当然，如果上面两种情况会大量出现，那么就应该考虑MyISAM表了。
给InnoDB加表锁：lock table table_name read/write, table_name2 read/write;
上述命令虽然可以给InnoDB加表锁，但是需要注意一下两点：
- 表锁不是由InnoDB存储引擎层管理的，而由上一层MySQL Server负责的。当且仅当autocommit = 0、innodb_table_locks = 1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则无法自动检测和处理这种死锁。
- 只有autocommit = 0设置为0时，MySQL才会给表加锁；事务结束前，不要用unlock tables是否表锁，因为unlock tables隐含了提交事务的意思；commit和rollback并不能释放lock table加的表锁，必须要unlock tables显式释放。
3.7 死锁
MyISAM总是一次性获取全部的锁，要么全部满足，要么等待，因此不会出现死锁；而InnoDB，除开单个SQL组成的事务，锁的逐步获得的，所以是可能产生死锁的。
发生死锁后，InnoDB一般是可以自动检测到的，并通过使一个事务释放锁并回退来解决死锁。但是在涉及到外部锁或者表锁的情况下，InnoDB并不能完全检测到死锁，需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。这个参数可以在并发访问很高的情况下，避免过多的事务因为无法获取到锁而挂起，占用过多的计算机资源，造成严重的性能问题，甚至拖垮数据库。
避免死锁的常用方法：
- 如果不同的程序会并发存取多个表，那么应该尽量约定以相同的顺序来访问这几个表，以降低死锁的机会；
- 程序以批量的方式处理数据的时候，应该事先对数据进行排序，保证每个线程按固定的顺序来处理；
- 事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新时再申请排他锁，因为用户再申请排他锁时，其他进程可能已经申请了相同记录的共享锁；
- 在repeatable-read的隔离级别下，如果两个线程同时对相同条件记录显示增加行锁的排他锁，在没有记录的情况下，两条记录都会成功。如果两个程序此时都去插入一条新纪录，就会出现死锁；
- 隔离级别为read committed时，如果两个线程都先执行select...for update，判断是否存在符合条件的记录，如果没有加入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第一个线程提交后，第二个线程会因为主键重复而出错，但是却会获得排他锁。此时，其他线程再来申请排他锁就会出现死锁。
3.8 行锁优化建议总结
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁（例如varchar必须加单引号等）；
合理设计索引，尽量缩小锁的范围；
尽可能减少检索条件，避免间隙锁；
尽量控制事务大小，减少锁定资源量和时间长度；
尽可能使用低级别事务隔离。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
