Mysql锁机制

发表于 | 分类于 ，

锁的基本介绍

MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。
MyISAM采用的是表级锁；InnoDB存储引擎支持行级锁也支持表级锁。默认行级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。​
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表级锁更适合于以查询为主；而行级锁则更适合于操作语句。

MyISAM表锁

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

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求。
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
MyISAM表的读操作与写操作之间，写操作之间是串行的。

MyISAM写锁阻塞读的案例：
​当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。
MyISAM读阻塞写的案例：
​ 一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。
【读写互斥，写写互斥】
注：
MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁。

InnoDB锁

支持事务

默认加行锁。

事务及其ACID属性

事务是由一组sql语句组成的逻辑处理单元。事务具有ACID属性。
原子性actomicity：事务是一个原子操作单元；
一致性consistent：在事务开始和完成时态持一致；undo log
隔离性isolation：保证事务不受外部并发影响；
持久性durable：对数据的修改是永久性的。 redo log

并发事务带来的问题

脏读：一个事务还没有提交，这条记录就能被另一个事务读取到。
不可重复读：一个事务还没有提交时这条记录不能被另一个事务读取到。但是，提交之后，另一个事务也能读到更新的记录。
幻读：在插入和删除数据时候产生的，一个事务占用了序列号导致另一个事务无法完成插入删除。

read uncommitted：脏读，不可重复读，幻读
read committed：不可重复读，幻读
repeatable read：幻读
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

1
2
mysql> show status like 'innodb_row_lock%';
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

InnoDB的行锁模式及加锁方法

共享锁（s）：又称读锁。
排他锁（x）：又称写锁。如果用了排他锁，当前线程可以对他进行增删改查操作，但是其他线程既不能获得我们的读锁，同时也不能获得我们的写锁。

mysql innodb 引擎默认的修改语句：

  1. update，delete，insert会自动给涉及到的数据加排他锁
  2. select默认不会加锁
    如果要加排他锁：select...for update
    如果加共享锁：select...lock in share mode
  3. 所以加过排他锁的数据行在其他事务中是不可能修改数据的，也不能通过for update和lock in share mode方式查询数据，但可以直接通过select from查询数据。
InnoDB行锁实现方式

innodb行锁是通过索引上的索引项加锁实现的。与mysql和oracle不同，后者是是通过数据块中对应数据行加锁实现的。
所以只有通过索引条件检索数据时，innodb才能使用行级锁，否则只能使用表锁！
1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁
2、创建带索引的表进行条件查询，innodb使用的是行锁
3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以一个索引值对应多条记录的时候，虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现冲突的。

总结

对于MyISAM的表锁：

  1. 每次锁的是表，读和写是串行的
  2. 在一定条件下，MyISAM允许查询和插入并发执行。可以利用这点解决应用中对同一表查询和插入的锁争用问题
  3. MyISAM默认的锁调度机制是写优先
  4. 表锁的粒度大，读写间又是串行，可能会出现严重锁等待。可以采用InnoDB表来减少锁冲突

对于InnoDB锁，用户可以通过设计和SQL调整等措施减少锁冲突和死锁。

  1. 尽量使用较低的隔离级别；尽量使用索引访问访问数据
  2. 小事务发生锁冲突概率小
  3. 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁
  4. 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  5. 除非必须，查询时不要显示加锁