Mysql的锁机制解读

最近系统多次因对数据库锁使用不当引起问题，故从基础学习一下mysql锁机制。

基本概念：

共享锁

共享锁的代号是S，是Share的缩写，共享多的锁粒度是行或者元组（多个行），一个事务获取了共享多之后，可以对锁定范围内的数据执行读操作。

排它锁

排它锁的代号是X，是eXclusive的缩写，排他锁的粒度是行或元组，与共享锁相同，一个事务获取了排它锁之后，可以对锁定范围内的数据执行写操作。

例：假设有两个事务t1和t2

如果事务t1获取了一个元组的共享锁，事务t2还可以立即获取这个元组的共享锁，但不能立即获取这个是元组的排它锁，（必须等到t1释放共享锁之后，才能获取这个元组的排它锁）。

如果事务t1获取了一个元组的排它锁，事务t2不能立即获取这个元组的共享锁，也不能立即获取这个元组的排它锁，（必须等到t1释放排它锁之后）。

意向锁

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁（IS）和意向排它锁（IX）两类。

意向共享锁表示一个事务“有意“对数据上共享锁。“有意“这两个字表达的意思比较微妙，说的明白点就是指事务想干这个事但还没有真去干。

例：一个事务t1执行了这样一个语句“select * from table lock in share model”,如果这个语句执行成功，就对表table上了一个意向共享锁。lock in share model就是说事务t1在接下来要执行的语句中要获取S锁。

意向排它锁的含义同理可知，上例中获取意向排它锁，可以使用“select * from table for update”。

lock in share model和for update这个两个东西在数据理论中还有个学名交悲观锁，与悲观锁相对的当然还有乐观锁。可见各种锁都是成双成对出现的，关于悲观锁和乐观锁的问题暂且不表。

锁的互斥和相容关系

锁与锁之间的关系，要么相容，要么互斥

锁a和锁b的相容是指：操作同样一组数据时，如果事务t1获取了锁a，另一个事务t2还可以获取锁b。

锁a和锁b的互斥是指：操作同样一组数据时，如果事务t1获取了锁a，另一个事务t2必须在t1释放锁a之后方可获取锁b。

上面的共享锁、排它锁、意向共享锁，意向排它锁相互之间都是有兼容/互斥关系的，可以用一个兼容性矩阵表示（y表示兼容，n表示不兼容）：

	X	S	IX	IS
X	n	n	n	n
S	n	y	n	y
IX	n	n	y	y
IS	n	y	y	y

兼容矩阵为什么这个样子的？

X和S的相互关系在上文中解释过了，IX和IS的相互关系全是兼容的，这也好理解，因为他们都只是“有意”，还处于YY阶段，没有真干，所以是可以兼容的。剩下的就是X和IX，X和IS，S和IX，S和IS的关系了，我们可以由X和S的关推倒出这四组关系。

简单的说：X和IX的关系=X和X的关系，因为事务在获取IX锁后，接下来就有权利获取X锁。如果X和IX兼容的话，就会出现两个事务都获取了X的情况，这与我们已知的X与X互斥是矛盾的，所以X与IX只能是互斥关系。其余三组同理可知。

MySQL三种锁级别

MySQL有三种锁级别：表级锁（table-level locking），页级锁（page-levellocking），行级锁（row-levellocking）

三种锁的特性可大致归纳如下：

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

行级锁：开销大，加锁慢，锁定粒度最小，会出现死锁，发生锁冲突的概率最低，并发度最高。

页级锁：开销大小、加锁速度、锁定粒度、发生所冲突的概率及并发度都介于表级锁与行级锁之间，会出现死锁。

而MySQL的存储引擎中：MyISAM和MEMORY采用表级锁；BDB采用（默认）页级锁，但也支持表级锁；InnoDB采用（默认）行级锁，但也支持表级锁；

一、MyISAM表锁

MyISAM存储引擎只支持表锁

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

可以通过查询table_locks_waited（表示不能立即获取锁的次数）和table_locks_immediate（表示可以立即获取锁的查询次数）状态变量来分析系统上的表锁定争夺

mysql>show status like"table%";
+———————–+———-+
| Variable_name | Value |
+———————–+———-+
| Table_locks_immediate | 76939364 |
| Table_locks_waited | 305089 |
+———————–+———-+
2 rows in set (0.00 sec)

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

2、表级锁的锁模式

表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table WriteLock）。MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，而在执行（UPDATE，INSERT）前，会自动给涉及的所有表加写锁。

所以对MyISAM表进行操作，会有以下几种情况：

a、对MyISAM表的读操作（加读锁），不会组三其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后才会执行其他进程的写操作。

b、对MyISAM表的写操作（加写锁），会组三其他进程对同一表的读和写操作，只有当写锁释放后才会执行其他进程的读写操作。

3、并发插入

原则上数据表有一个读锁时，其他进程无法对此表进行写操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1、2

a、当concurrent_insert为0时，不允许并发插入。

b、当concurrent_insert为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这个是MyISAM的默认设置。

c、当concurrent_insert为2时，无论MyISAM表中有没有空洞，都允许下表尾并发插入

4、锁调度

由于MySQL认为写请求一般比读请求重要，所以如果读写请求同时进行的话，MySQL将会优先执行写操作。这样MyISAM表在进行大量的写操作时（特别是更新的字段存在索引的情况下），会造成查询操作很难获取读锁，从而导致查询阻塞。

我们可以通过一些设置来调试MyISAM的调度行为：

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

b、通过执行命令SET LOW_PRIORITY_UPDATES=1，使该链接发出的更新请求有限级降低。

c、通过INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

上面三种方法都是要么更新优先，要么查询优先的方法。这里要说明的是，不要盲目的给MySQL设置为读优先，因为一些需要长时间运行的查询操作，也会使进程“饿死”。只有根据你你的实际情况，来决定设置那种操作优先。这些方法还是没有从根本上同时解决查询和更新的问题。

在一个有大数据量高并发的MySQL里，我们还可以采用另一种策略来进行优化，那就是通过MySQL主从（读写）分离来实现负载均衡，这样可以避免优先哪种操作从而可能导致另一种操作的阻塞，下面将用一个篇幅来说明MySQL的读写分离。