mysql 锁

1 概述

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

在数据库中，除传统的计算资源（cpu,ram,io）的争用外，数据也是许多用户共享的资源，为了保证数据并发访问的一致性，有效性。锁冲突也是影响数据库并发访问性能的一个重要因素

1.1 锁的分类

从对数据操作的类型

• 读锁
  针对同一份数据，多个读操作可以同时进行而不会互相影响

• 写锁
  当前写操作没有完成前，会阻断其他写锁与读锁

从对数据操作的粒度分

• 表锁

• 行锁

2 表锁（偏读）

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

2.2 表锁的使用

• 对锁的查看

show open tables

• 加表锁

lock table [tableName] [read/write]

• 分析表锁定

show  status like 'table_locks%';

Table_locks_immediate:产生表级锁定的次数，表示立即获取锁的查询次数，没立即获取锁值加一

Table_locks_waited:出现表级锁定争用而发生等待的次数，此值高则说明存在着严重的表级锁争用情况

• 解锁

unlock tables;

2.3 表锁读锁的权限

表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

2.4 表锁写锁的权限

表独占写锁 （table write lock）：会阻塞其他用户对同一表的读和写操作

myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量更新会使查询很难得到锁，从而造成阻塞。

2.5 表级锁mdl

mdl不需要显示使用，在访问一个表时会被自动加锁，其作用是保证读写的正确性。当对一个表做增删改成操作时，加mdl读锁；当要对表结构做变更操作时，加mdl写锁

• 读锁之间不互斥，因此可以并发同时对同一张表增删改查
• 读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。因此如果有两个线程同时给一个表加字段，要等另一个执行完才能开始执行

正确修改表的步骤

由于在修改表结构时，会导致加入mdl写锁，会阻塞后面的读锁。正确的写法是在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句可以在MariaDB进行

ALTER TABLE tbl_name NOWAIT  add column ....
ALTER TABLE tbl_name WAIT N  add column ....

3 行锁

偏向innodb存储引擎，开销大，加锁慢；会出现死锁，锁粒度最小，发生锁冲突的概率最低，并发度最高。
innodc与myisam1的最大不同有两点：一是支持事务；而是采用了行级锁

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

索引失效会导致行锁变为表锁

3.1 两阶段锁协议

在innodb事务中，行锁是在需要的时候才加锁的，但并不是不需要就里可释放，而是要等到事务结束时才释放（执行commit），这个就是两阶段锁协议。

事务b会一直被阻塞在事务提交后

3.2 死锁

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。当出现死锁时，有两种策略我们通常采用第二种策略：

• 一种策略是，直接进入等待，直到超时，通过innodb_lock_wait_timeout进行设置
• 另一种策略是发起死锁检测，当发现死锁后，主动回滚死锁链条中的某一事务，让此继续执行。innodb _deadlock_detedt为on表示开启（默认开启）

4 间隙锁

• 什么是间隙锁

当我们使用的是范围条件而不是相等条件检索数据，在请求共享或排他锁时，innodb会给符合条件已有的数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙gap，innodb也会对这个间隙加锁。
InnoDB使用间隙锁的目的，是为了防止幻读，以满足相关隔离级别的要求

• 危害

在执行过程中通过范围查找的话，他会锁定整个范围内所有索引键值，即使这个键值不存在。间隙锁有一个致命弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被锁定，造成在锁定的时候无法插入锁定键值范围内的任何数据

5 怎样锁定一行

共享锁（Ｓ）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE

begin;

select xxx...for update 

commit;

select xxx…for update表示锁定某一行后，其他操作会被阻塞，直到锁定行的会话提交commit;

6 行锁的分析

通过innodb_row_lock可以查看航所情况

show status like 'innodb_row_lock%';

innodb_row_lock_current_waits:当前正在等待锁定的数量

innodb_row_lock_time:从系统启动到现在锁定时间的长度（等待的总时长）

innodb_row_lock_watis 等待的总次数

innodb_row_lock_time_avg等待的平均时长