mysql锁知识小了解

2019独角兽企业重金招聘Python工程师标准>>>

一、概述

mysql的锁分为表锁和行锁两种，其中myisam引擎用的是表锁， innoDB默认的使用是行锁， 其他情况是表锁。

两种锁的优缺点：

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

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

  页面锁：介于以上两者之间

 无法确定哪种锁更合适：

表级锁更适合查询为主，只有少量按索引更新数据的应用，如web应用。

行级锁适合有大量索引并发更新少量不同的数据，同时有并发查询的应用，如一些在线的事物系统。

二、MyIsam存储引擎

只支持表锁。

查询表级锁的争用情况：

table_locks_waited 和 table_locks_immediate 

1.  mysql> show status like 'table%';

2.  +-----------------------+-------+

3.  | Variable_name         | Value |

4.  +-----------------------+-------+

5.  | Table_locks_immediate | 2979  |

6.  | Table_locks_waited    | 0     |

2.1、表级锁的锁模式

两种模式：表共享读锁，表独占写锁

分析：

读锁不会阻塞其他用户对同一张表的读请求，但会阻塞对同表的写请求；

对表的写请求，会阻塞对同表的读操作和写操作

myisam的读操作和写操作之间，以及写操作之间是互相串行的。

2.2、如何加表锁

myisam引擎在执行查询语句之前会自动的给涉及到的表加上读锁，执行更新操作的语句之前会自动加上写锁，这个过程不需要用户干预。

加锁代码如下

1.  Lock tables orders read local, order_detail read local;

2.  Select sum(total) from orders;

3.  Select sum(subtotal) from order_detail;

4.  Unlock tables;

2.3、并发插入

 Myisam也支持查询和插入同时进行的并发操作：存储引擎有一个系统变量concurrent_insert, 专门控制并发插入的行为。值为 0 1 2

concurrent_insert == 0 ：不允许并发的插入

concurrent_insert == 1 ：（默认值1）如果该表没有空洞（表的中间没有被删除的行），允许边读边插入。

concurrent_insert == 2 ：无论有没有空洞都运行在表的末尾插入数据

2.4、锁调度

Myisam的读写操作是互斥的，读写操作是串行的。

如果一个进程请求表的读锁，同时另一个线程请求表的写锁，那么写的进程会先获得锁，就算是读的进程先到锁的等待队列，如果有写进程

那么也是写进程获得锁，因为mysql默认认为写进程比读的进程更加重要。 可以通过一些设置来调节这些级别：

SET LOW_PRIORITY_UPDATES = 1 ：更新请求的优先级降低

还可以通过调节系统参数来调节读写的冲突：max_write_lock_count, 当读锁的阻塞数量达到这个值的时候，就会把写请求的优先级降低，给读的进程获得锁的机会

三、InnoDB存储引擎

InnoDB和Myisam最大的不同就是

1、事物支持

2、行级锁

3.1、事物以及其的属性

• 原子性：对数据的修改要么执行，要么全部不执行
• 一致性：事物开始和完成时，数据都应该保持一致的状态，数据结构的完整性都是一致的。
• 隔离性：数据库系统提供一定的隔离机制，保证事物在不收外部并发操作影响的独立环境进行，意味着事物处理过程中是不对外可见的。
• 持久性：事物完成后，对于数据的修改是永久性的，即时出现系统故障会保持

相对于串行处理来说，并发事物处理能力能大大的增强数据库资源的利用率，提高数据库系统的吞吐量，可以支持更多的用户量，但是也带来一定的问题：

1、更新丢失：两个事物选择同一行，然后都是基于最后选定的值更新，每个事物都不知道其他事物的存在。最后的事物就会覆盖前面的事物更新的数据。

2、脏读：一个事物正在对一条记录做修改，在这个事物完成并提交之前，此时这条数据就是不一致的状态；有一条事物同时来读取这条记录，并进行下一步的处理，就会产生未提交的数据依赖关系。叫做脏读。

3、不可重复读：一个事物在读取某个数据后的某个时间，再次读取以前读过的数据，却发现读出的数据发生了变换，或者删除了某些记录，这种现象叫做“不可重复读”。

4、幻读：一条事物按照一定的条件检索出以前读取的数据，却发现检索出了其他事物插入的符合条件的数据。这种现象称为 幻读。

3.2、事物隔离级别

事物处理带来的问题中，“更新丢失” 应该是可以完全避免的。防止数据更新丢失 不能全部靠数据库的事物机制来控制，需要应用程序对更新的数据加上必要的锁来控制。 其余的“脏读， 幻读， 不可重复读” 都是数据库的读一致性的问题，那么必须由数据库提供一定的事物隔离机制来解决。解决方式基本分为两种：

• 读取数据前，对其加上锁。
• 不加任何锁机制，通过一定的机制生成数据请求时间点的一致性数据快照，并用这个快照提供一定级别的（语句， 事物）的一致性读取，从用户的角度来看，好像是数据库可以提供一份数据的多个版本，因此可以叫做数据库的多版本控制（多版本数据库）。

数据库的隔离机制越高，并发副作用越小， 但是付出的性能代价也是越高，因为事物隔离实质上就是对事物进行串行进行。

为了解决并发和隔离的矛盾，提供了4个隔离级别， 每个级别的隔离程度不同，允许出现的副作用也不同，根据业务要求来决定。

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别	是	是	是
已提交读	语句级	否	是	是
可重复读	事物级	否	否	是
可序列化	最高级别（事物级）	否	否	否

3.3、InnoDB锁

共享锁：允许一个事物去读一行，阻止其他事物获得相同数据集的排他锁

排他锁：允许获的排他锁的事物更新数据，阻止其他事物取得相同数据集的共享读锁和排他写锁。

InnoDB为了行锁和表锁共存，实现多粒度的锁机制，还有两种内部使用的意向锁，这两种意向锁都是表锁

意向共享锁：事物打算给数据行加上行共享锁，事物在给一个数据行加共享锁前必须获得该表的意向共享锁

意向排他锁：事物打算给数据行加上行排他锁，事物在给一个数据行加排他锁前必须获得该表的意向排他锁

InnoDB的实现方式

行锁是通过索引上的索引项上加锁来实现的：只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表搜

3.4、InnoDB死锁

死锁的例子：

步骤1：

用户1：select * from table1 where id = 1;

用户2：select * from table2 where id = 1;

步骤2：

用户1：select * from table2 where id = 1; （此时在步骤1中的用户2中已经取得了排他锁，故等待。。。）

用户2：select * from table1 where id = 1;  (死锁)

发生死锁后，InnoDB 一般都能自动检测到，并使一个事物释放锁并回退，另一个事物获得锁，继续完成事物。但是在涉及到外部锁，或涉及到表锁的情况下，InnoDB并不能自动检测到死锁，需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决，需要说明的是， 这个参数并不是只用来解决死锁的问题，在并发访问比较高的情况下，如果大量事物因无法立即获得所需的锁而挂起，会占用大量的计算机资源，造成严重的性能问题，甚至拖垮数据库，我们通过设置合适的锁等待超时值，可以避免这种情况的发生。

此文是通过了解mysql的锁的基本工作原理：

1、让我们在业务数据处理的流程上做出好的设计，可以大部分避免无法解开死锁导致应用挂掉。

举个例子：不同的程序会并发存取某个表，尽量约定好程序以相同的顺序来访问表来降低死锁几率。

2、同时了解一下常用的检测参数值，追踪mysql的锁状态。

转载于:https://my.oschina.net/gyj0728/blog/505613