MySQL锁概述

前言

根据锁的特性可大致分为以下三类:
表级锁：开销小，加锁快；不会产生死锁；锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢；会产生死锁，锁定粒度最大；发生锁冲突的概率最低，并发量最高。
页级锁：开销和加锁时间介于表锁和行锁之间，会发生死锁，锁定粒度介于表锁和行锁之间，并发量一般。

表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。如web应用。
行级锁更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如在线事务处理（OLTP）系统。
下面重点说一下MYsql表锁和Innodb行锁。

一、表级锁

1.查询表级锁的争用情况

命令：

table_lock_waited
table_lock_immediate

table_lock_waited 的值比较高，说明存在较严重的表级锁征用情况。

2.锁模式

共享读锁（Table Read Lock）：锁兼容能对数据加读锁，不允许加写锁；也就是说，对于myisam表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞同一表的写请求。
表独占写锁（Table Write Lock）：锁兼容不允许加读锁和写锁。也就是说，对于myisam的表的写操作，则会阻塞其他用户对同一表的读和写操作。

3.如何加表锁

myisam在执行查询（SELECT）语句，会自动给涉及的所有表加读锁；在执行更新(UPDATE INSERT DELETE)操作前，会自动加写锁。
使用local table 命令显示加锁，基本上为了方便说明问题，在一定程度模拟事务操作，实现一个时间点多个表的一致性读取。
例如：有一个订单表order，记录总额total，同时还有一个订单详细表order_detail，记录每个订单的额度subtotal。假设我们需要检查这两个表的金额是否相符。可能执行下面两条 SQL语句。

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

如果不给这两个表加锁，可能会造成错误的结果，因为第一条语句执行完，order_detail表可能已经发生改变。正确的应该是：

Lock table orders read local,order_detail read local;
Select sum(total) from order;
Select sum(subtotal) from order_detail;
unlock table;

说明：
在lock table 加入了local，作用在于：在满足mysiam表并发的插入条件的情况下，允许其它用户在表尾并发插入记录。
在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表;同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。在自动加锁的情况下也是如此，MySAM 总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁( Deadlock Free)的原因。

4.并发插入

MISAM表的读和写是串行的，但这是就总体而言的，在特定条件下，MyISAM表也支持查询和插入操作的并发进行。

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

当concurrent insert设置为0时，不允许并发插入。

当concurrent insert设置为 1时，如果MyISAM表中没有空洞(表的中间没有被删除的行)，MyISAM 允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

当concurrent insert 设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发。

5.MYISAM的锁调度

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么进积销求某个MNSSM表的读锁，同时另一个进程也请求同表的写锁，MySQL如何处理呢?答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到如何处会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也**正是Myisam表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成在的MySAM很难获得读锁，从而可能永远阻塞。**这种情况有时可能会变得非常糟糕!幸好我们可以通一些设置来调节 MyISAM的调度行为。

（1）通过指定启动参数lowapririty-updates使MyISAM引擎默认给予读请求以优先的权力
（2）通过执行命令SETLOW PRIORITY UPDATES=1,使该连接发出的更新请求优先级降低
（3）通过指定INSERT、UPDATE、DELETE语句的LOW_ PRIORITY属性，降低该语句的优先级。

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

另外，Mysql也提供了一种折中的办法来调节读写冲突，即给系统参量写请水的优场路设理一个合适的值，当一个表的读锁达到这个值后，MSQOL就暂时特写请求的优先级降低，给读进程-些获得锁的机会。

二、 行级锁

在学习行锁之前，最好弄清楚事务。

1.InnoDB的行锁模式及加锁模式

共享锁（读锁，S锁）：允许一个事务读一行，不允许其他事务获得相同数据集的排他锁。
排他锁（写锁，X锁）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排它写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，innoDB还有两个内部使用的意向锁，这两种意向锁都是表锁：
意向共享锁（IS）：事务打算给数据行加行共享锁；也就是说事务再给以一个数据行加共享锁前必须先取得该表的is锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务再给一个数据行加排他锁前必须先取得该表的IX锁。

兼容列表：

意向锁是innodb自动加的，不需用户干预。对UPDATA DELETE INSERT 语句，innodb会自动涉及数据集加排他锁（x），对于普通的select语句，innodb不会加任何锁；当然事务可以通过显示给记录集加共享锁和排他锁。

2.行锁实现方式

innodb行锁是给索引上的索引项加锁，如果没有索引，innodb将通过隐藏的聚簇索引来对记录加锁。
三种加锁情景：
Record lockL:对索引项加锁。
Gap lock：对索引项之间的间隙加锁，第一条记录之前的间隙加锁，或最后一条的记录的间隙加锁。
Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

行锁的实现特点意味着：如果不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，实际效果就跟表锁一样。

三、Next-key锁

当我们使用总用条件而不是相等条件检索数据，并请求共享或排他锁时，Innodb会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫间隙锁(GAP). InoDB也会对这个“间隙” 加锁，这种锁机制就是所谓的NetKey锁。

举例来说，假如emp表中只有101条记录，其empid的值分别是1、2. .10 101.SQL语句如下:

selectfrom emp where empid > 100 for update;

这是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101 (这些记录并不存在)的“间隙”加锁。

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的示例，要是不使用间隙锁，如果其他事务插入了empid 大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读;另一方面， 是为了满足其恢复和复制的需要。

很显然，在使用范围条件检索并锁定记录时，ImoDB这种加锁机制会阻塞符合条件范围内键值的并发插人，这往往会造成严重的锁等待。因此， 在实际应用开发中，尤其是并发插入比较多的应用，要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。还要特别说明的是，innodb除了通过范围条件加锁时使用NextKey锁外，如果使用相等条件请求给一个不在的记录加锁， innodb也会使用nextkey锁。

四 、扩展

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

（1）第一种情况是:事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

（2）第二种情况是:事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

当然应用中这两种事务不能太多，否则， 就应该考虑使用MyISAM表了。在lmDB下，使用表锁要注意以下两点。

(1)使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层-MySQL Server负责的，仅当autcommi=innodb_ table_ locks=1 (默认设置)时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁;否则，InnoDB将无法自动检测并处理这种死锁。

(2)在用LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁;事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCKTABLES会隐含地提交事务; COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。