高性能的MySQL（1）锁和MVCC

一、MySQL逻辑架构

客户端

链接/线程处理

查询缓存

解析器

优化器

存储引擎

二、锁

1、读写锁

读锁是共享的，是互相不阻塞的，多个客户在同一时间读取同一资源，互补干扰。写锁是排他的，会阻塞其他的写锁和读锁，写锁有更高的优先级~

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源，锁定的资源越少，系统的并发性更好。

表锁（table lock）

表锁是锁开销最小的策略，会锁定整张表，一个用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对表的所有读写操作。举例：有如下一张表

1

2

3

4

5

6

7

8

9

10

11

12

<code>CREATE</code> <code>TABLE</code> <code>`emp2` (</code>

<code>  </code><code>`id` </code><code>int</code><code>(11) </code><code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'0'</code><code>,</code>

<code>  </code><code>`</code><code>name</code><code>` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num1` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num2` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num3` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job_num` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`d` </code><code>date</code> <code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>PRIMARY</code> <code>KEY</code> <code>(`id`),</code>

<code>  </code><code>KEY</code> <code>`job_num` (`job_num`)</code>

<code>) ENGINE=MyISAM </code><code>DEFAULT</code> <code>CHARSET=utf8 ;</code>

我们在一个终端中加上读表锁

<a href="http://blog.51cto.com/attachment/201310/105606537.png" target="_blank"></a>

然后打开另一个终端，可以读但不可以写

<a href="http://blog.51cto.com/attachment/201310/105742614.png" target="_blank"></a>

直到第一个链接释放锁之后，才能修改成功。

<a href="http://blog.51cto.com/attachment/201310/110043513.png" target="_blank"></a>

<a href="http://blog.51cto.com/attachment/201310/110119735.png" target="_blank"></a>

行级锁：

行锁可以最大程度的支持并发，但是锁的开销也最大，InnoDB支持行级锁，在存储引擎层实现。

死锁：

两个或者多个事务在同一资源上互相占用，并请求锁定对方占用的资源时，导致产生死锁，比如有如下2个事务

<code>#事务1</code>

<code>start </code><code>transaction</code><code>;</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=45 </code><code>where</code> <code>id = 4;</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=54 </code><code>where</code> <code>id = 3;</code>

<code>commit</code><code>;</code>

<code>#事务2</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=12 </code><code>where</code> <code>id = 3;</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=18 </code><code>where</code> <code>id = 4;</code>

如果巧合，两个事务都执行了第一条，更新了一行，同时锁定了该行数据，接着都尝试去执行第二条，却发现被对方锁定，然后2个事务都等待对方释放锁，则陷入死循环。为了解决这问题，数据库系统实现了各种死锁检测和死锁超时机制。

2、隐式和显式锁定

隐式：InnoDB，在执行事务过程中会自动加锁，除非执行COMMIT或ROLLBACK时锁会自动释放。

显式：可以使用SELECT...LOCK IN SHARE MODE || SELECT...FOR UPDATE生成锁

三、InnoDB的MVCC（多版本并发控制）

通过在每行记录的最后保存2个隐藏的列来实现。一个保存行的创建时间，一个保存行的过期时间，其实存的系统版本号。每开始一个新的事务，系统版本号都会递增。事务开始时刻的系统版本号作为事务的版本号，用来和查询到的每行记录的版本号来比较。

SELECT：

InnoDB会根据以下2个条件检查每行记录：

a、只查找版本早于当前事务版本的数据行，也就是行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改的。

b、行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行在事务开始之前未被删除。

INSERT：

InnoDB为新插入的每一行保存当前系统版本号为行版本号

DELETE：

InnoDB为删除的每一行保存当前系统版本号作为行的删除标示。

UPDATE：

InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标示。

保存这2个额外的系统版本号，使大多数读操作都可以不用枷锁。这样数据库操作简单，性能也会很好，并且保证只会读到标准的行。但是需要额外的空间和更多的检查工作。

本文转自shayang8851CTO博客，原文链接：http://blog.51cto.com/janephp/1306411，如需转载请自行联系原作者