mysq-锁篇

产生背景

项目中后台服务有个数据接入功能，多个终端进行数据上报，后台做入库处理。入库逻辑存在查询（select）和更新（update）的操作。压测时发现，同一时间大量终端连接过来时，对数据的更新会存在问题。即高并发下，mysql的写入或更新会出现数据一致性问题。

• 优化方案
  1.修改业务逻辑，数据接入只做插入，不做更新、删除等操作。涉及数据统计的内容，单独做一个定时任务去完成。
  2.将查询、更新sql语句写成一个，保证原子性。
  3.事务+锁：共享锁、悲观锁、乐观锁。
  业务逻辑已经固定，改动量大，暂不考虑。而将查询和更新sql写成一个，不够灵活，也不一定能实现。故，采用第三种，即加锁实现。
• 锁类型介绍
  1.共享锁（读锁）
  共享锁又被称为读锁，顾名思义就是指，当一个线程对某一资源添加共享锁后，其他线程只有查询的权限，而没有写（insert、update、delete）权限。只有当拥有权限的线程执行完所有操作后，其他线程才能执行写操作。
  实现方式：SELECT … LOCK IN SHARE MODE
  2.排它锁（写锁）
  排它锁又被称为写锁，是指当一个线程对某一资源添加排它锁后，其他线程便不能再访问该资源，即其他线程没有读写权限（没有select权限，也是区别共享锁的地方）。只有该线程执行完所有操作后，其他线程才能执行读写操作。
  实现方式：select * from ad_plan for update
  3.悲观锁（排它锁）
  悲观锁又被称为排它锁。
  4.乐观锁
  乐观锁不是mysql自有的机制，而是通过表结构设计和代码实现的。其方式为，给表添加一个字段（version或timestamp）。查询不做限制，在更新时候在where条件中会带上查询时候获取的version或timestamp（update table set num=num-1 where id=10 and version=23）。
  如果更新失败，表示在更新操作之前，有其他执行程序已经更新了该库存数，那么就要尝试重试来保证更新成功。
• 使用场景
  1.共享锁
  合于两张表存在关系时的写操作。但是由于其特性，即加锁后，其他线程可以读，但写必须等待锁释放后才能执行。故设计不当，很容易出现死锁现象。
  2.排它锁
  排它锁或悲观锁，如果where条件中的字段没有索引，则会导致整张表锁住，其他读请求就会阻塞，所有不适合读请求过大情况。适合大量写请求的操作。
  3.乐观锁
  乐观锁的机制，决定其在更新时候，会出现多次更新失败情况，不适合大量写请求情况。读用乐观锁，写用悲观锁。
• 总结说明
  1.innoDB引擎数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。
  2.数据库规定同一资源上（如表）不能同时共存共享锁和排他锁，却可以存在多个共享锁，这被称为共享锁与共享锁兼容。
  3.悲观锁会导致整个表被锁住，影响性能，可以通过添加索引方式，使其只对某一行加锁，从而避免整个表加锁。
  4.悲观锁，仅适用于InnoDB,并且必须开启事务，在begin和commit之间才生效。
  5.InnoDB默认是行级别的锁，当有明确指定的主键时，使用的是行锁；否则使用的是表锁。举例如下：

明确指定主键，并且由此记录，行级锁。
select name,age from tb_user where id = ‘1’ for update（id是主键）

明确指定主键/索引，若查无记录，无锁。
select name,age from tb_user where id = ‘1’ for update（id是主键，但不存在id = 1的数据）

无主键/索引，表级锁。
select name,age from tb_user where age = 12 for update（age是普通字段）

主键/索引不明确，表级锁。
select name,age from tb_user where age = 12，id = ‘1’ for update（id是主键，age不是，但数据库有此数据）

• 共享锁出现死锁情况举例

Deadlock found when trying to get lock; try restarting transaction
原因：多个请求（多线程）同时执行查询和更新sql时，如果此时有共享锁，会出现死锁情况。假设 T1 和 T2 同时达到 select，T1 对 table 加共享锁，T2 也对 table 加共享锁，当 T1 的 select 执行完，准备执行 update 时，根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update。在升级排他锁前，必须等 table 上的其它共享锁（T2）释放，同理，T2 也在等 T1 的共享锁释放。于是死锁产生了。