一、mysql数据库锁介绍

   mysql数据库里的锁主要可以分为全局锁、表级锁、行锁、间隙锁本文将一一介绍。

 

1.1全局锁

全局锁：对整个数据库进行加锁，比如对整个数据库加读锁就是Flush tables with read Lock（FTWRL），解锁语句为unlock tables.当 对数据库进行FTWRL之后，其它线程的以下语句会被阻塞：数据更新语句（update,insert等）数据表的修改语句（alter table drop table）更新类事务提交语句。

1对数据库加全局锁

2新建表结构

可以看到新建一张表的时候navicat长时间没有反应。

3我们通过unlock tables 解除锁就可以进行修改语句了。

     从全局锁的特性可以看出全局锁一般用于mysql全库逻辑备份，因为此时整个mysql库只能进行select语句。但是这时势必会造成一定的问题。试想一下如果我们采取主从结构的mysql.：

1在主库上加全局锁：那会造成主库上的所有更新语句不能更新，因此也会引起业务停摆。

2在从库上加全局锁：会造成主库导向从库的binlog不能更新，会导致主从延迟。

   那么mysql官方是如何备份一张表的呢？他们采取了mysqldump –single-transaction 进行备份。该语句会在备份前开启一个一致性视图，并且由于MVCC的支持，过程中数据一直可以正常更新。一致性视图和MVCC不理解的可以看看mysql的事务。除此之外熟悉mysql主从结构的同学可能知道把global设置成readonly后，数据库也可以保证只读状态，但是相对于FTWRL语句。把global设置成readonly有两点不好。第一 在实际项目开发过程中global=readonly一般用于表示主从的从数据库，第二FTWRL在发生异常，客户端断开后会自动释放锁。readonly会让全库一直保持读状态。

1.2表级锁

   表级锁可以分为：表锁、元数据锁（meta data lock）

1.2.1表锁

   表锁对数据库的一张表进行显示的加锁，可以加读锁和写锁，加锁语句为 lock tables...read/write.,解锁语句为unlock tables.下面的两张表为加读写锁时各个线程间的隔离级别。

lock tables t1 read

	读	写
本线程	可以	阻塞
其他线程	可以	阻塞

 

lock tables t2 write

	读	写
本线程	可以	可以
其他线程	阻塞	阻塞

 

1.2.2MDL锁（meta data lock）

  和表锁不同MDL锁并不需要显示的去声明。当你对表做增删增删改查时会对表加MDL读锁，对表结构进行修改时会对表加MDL写锁。MDL对表加锁时不会立即释放，而是要等到整个事务提交之后才释放。

在MDL锁上，读锁之间是不互斥的。因此你可以同时对一张表进行增删改查。读写锁之间，以及写锁之间是互斥的，这是一种对数据的保护，假设读写锁之间不互斥，这时你在执行查找name=2的一条语句，同时有另外一条语句执行删除name这行会发生什么。

相信你一定有遇到过给一张表增加一个字段，导致整个库挂了的例子。我们来分析一下下面这个场景。假设t表比较大并且name不是索引。事务执行流程如下

session A	session B	session C	session D
select * from t order by name limit 3
	select * from t order by name limit 3
		alter t add f int;（blocked）
			select * from t order by name limit 3（blocked）

sessionA 对表t加MDL读锁，但一直没有提交事务，这时SessionB也要对表t加MDL读锁,发现SessionA的读锁还没有释放，但因为加的也是读锁，因此可以正常执行。之后SessionC需要对表加MDL写锁，因此会被阻塞。那么问题来了如果只有SessionC阻塞是没有问题的,但之后对表t的增删改查操作也会被阻塞，因为再再次申请读锁前，要先释放掉SessionC的写锁。因此SessionD会阻塞。假设对表t的查询特别频繁，那么数据库线程会立即打满，导致数据库挂掉。

 那么有没有方法解决这个问题呢：我们可以从两个方面去思考：

1事务提交后锁就会释放，因此我们可以打开information_schemale库的Innodb_trx表查询长事务，并关掉它们。

2使用 Alter table t wait N add column这样的语句可以进行超时等待。

1.3行锁

行锁可能是我们开发过程中使用最多的锁，比如执行update t set name='法外狂徒张三' where id =1；就会对表t中id=1的这行加上行锁。那么行锁有什么需要注意的呢，看看下面的例子。

事务A	事务B
begin update t set name='法外狂徒张三' where id =1； update t set name=‘罗X' where id =2；
	begin update t set name='共犯李四' where id =1；
commit;

在mysql中行锁是在需要的时候加上去的，但只有在事务提交之后才会释放。因此事务B的语句只有在事务Acommit之后才会释放。

在实际执行语句的过程中我们需要把可能造成锁冲突的语句放在最后。下面就是例子，假设顾客A在公司食堂B窗口吃饭对数据库的操作如下。

1从顾客A的卡中扣除余额

2给B窗口增加余额

3记录顾客A的一条吃饭日志

这时顾客B也来吃饭，对数据库的操作如下

1从顾客B的卡中扣除余额

2给B窗口增加余额

3记录顾客B的一条吃饭日志

那么明显给B窗口增加余额就会有锁冲突，我们在写语句的时候按照312的顺序执行就可以显著减少锁冲突。

下面来讲讲数据库死锁以及死锁检测。下面的事务是一个常见的死锁的案例：

事务A	事务B
begin; update t set name='张三' where id = 1	begin
	update t set name='张三' where id = 2
update t set name='张三' where id = 2
	update t set name='张三' where id = 1

 

可以看到事务A在等待事务B释放id=2的行锁，事务B在等待事务Aid=1的行锁.

解决死锁的方法也有两种

1.innodb_lock_wait_timeout参数默认值是50s，但可以看出很多线上业务是无法容忍这50s的超时的，但如果这个时间设计的太小也可能导致修改语句根本没机会获取锁就被淘汰了。

2开启死锁检测 innodb_deadlock_detect= on,可以想象死锁检测肯定需要额外开销，假设有100个线程都在更新一条消息，每个线程间都要相互检测总共要检测100*99次o(n平方)这时非常消耗cpu和资源的。

3控制并发度，如批提交。

1.4间隙锁

间隙锁下次再写,了解间隙锁需要先了解mysql事务。