mysql 锁机制一

最新推荐文章于 2024-09-23 09:12:23 发布

michael8239

最新推荐文章于 2024-09-23 09:12:23 发布

阅读量524

点赞数

分类专栏：数据库文章标签： insert mysql MySQL table 存储引擎

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

锁机制是数据库系统区别于文件系统的一个关键特性，他可以确保用户能以一致的方式读取和修改数据。

为了保证一致性，必须有锁的介入。MySQL操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素等地方也都适用锁，从而允许对多种不同资源的并发访问。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？

这里肯定要用到事物，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

MySQL的锁管理机制：

Meta-data元数据锁：在table cache缓存里实现的，为DDL（Data Definition Language）提供隔离操作。一种特别的meta-data元数据类型，叫Name Lock。（SQL层）
表级table-level数据锁（SQL层）
存储引擎特有机制 — row locks行锁，page locks页锁，table locks表级，版本控制（在引擎中实现）
全局读锁 — FLUSH TABLES WITH READ LOCK（SQL层）

上张图：

MySQL的锁执行流程：

计算语句使用到的所有表
在每个表：打开open表 — 从table cache缓存里得到TABLE对象，并在此表加上meta-data元数据锁
等待全局读锁后改变数据
在每个表：锁lock表 — 在表加上table-level数据锁
执行语句：调用：handler::write_row()/read_rnd()/read_index()，等；隐式地调用引擎级engine-level锁机制
在每个表：释放表的数据锁
在每个表：释放表的DDL锁并把表放回table cache缓存里
DDL语句也是一样，没有典型的执行计划。

MySQL三种锁地级别：页级、表级、行级。

三种锁地特性：

    表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
    行级锁：开销稍大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
    页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

在执行SQL语句时，会实现存储引擎内部锁，比如InnoDB存储引擎的“行锁”（MyISAM存储引擎只支持表锁，而InnoDB存储引擎支持行锁。实际上，行锁并不总是会增加开销，只有当实现本身增加开销时，行锁才会增加开销）。

表锁：

手动增加表锁：

lock table XXX read(write);

释放表锁：

unlock tables;

表级锁性能监控：

show status like ‘table%’;

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

表级锁的锁模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）

    MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。
    所以对MyISAM表进行操作，会有以下情况：
         a、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
        b、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

关于表锁并发插入
    原则上数据表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
    MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
        a、当concurrent_insert设置为0时，不允许并发插入。
        b、当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
        c、当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

表锁地优化：

使用表级锁定在锁定实现的过程中比实现行级锁定或页级锁定所带来的附加成本要小，锁定本身所消耗的资源也是最少的。但是由于锁定的颗粒度比较大，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。所以表锁优化，最关键的是如何让其提高并发度。由于锁定级别是不可能改变的了，所以首先需要尽可能地锁定的时间变短，然后就是让可能并发进行的操作尽可能地并发。

1.缩短锁定时间：

1）尽量减少大的复杂的Query，将复杂的Query分拆成几个小的Query分步进行；

2）尽可能地建立足够高效的索引，让数据检索更迅速；

3）尽量让MyISAM存储引擎的表至存放必要的信息，控制字段类型；

4）利用合理的机会优化MyISAM表数据文件。

2、分离能并行的操作

2.合理利用上面提到的concurrent_insert
3.合理利用读写优先级：

MyISAM的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读。所以，可以根据系统环境的差异决定读与写的优先级。如果系统是一个以读为主，而且要优先保证查询性能的话，可以通过设置系统参数选项low_priority_updates=1，将写的优先级设置为比读低，即告诉MyISAM尽量优先处理读请求。当然，如果系统需要优先保证数据写入的性能的话，则不用设置low_priority_updates参数了。

以上笔记参考网络资料以及《MySQL技术内幕：InnoDB存储引擎》，如有谬误请指正。