MySQL锁机制

一、MySQL锁概述

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低，又分读锁与写锁。 

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高，又分共享锁与排他锁。 

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 

MySQL的锁机制的特点是不同的存储引擎支持不同的锁机制：

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；

BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 

二、表级锁

MyISAM 操作数据都是使用的表锁，更新一条记录就要锁整个表，导致性能较低，并发不高。当然同时它也不会存在死锁问题。但表级锁创建锁的开销小，当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，推荐使用表级锁。

MySQL表级锁分为读锁和写锁。

1）读锁与写锁

读锁：

• 申请读锁成功后，所有线程都可对该表进行读操作，但不允许对该表进行写操作，包括当前线程也不允许
• 当锁住了A表之后，就只能对A表进行读操作，对其他表进行读操作会出现错误（tablename was not locked with LOCK TABLES）

LOCK TABLE table_name [ AS alias_name ] READ

# sql

UNLOCK tables

写锁：当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止

LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE

# sql

UNLOCK tables

写锁会优先读锁，可通过设置LOW_PRIORITY属性，用来降低该语句的优先级

也可以通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

还可以给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

2）使用场景

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。 

给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。例如， 有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL：

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local;
    Select sum(total) from orders;
    Select sum(subtotal) from order_detail;
Unlock tables;

上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关MyISAM表的并发插入问题，在后面还会进一步介绍。 

3）查询表级锁排队情况

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁排队情况：

mysql> show status like 'table%';

Variable_name | Value 
Table_locks_immediate | 2979 
Table_locks_waited | 0 
2 rows in set (0.00 sec))

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

4）并发插入（Concurrent Inserts）

上文提到过MyISAM表的读和写是串行的，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。 

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

• 当concurrent_insert设置为0时，不允许并发插入。
• 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

可以利用MyISAM存储引擎的并发插入特性，来解决应 用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

三、行级锁

InnoDB 与 MyISAM 的最大不同有两点：一是 InnoDB 支持事务；二是 InnoDB 采用了行级锁。

行级锁是Mysql中锁定粒度最细的一种锁，能大大减少数据库操作的冲突，由于其粒度小，加锁的开销最大。

行级锁分为共享锁和排他锁。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

1）共享锁与排他锁

共享锁：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。语法为：

select * from table lock in share mode

排他锁：对某一资源加排他锁，自身可以进行增删改查，其他事务不能再在其上加其他的锁。语法为：

select * from table for update

注意这里有一个误区，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是其他事务不能再在其上加其他的锁。

mysql InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁，而select语句默认不会加任何锁类型，所以可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

2）使用场景

1，InnoDB数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。

2，in share mode和for update显式加锁必须在事务中才生效，在事务commit或者rollback或者断开连接后自动解锁。

3，InnoDB事务四个级别中（Repeatable Read和Serializable）2个事务隔离级别是不需要手动加锁的，因为其他会话的事务是无法取得这2种事务中执行的数据的。而InnoDB默认事务基本是Repeatable Read，所以可以理解为不用手动加锁

3）获取InonoD行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

四、死锁

死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程.

T1:begin tran

select * from table lock in share mode

update table set column1='hello'

T2:begin tran    

select * from table lock in share mode

update table set column1='world'

假设 T1 和 T2 同时达到 select，T1 对 table 加共享锁，T2 也对 table 加共享锁，当 T1 的 select 执行完，准备执行 update 时，根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update。在升级排他锁前，必须等 table 上的其它共享锁（T2）释放，同理，T2 也在等 T1 的共享锁释放。于是死锁产生了。

解决办法：

遇到死锁可以执行如下的查询语句观察等待的事务：

-- 查看当前的事务
select * from information_schema.innodb_trx;

-- 查看当前锁定的事务
select * from information_schema.innodb_locks;

-- 查看当前等锁的事务
select * from information_schema.innodb_lock_waits;

也可以执行如下的查询将死锁的日志导出：

show engine innodb status

五、乐观锁与悲观锁

乐观锁与悲观锁是一个抽象的概念描述，实际中并没有这种锁的存在

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不加锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制来实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。上文说到的表锁、行锁，共享锁（读锁）与排它锁（写锁）等等，都是在做操作之前先上锁，也就是所谓的悲观锁。

 版本号机制
一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

例子：
假设数据库中帐户信息表中有一个 version 字段，当前值为 1 ；而当前帐户余额字段（ balance ）为 $100 。

1-1：操作员 A 此时将其读出（ version=1 ），并从其帐户余额中扣除 $50（ $100-$50 ）。
1-2：在操作员 A 操作的过程中，操作员B 也读入此用户信息（ version=1 ），并从其帐户余额中扣除 $20 （ $100-$20 ）。

2-1：操作员 A 完成了修改工作，将数据版本号加一（ version=2 ），连同帐户扣除后余额（ balance=$50 ），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录 version 更新为 2 。
2-2：操作员 B 完成了操作，也将版本号加一（ version=2 ）试图向数据库提交数据（ balance=$80 ），但此时比对数据库记录版本时发现，操作员 B 提交的数据版本号为 2 ，数据库记录当前版本也为 2 ，不满足 “ 当前最后更新的version与操作员第一次的版本号相等 “ 的乐观锁策略，因此，操作员 B 的提交被驳回。

这样，就避免了操作员 B 用基于 version=1 的旧数据修改的结果覆盖操作员A 的操作结果的可能。