mysql基础-锁

表锁

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

语法

    加锁

 lock tables t_dictionary write,t_diners read 

  查看

show open tables where In_use=1

Database	Table	In_use	Name_locked
redis	t_dictionary	1	0
redis	t_diners	1	0

释放锁

unlock tables

示例说明

session_1	session_2
获得表mylock的READ锁定: lock table mylock read;	连接终端
当前session可以查询该表记录	其他session也可以查询该表的记录
当前session不能查询其它没有锁定的表	其他session可以查询或者更新未锁定的表
当前session中插入或者更新锁定的表都会提示错误	其他session插入或者更新锁定表会一直等待获得锁
释放锁	Session2获得锁，插入操作完成

session_1	session_2
获得表mylock的WRITE锁定 lock table mylock write	待Session1开启写锁后，session2再连接终端
当前session对锁定表的查询+更新+插入操作都可以执行：	其他session对锁定表的查询被阻塞，需要等待锁被释放：
	在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞住。
释放锁	Session2获得锁，查询返回：
不能查询其他表

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

锁类型	自己可读	自己可写	他人可读	自己可操作其他表	他人可写
读锁	是	否	是	否	否，等
写锁	是	是	否，等	否	否，等

行锁

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

InnoDB与MyISAM的最大不同有两点：

        一是支持事务（TRANSACTION）

        二是采用了行级锁

读锁

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。 如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法 SELECT ... LOCK IN SHARE MODE; 查询结果中的每行都加共享锁

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

写锁

排他锁（eXclusive Lock）

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

SELECT ... FOR UPDATE 查询结果中的每行都加排他锁

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

如果查询条件用了索引/主键，那么select ..... for update就会进行行锁。

如果是普通字段(没有索引/主键)，那么select ..... for update+就会进行锁表。

间隙锁危害

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录

即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

行锁分析

 通过检查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：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是
  Innodb_row_lock_time_avg（等待平均时长），
  Innodb_row_lock_waits（等待总次数）
  Innodb_row_lock_time（等待总时长）这三项。

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

最后可以通过

SELECT * FROM information_schema.INNODB_TRX; 来查询正在被锁阻塞的sql语句。

示例

 主键： 行锁

普通索引： c=20 锁住两行 

d 无索引：表锁

间隙锁：发生在普通索引，对insert有影响 c=10，上下都会锁住，可重复读

               5-15区间 都会锁 5和15 不会影响，比如在继续新插入 c=10数据

推荐使用 唯一索引

优化建议

1、不要三心二意，锁住哪行，就改哪行

2、要说话算数，说读锁就干读的事，说写锁就干写的事

3、不要拖拖拉拉，用了锁，就赶紧办事，办完事赶紧提交

4、如果锁，请轻锁，一锁一大片，中间再也无法容纳他人了。（间隙锁的问题）

5、无索引，不行锁。（行锁的前提是过滤条件利用上了索引）

6、用情越深，付出越大。（隔离级别越高，锁成本越高）