数据库锁

锁的分类

• 表锁
• 行锁
• 间隙锁

锁是计算机协调多个进程或线程并发访问某一资源的机制

表锁（偏向于读锁）

偏向MyISAM存储引擎，开销小，加锁快；无死锁，锁定粒度大，发送锁冲突的概率最高，并发度低

表锁案例

• 手动添加表锁

  lock table tb_name1(表名) read(write),tb_name1(表名) read(write)
  

  可一次性给多个表添加表锁，可以添加读锁(read)或写锁(write)
• 释放表锁

  unlock tables;
  

• 图示

  • 添加读锁
    当前为添加表锁的进程：
    
    给表添加读锁，可以查询表数据，但是不能更新表数据

    在另一个进程中操作：
    
    可以发现，lock_table表在另一个进程被加读锁时，当前进程只能对表查询操作，不能更新，如果更新则会阻塞，直到解锁

  • 添加写锁
    当前添加表锁的进程：
    
    添加写锁时，当前进程既可以查询表数据，也可以对表数据进行更新
    在另一个进程中操作：
    
    
    可以发现，lock_table表在另一个进程被加写锁时，当前进程无论是对表数据的查询还是更新，都处于阻塞状态，直到解锁

表锁总结

MyISAM在执行查询语句(select)前，会自动给涉及的所有表加读锁,在执行增删改操作前，会自动给涉及的表加写锁

• 对MyISAM表的读操作(加读锁)时，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进行的写操作
• 对MyISAM表的写操作(加写锁)时，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作

行锁（偏向于写锁）

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁。锁定粒度最小，发生锁冲突的概率最低，并发度也最高

InnoDB与MyISAM的最大不同点是：InnoDB支持事务，采用了行级锁

行锁案例

• 添加行锁步骤

  • 1、将自动提交关闭

    set autocommit=0;
    

  • 2、修改某行的数据，在没手动提交之前相当于给这一行记录添加行锁

• 图示
  所用表
  

  第一步：在两个进程里面将自动提交关闭：
  

  第二步：在进程1里将id=1的记录的num字段值更新为“1001”：
  
  第三步：在进程2查看表数据：
  
  发现数据没有改变，是因为第二步在进程1里面还没有手动提交

  第四步：在进程1里修改的数据没提交之前，在进程2里尝试修改id为1的记录的字段num值：
  
  发现阻塞了，因为第二步在进程1里修改之后没有提交，所以这行记录被上了行锁，当进程1提交之后进程2所做的修改才会解除阻塞并修改

如何分析行锁定

-- 通过检查innodb_row_lock状态变量来分析系统上的行锁争夺情况
show status like 'innodb_row_lock%';

各个状态量的说明

Innodb_row_lock_current_waits       当前正在等待锁定的数量
* Innodb_row_lock_time            从系统启动到现在锁定的总时间长度
* Innodb_row_lock_time_avg        每次等待所花费平均时间
Innodb_row_lock_time_max        从系统启动到现在等待最长的一次所花费的时间
* Innodb_row_lock_waits           系统启动后到现在总共等待的次数

如何锁定一行

select * from innodb_table where id=1 for update;

间隙锁

什么是间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做"间隙"

innodb也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁

间隙锁的危害

因为SQL执行过程中通过范围查找的话，他会锁定整个范围内所有的索引值，即使这个键值并不存在

间隙锁有一个比较致命的弱点，就是当锁定以为范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害