【mysql】锁

MYSQL 锁：

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表锁和行锁三类。全局锁、表级锁是Server层的，行级锁是存储引擎级别的，只有Innodb支持行及锁。

---

全局锁：

全局锁就是对整个数据库实例加锁

• MySQL提供了一个加全局读锁的方法

命令： Flush tables with read lock

整个库处于只读状态，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

• 但是如果你发出命令FTWRL时，还有其他的操作，而且是很耗时的操作呢？

1. 先说写操作，这个FTWRL肯定是得等的，等写操作完成才能执行FTWRL，这个很好理解。
2. 那么对于其他的读操作呢？比如说在FLWRL发出之前有一个query：select count(*) from tb 那么FTWRL也得等待(show processlist可以看到 waiting for table flush)。

• mysqldump

全局锁的典型使用场景是，做全库逻辑备份。官方自带的逻辑备份工具是mysqldump。当执行mysqldump -uroot -p123 -h192.131.1.9 -R  vgos_statnum>11.dmp 的时候，lock整个vgos_statnum库。

• * mysqldump –single-transaction

当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。但是single-transaction方法只适用于所有的表使用事务引擎的库。这也是InnoDB 相对于 MyISAM 的优点。

---

表锁：

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁的语法是 lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL作用是防止DDL和DML并发的冲突 ，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
虽然 MDL 锁是系统默认会加的，但却是你不能忽略的一个机制。例如在业务高峰期加字段，可能会导致整个数据挂了。加字段需要MDL写锁，其他操作数据表的DML需要MDL读锁，会被阻塞住，如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话，这个库的线程很快就会爆满。同时，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，对线上业务的影响是非常巨大的。

---

行锁：

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

Innodb采用的是两阶段锁定协议。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDb会根据隔离级别在需要的时候自动加锁。另外，InnoDB也支持通过特定的语句显示锁定。

InnoDB 实现了两种类型的行级锁：

• 共享锁：也称为 S 锁，读锁，多用于判断数据是否存在，多个读操作可以同时进行而不会互相影响。当如果事务对读锁进行修改操作，很可能会造成死锁。
• 独占锁 ：也称为 X 锁，排他锁，写锁，当前写操作没有完成前，它会阻断其他写锁和读锁。
  S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容
  加锁的方式：对于update、delete和insert语句，Innodb会自动给涉及数据集加排他锁(X)；
  事务可以通过以下语句显式的给记录集加锁：

//共享锁
select * from table_name where  ... lock in share mode;
//排它锁
select * from table_name where ... for update;

意向锁：

InnoDB 还有两种内部使用的意向锁，由 InnoDB 自动添加，且都是表级别的锁。用于在加表锁的时候，快速判断是否存在行锁

• 意向共享锁（IS）：事务即将给表中的各个行设置共享锁，事务给数据行加 S 锁前必须获得该表的 IS 锁。
• 意向排他锁（IX）：事务即将给表中的各个行设置排他锁，事务给数据行加 X 锁前必须获得该表 IX 锁。
  加锁方式：意向锁是InnDB自动加的，不需要用户的干预。

`注意：INNODB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁。`
在这里要注意的是如果查询条件中存在没有建索引的字段，Mysql则会给数据库中整张表的所有数据行加行锁，
这时存储引擎会将所有的数据记录加锁返回给MysqlServer层进行过滤，
在MysqlServer层过滤的时候，如果发现有不满足过滤条件的记录，则会调用unlock_row方法，将不满足条件的记录所加的锁释放掉

间隙锁：

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做"间隙(GAP)"。InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

MySQL官方文档：间隙锁的目的是为了让其他事务无法在间隙中新增数据。

在InnoDB下，间隙锁的产生需要满足三个条件：

• 隔离级别为RR
• 当前读
• 查询条件能够走到索引

Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;

Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)

危害(坑)：若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响。

---

行锁优化

1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。
2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。
3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。
4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。