05-MySql 锁

Mysql中的锁

一、锁分类

1.1 表级锁

• 开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低，适用于查多更新少的场景

1.2 行级锁

• 开销大，加锁慢，会出现死锁，锁定粒度最小，锁冲突概率最低，并发高，适用于修改较多的场景

1.3 页面锁

• 开销和加锁时间界于表锁和行锁之间；会出现死锁，锁粒度、并发度和效率在上面2者之间

二、锁和存储引擎

• InnoDB：支持表级锁和行级锁，默认是行级锁
• MyISAM：只支持表级锁
• Memory：只支持表级锁

三、MyISAM和锁

3.1 表级锁的2种模式

MySQL表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock），表锁满足读写互斥，写写互斥，读读共享的原则。

• 对MyISAM的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；
• MyISAM表的读写操作之间，以及写写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

3.2 MyISAM表锁

• 前面介绍的2中表锁主要是针对MyISAM存储引擎的，因为MyISAM存储引擎只要加锁就一定是表锁。MyISAM的加锁操作是隐式的，
  MyISAM在执行查询语句（SELECT）前，会自动给目标表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给目标表加写锁，这个过程并不
  需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。当然也可以显式加锁，如下：

LOCK tables tableA read local,tableB read local; //加写锁就是write
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;
Unlock tables;

3.3 MyISAM锁并发

• MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

当concurrent_insert设置为0时，不允许并发插入。
当concurrent_insert设置为1时，如果MyISAM允许在一个读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。
可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入锁争用。例如，将concurrent_insert系统变量为2，总是允许并发插入；同时，
通过定期在系统空闲时段执行OPTIONMIZE TABLE语句来整理空间碎片。

3.4 MyISAM锁调度

• 前面提到MyISAM读和写锁是互斥，读写操作是串行。但是如果2个线程一个请求读锁，一个请求写锁，那么请求写锁的线程会先拿到锁从而阻塞请求读锁的线程，即使请求读
  锁的线程先到达锁等待队列，因为MyISAM认为写操作比读操作重要，这使得MyIsam不适合更新频繁的场景，因为他可能会阻塞大量的读请求。
• MyISAM支持通过LOW_PRIORITY_UPDATES参数来设置指定语句的优先级来缓解前面的线程之间因为锁竞争导致的问题。详情可以阅读MySQL中的锁（表锁、行锁）

四、InnoDB和锁

4.1 InnoDB事物

• InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁和表级锁有许多不同之处，另外，事务的引入也带来了一些新问题。
• 关于事物，可以参考：03-MySql 事物

4.2 InnoDB锁竞争

• 获取InonoD行锁争用情况:

//可以查询到等待锁的请求数量和等待锁的平均时间等信息来帮助我们分析锁的竞争情况
show status like 'innodb_row_lock%';

4.3 InnoDB锁细节

• InnoDB实现了以下两种类型的行锁。

共享锁 (S) ：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁，但是其他事物可以获取共享锁。
排他锁 (X) ：允许获取排他锁的事务更新数据，其他事务尝试取得相同的数据集的共享读锁和排他写锁都会被阻止。

• 另外InnoDB为了增加锁的粒度选择，还有意向共享锁和意向排他锁，意向共享锁就是说我有意向去获取共享锁，并且在获取共享锁之前，我是必须要获取到这个数据对应的IS锁。

意向共享锁 (IS) ：事务打算给数据行共享锁。事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁 (IX) ：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

• 兼容性列表如下：

当前锁模式/是否兼容/请求锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

4.4 InnoDB加锁

• 根据上面表格中的锁之间的兼容性，如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。
• 意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及及数据集加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁；
  事务可以通过以下语句显示给记录集加共享锁或排锁。

共享锁 (S) ：SELECT * FROM table_name WHERE id=1  LOCK IN SHARE MODE
排他锁 (X) ：SELECT * FROM table_name WHERE id=1  FOR UPDATE 

(在前面的文章MySQL中的锁（表锁、行锁）中，我们显式使用排它锁在Mysql默认的RR隔离级别下避免了幻读)

4.5 InnoDB行锁实现方式

• InnoDB行锁是通过索引上的索引项来实现的，这一点MySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索
  引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并
  发性能。

五、间隙锁（Next-Key锁）

• 间隙锁可以理解为锁住那些符合条件但是还目前还不存在的数据，在前面的03-MySql 事物文章中，
  演示幻读的时候，我们先在事物A中查询id=10的记录发现不存在，然后再事物B中插入一条id=10的记录并commit，然后事物A尝试插入id=10的记录就会失败，这就是幻读的
  一种外在的表象。
• 那么我们可以通过间隙锁来避免这样的情况，在事物A中查询id=10不存在的时候，会将id=10的记录锁住，即使它不存在，在事物B中也不允许操作，然后事物A就可以顺利
  插入记录了。这里将条件改为id>10会更好理解，加了间隙锁之后，其他的事物不能对id>10的记录做修改，但是这样也加大了锁的粒度，很可能会造成严重的阻塞。因此在
  使用条件的时候，我们进来用精确条件会好一些。

六、锁和事物

• 可以通过间隙锁来解决幻读，这样我们可以在不使用serilizable的情况下避免幻读。(关于幻读可以参考：03-MySql 事物)

七、锁的应用

• 对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可
以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表。

• 在InnoDB下 ，使用表锁要注意以下两点。

使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层MySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，
InnoDB层才能知道MySQL加的表锁，MySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务，
COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

• 例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ ;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

八、参考

• [1] MySQL中的锁（表锁、行锁） （非常详细的好文）