1 MySQL的三种锁

1.1 表锁

开销小，加锁快

不会出现死锁

锁定粒度大，发生锁冲突的概率最高，并发度最低

1.2 行锁

开销大，加锁慢

会出现死锁

锁定粒度小，发生锁冲突的概率最低，并发度最高

1.3 页锁

开销和加锁时间介于表锁和行锁之间

会出现死锁

锁定粒度介于表锁和行锁之间，并发度一般

1.4 引擎与锁

MyISAM和MEMORY支持表锁

BDB支持页锁,也支持表锁

Innodb既支持行锁,也支持表锁,默认行锁

1.5 查询表锁争用情况

检查table_locks_waited和table_locks_immediate状态变量分析

table_locks_immediate : 可以立即获取锁的次数

table_locks_waited : 不能立即获取锁，需要等待锁的次数

table_locks_waited 的值越高，则说明存在严重的表级锁的争用情况

2 表锁模式（MyISAM)

ＭySQL的表锁有两种模式

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

2.1 表锁兼容性

锁模式的兼容如下表

可见，对ＭyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;

对ＭyISAM表的写操作,则会阻塞其他用户对同一表的读和写请求;

ＭyISAM表的读和写操作之间,以及写和写操作之间是串行的!(当某一线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作.其他线程的读、写操作都会等待,直到锁被释放为止)

2.2 如何加表锁

对于 MyISAM 引擎

执行select前,会自动给涉及的所有表加 读

执行更新（update,delete,insert)会自动给涉及到的表加 写

不需要用户直接显式用lock table命令

对于给MyISAM显式加锁，一般是为了在一定程度上模拟事务操作，实现对某一个时间点多个表一致性读取

2.2.1 实例

订单表 - orders

记录各订单的总金额total

订单明细表 - order_detail

记录各订单每一产品的金额小计subtotal

假设我们需要检查这两个表的金额合计是否相符,可能就需要执行如下两条SQL

[图片上传失败…(image-3017e3-1547370332969)]

这时,如果不先给这两个表加锁,就可能产生错误的结果;

因为第一条语句执行过程中,order_detail表可能已经发生了改变.

因此,正确写法应该如下

[图片上传失败…(image-8081d7-1547370332969)]

2.2.2 注意点

上面的例子在LOCK TABLES时加了‘local’选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录

在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及表的锁，并且MySQL支持锁升级;

也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；

同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作

其实，在自动加锁的情况下也基本如此，MySQL会一次获得SQL语句所需要的全部锁.这也正是MyISAM表不会出现死锁（Deadlock Free）的原因

##2.3 tips

当使用lock tables时,不仅需要一次锁定用到的所有表

且同一表在SQL语句中出现多少次,就要通过与SQL语句中别名锁多少次

lock table actor read

会提示错误

select a.first_name.....

需要对别名分别锁定

lock table actor as a read,actor as b read;

3 MyISAM的并发锁

在一定条件下，MyISAM也支持并发插入和读取

3.1 系统变量 : concurrent_insert

控制其并发插入的行为,其值分别可以为

0 不允许并发插入,所有插入对表加互斥锁

1 只要表中无空洞,就允许并发插入.

MyISAM允许在一个读表的同时，另一个进程从表尾插入记录(MySQL的默认设置)

2 无论MyISAM表中有无空洞,都强制在表尾并发插入记录

若无读线程,新行插入空洞中

可以利用MyISAM的并发插入特性，来解决应用中对同表查询和插入的锁争用

例如,将concurrent_insert系统变量设为2，总是允许并发插入;

同时,通过定期在系统空闲时段执行OPTIONMIZE TABLE语句来整理空间碎片，收到因删除记录而产生的中间空洞

删除操作不会重整整个表,只是把 行 标记为删除,在表中留下空洞

MyISAM倾向于在可能时填满这些空洞,插入时就会重用这些空间,无空洞则把新行插到表尾

3.2 MyISAM的锁调度

MyISAM的读和写锁互斥,读操作串行的

一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同表的写锁，MySQL如何处理呢？

写进程先获得锁!!!

不仅如此,即使读进程先请求先到锁等待队列,写请求后到,写锁也会插到读请求之前!!!

这是因为MySQL认为写请求一般比读请求重要

这也正是MyISAM表不适合有大量更新 / 查询操作应用的原因

大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞

幸好,我们可以通过一些设置来调节MyISAM的调度行为

指定启动参数low-priority-updates

使MyISAM引擎默认给予读请求以优先权利

执行命令SET LOW_PRIORITY_UPDATES=1

使该连接发出的更新请求优先级降低

指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性

降低该语句的优先级

虽然上面3种方法都是要么更新优先，要么查询优先，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题

另外，MySQL也提供了一种折中的办法来调节读写冲突;

即给系统参数max_write_lock_count设置一个合适的值;

当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会

4 InnoDB锁

InnoDB与MyISAM的最大不同有两点

支持事务

采用行锁

行级锁和表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题

4.1 事务

一组SQL语句组成的逻辑处理单元

原子性(Actomicity)

事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行

一致性（Consistent）

在事务开始和完成时，数据都必须保持一致状态

这意味着所有相关的数据规则都必须应用于事务的修改，以保持完整性

事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的

隔离性（Isolation）

一个事务所做的修改在最终提交前对其他事务不可见

持久性（Durability）

一旦事务提交，它对于数据的修改会持久化到DB

4.2 事务的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户

但并发事务处理也会带来一些问题，主要包括以下几种情况

更新丢失（Lost Update）

当多个事务选择同一行,然后基于最初选定值更新该行时，由于事务隔离性，最后的更新覆盖了其他事务所做的更新.

例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改;

如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题

脏读（Dirty Reads）

一个事务正在对一条记录做修改，在该事务提交前，这条记录的数据就处于不一致状态

这时，另一个事务也来读取同一条记录，读取了这些未提交的数据

不可重复读（Non-Repeatable Reads）

一个事务在读取某些数据已经发生了改变、或某些记录已经被删除

幻读（Phantom Reads)

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据

4.3 事务隔离级别

在并发事务的问题中，“更新丢失”通常应该是完全避免的;

但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此，防止更新丢失应该是应用的责任

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决

数据库实现事务隔离的方式，基本可以分为以下两种

在读取数据前,对其加锁,防止其他事务对数据进行修改

不加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照,并用这个快照来提供一定级别(语句级或事务级)的一致性读取.

从用户的角度,好像是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control,MVCC），也经常称为多版本数据库

数据库的事务隔离级别越严格,并发副作用越小,但付出的代价也越大

因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”矛盾

为了解决“隔离”与“并发”的矛盾，ANSI SQL定义了４种隔离级别

查看Innodb行锁争用情况

如果发现争用比较严重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比较高

查询information_schema相关表来查看锁情况

设置Innodb monitors

进一步观察发生锁冲突的表，数据行等，并分析锁争用的原因

停止监视器

默认情况每15秒会向日志中记录监控的内容;

如果长时间打开会导致.err文件变得非常巨大;

所以确认原因后,要删除监控表关闭监视器,或者通过使用–console选项来启动服务器以关闭写日志功能